DBMaker Tutorial Master

DBMaker DBMaker Tutorial CASEMaker Inc./Corporate Headquarters 1680 Civic Center Drive Santa Clara, CA 95050, U.S.A. www.casemaker.com www.casemaker.com/support ©Copyright 1995-2003 by CASEMaker Inc. Document No. 645049-231048 /DBM41-M06062003-TUTO Publication Date: 2003-06-06 All rights reserved. No part of this manual may be reproduced, stored in a retrieval system, or transmitted in any form, without the prior written permission of the manufacturer. For a description of updated functions that do not appear in this manual, read the file named README.TXT after installing the CASEMaker DBMaker software. Trademarks CASEMaker, the CASEMaker logo, and DBMaker are registered trademarks of CASEMaker Inc. Microsoft, MS- DOS, Windows, and Windows NT are registered trademarks of Microsoft Corp. UNIX is a registered trademark of The Open Group. ANSI is a registered trademark of American National Standards Institute, Inc. Other product names mentioned herein may be trademarks of their respective holders and are mentioned only form information purposes. SQL is an industry language and is not the property of any company or group of companies, or of any organization or group of organizations. Notices The software described in this manual is covered by the license agreement supplied with the software. Contact your dealer for warranty details. Your dealer makes no representations or warranties with respect to the merchantability or fitness of this computer product for any particular purpose. Your dealer is not responsible for any damage caused to this computer product by external forces including sudden shock, excess heat, cold, or humidity, nor for any loss or damage caused by incorrect voltage or incompatible hardware and/or software. Information in this manual has been carefully checked for reliability; however, no responsibility is assumed for inaccuracies. This manual is subject to change without notice. This text is not here.  1Contents Contents 1 Introduction.......................................1-1 1.1 Additional Resources ...................................1-3 1.2 Technical Support ........................................1-4 1.3 Document Conventions ................................1-5 2 RDBMS Basics ...................................2-1 2.1 Syntax Diagrams...........................................2-3 2.2 RDBMS Functions .........................................2-4 2.3 Data Models ..................................................2-6 2.4 Data Independence ......................................2-7 Physical.................................................................................. 2-7 Logical ................................................................................... 2-8 2.5 High-Level Language Support ......................2-9 2.6 Transaction Management ..........................2-10 What a Transaction is .......................................................... 2-10 Concurrency Control........................................................... 2-11 The Lock Concept ............................................................... 2-11 2.7 Integrity Control .........................................2-14 2.8 Access Control ...........................................2-15 ©Copyright 1995-2003 CASEMaker Inc. i DBMaker Tutorial1 User Authorization...............................................................2-15 Transaction Authorization....................................................2-15 2.9 RDBMS Recovery ....................................... 2-17 System Failures.....................................................................2-17 Media Failures......................................................................2-17 3 RDBMS Architecture ......................... 3-1 3.1 Logical RDBMS............................................. 3-2 Internal or Physical Level .......................................................3-3 Conceptual Level....................................................................3-3 External or View Level ...........................................................3-3 Mappings between Levels3-4 3.2 Physical RDBMS........................................... 3-5 Applications and Utilities3-6 Application Program Interface (API)......................................3-8 Query Language Processor .....................................................3-9 RDBMS Engine...................................................................3-10 4 Databases ......................................... 4-1 4.1 Naming Conventions.................................... 4-2 4.2 dmconfig.ini File........................................... 4-3 Creating .................................................................................4-3 Directory................................................................................4-4 Format ...................................................................................4-4 Section Names .......................................................................4-5 Keywords ...............................................................................4-5 Comments .............................................................................4-6 4.3 dmSQL .......................................................... 4-7 Starting ..................................................................................4-7 Workspace..............................................................................4-8 4.4 J Tools........................................................ 4-10 JConfiguration Tool.............................................................4-10 JServer Manager ...................................................................4-10 ii ©Copyright 1995-2003 CASEMaker Inc.  1Contents JDBA Tool .......................................................................... 4-11 4.5 Creating a Database...................................4-12 Tutorial Database ................................................................ 4-12 Connection Handles ............................................................ 4-12 Default User ........................................................................ 4-13 4.6 Database Modes .........................................4-15 Single-User Mode 4-15 Multiple-Connection Mode................................................. 4-15 Client/Server Mode.............................................................. 4-16 5 Tables................................................5-1 5.1 Tablespaces..................................................5-2 Regular Tablespaces ............................................................... 5-2 Autoextend Tablespaces ......................................................... 5-2 System Tablespace ................................................................. 5-2 Default User Tablespace ........................................................ 5-3 5.2 Data Types....................................................5-4 CHAR (size) .......................................................................... 5-4 VARCHAR (size) .................................................................. 5-4 BINARY (size) ....................................................................... 5-5 SMALLINT........................................................................... 5-5 INTEGER............................................................................. 5-6 FLOAT.................................................................................. 5-6 DOUBLE .............................................................................. 5-6 DECIMAL (precision, scale).................................................. 5-7 DATE.................................................................................... 5-8 TIME 5-8 TIMESTAMP ....................................................................... 5-8 LONG VARCHAR............................................................... 5-9 LONG BINARY ................................................................... 5-9 SERIAL (start) ..................................................................... 5-10 FILE 5-10 OID..................................................................................... 5-11 ©Copyright 1995-2003 CASEMaker Inc. iii DBMaker Tutorial1 5.3 Creating a Table......................................... 5-12 Default Values for Columns.................................................5-14 Lock Mode...........................................................................5-15 Fillfactor...............................................................................5-15 NOCACHE.........................................................................5-16 Temporary Tables ................................................................5-16 6 Data................................................... 6-1 6.1 Inserting ....................................................... 6-2 Inserting Using Host Variables...............................................6-3 Different Data Types .............................................................6-4 Inserting Blob Data6-5 6.2 Updating 6-7 Updating Using Standard SQL ..............................................6-7 Updating Using Host Variables6-7 Updating Using OIDs............................................................6-8 6.3 Result Sets................................................. 6-10 Selecting Tables....................................................................6-10 Selecting Columns ...............................................................6-12 Selecting Rows .....................................................................6-13 6.4 Operator Types .......................................... 6-14 Comparison Operators.........................................................6-14 Logical Operators............................