CONTENTS Title Page Copyright Page Preface 1 Designing a Database 1.1 Analyzing User Requirements 1.2 Designing the Schema 1.3 Designing the Storage Schema 1.4 Designing Subschemas 1.5 Designing Security Schemas 1.6 Locking Considerations 1.7 Accessing Remote Databases Through DECnet 1.8 Compiling the Database 1.9 Creating the Database 1.10 Testing the Database Design 1.11 Loading the Production Database 2 Analyzing Data and Transactions 2.1 Sample Database Background 2.2 Analyzing Data 2.2.1 Isolating Records and Data Items 2.2.2 Reducing Data Redundancy 2.2.3 Organizing Records into Sets 2.2.3.1 Using One-to-Many Relationships 2.2.3.2 Resolving Multiple One-to-Many Relationships 2.2.3.3 Resolving Many-to-Many Relationships 2.2.3.4 Resolving Reflexive Relationships 2.3 Analyzing Transactions 2.3.1 Parts and Components 2.3.2 Vendor Information 2.3.3 Responsible Employees 3 Writing the Schema 3.1 Understanding Schemas 3.2 Defining the Schema 3.3 Defining Areas 3.4 Defining Records 3.4.1 Defining Storage Area Placement 3.4.2 Defining Data Items 3.4.3 Default Values 3.4.4 Repeating Data Items 3.4.5 Taking Advantage of Contiguous Moves 3.5 Defining Sets 3.5.1 Set Ownership and Membership 3.5.2 Participation in Multiple Sets 3.5.3 Set Insertion and Retention 3.5.3.1 AUTOMATIC FIXED Mode 3.5.3.2 AUTOMATIC MANDATORY Mode 3.5.3.3 AUTOMATIC OPTIONAL Mode 3.5.3.4 MANUAL FIXED Mode 3.5.3.5 MANUAL MANDATORY Mode 3.5.3.6 MANUAL OPTIONAL Mode 3.5.4 Set Order 3.5.4.1 Unsorted Sets 3.5.4.2 Sorted Sets 3.6 Performing Validity Checks 3.6.1 Checking Data at the RECORD Level 3.6.2 Checking Data at the ITEM Level 3.6.3 Checking Data at the Set MEMBER Level 3.7 Ensuring Unique Data Items and Records for CALC Sets 4 Writing the Storage Schema 4.1 Enhancing the Default Storage Schema 4.2 Optimizing Storage Schemas 4.3 Storage Records 4.3.1 CLUSTERED VIA Record Storage Placement Option 4.3.2 SCATTERED Record Storage Placement Option 4.3.3 Storing Data Items 4.3.3.1 Data Types 4.3.3.2 STATIC and DYNAMIC Allocation 4.4 Storage Sets 4.4.1 Storing CHAIN Sets for Serial Processing 4.4.1.1 Tradeoffs of CHAIN Mode 4.4.1.2 Retrieval in CHAIN Sets 4.4.1.3 Improving Insertion Performance by Using OMIT PRIOR Clause 4.4.2 Storing Records in CALC Sets for Direct-Access Processing 4.4.2.1 Tradeoffs of CALC Mode 4.4.2.2 Retrieval in CALC Sets 4.4.3 Storing INDEX Sets 4.4.3.1 B-Trees 4.4.3.2 Tradeoffs of INDEX Mode 4.4.3.3 Controlling B-Tree Placement 4.4.3.4 Retrieval in INDEX Sets 4.4.3.5 Node Size Calculation 4.4.3.6 Optimum Node Size 4.4.4 Storage Set Mode Concerns and Restrictions 5 Writing Subschemas 5.1 Adding Subschemas to the Database 5.2 Customizing Subschemas 5.2.1 Mapping Areas to Realms 5.2.2 Renaming Schema Entities 5.2.3 Defining Subschema Records 5.2.3.1 Data Type Equivalence and Conversion 5.2.3.2 Contiguous Moves 5.2.3.3 Other Data Type Considerations 5.2.3.4 GROUP Items 5.2.3.5 DATATRIEVE Support 5.2.4 Defining Subschema Sets 6 Locking Considerations 6.1 Declaring Processing Intentions 6.1.1 Allow Modes and Access Modes 6.1.2 Usage Modes 6.1.3 BATCH Usage Modes 6.1.4 Selecting the Appropriate Usage Mode 6.2 Adjustable Record Locking 6.2.1 Enabling and Customizing Adjustable Record Locking Levels 6.2.2 Read-Only Record Locks 6.2.3 Update Record Locks 6.3 Adjustable Page Locking 6.4 Two-Phase Page Locking 6.5 Carry-Over Lock Optimization 7 Establishing Remote Access 7.1 Access Options and Security Considerations 7.1.1 Using Access Control Strings (ACSs) 7.1.2 Creating Proxy Logins 7.1.3 Creating a Common Account 7.1.4 Using the Default DECnet Account 7.1.5 Transferring Bind Parameters 7.2 Using Security Schemas 8 Compiling Schemas 8.1 Compiling DDL Source Files 8.2 Using DDL Compiler Options 8.2.1 Specifying Default Compilations 8.2.2 Listing a Compilation 8.2.3 Storing Compilations 8.2.4 Preventing Compilation Storage 8.2.5 Replacing an Existing Compilation 8.3 Listing CDD/Repository Information 8.4 Generating Defaults from a Compiled Schema 8.5 Modifying Default Compilations 9 Creating A Database 9.1 Naming the Root File 9.2 Naming Database Area and Snapshot Files 9.3 Sizing Storage Area Files 9.4 Selecting Data Definitions 9.5 Journaling Before- and After-Images 9.5.1 Managing .RUJ Files 9.5.2 Using After-Image Journaling 9.5.3 Reserving Space for .AIJ Files 9.5.4 Enabling After_Image Journaling 9.5.5 Creating .AIJ Files 9.6 Loading and Restructuring Considerations 9.7 Using Concealed Logical Names 9.8 Placing Database Files 9.9 Space Area Management 9.9.1 Understanding How DBCS Uses SPAM Pages 9.9.2 Understanding the Space Search Algorithm 9.9.3 Selecting Threshold Values for an Area 9.10 Data Transfer 9.10.1 Clustered I/O 9.10.2 Page Size 10 Buffering and Commit Processing 10.1 Choosing a Buffer Scheme 10.1.1 Local Buffer Pools 10.1.2 Global Buffer Pools 10.2 Writing Buffers 10.2.1 The Default Commit Behavior 10.2.2 The Fast Commit Behavior 10.2.3 Enabling the Fast Commit Behavior 10.2.4 Optimizing Commit-to-Journal 10.2.5 Checkpointing 10.2.5.1 Checkpointing by Specifying .AIJ File Growth 10.2.5.2 Checkpointing by Specifying a Time Interval 10.2.5.3 Checkpointing by Specifying a Transaction Limit 10.2.6 Selecting Checkpointing Options 10.2.7 Using Checkpointing to Adjust Recovery Time 10.2.7.1 Examining the Monitor Log File 10.2.7.2 Examining Checkpoint Statistics 10.3 Snapshot Storage Areas 10.3.1 Using Snapshots 10.3.2 Benefits of Snapshots 10.3.3 Snapshot Characteristics A Sample Schemas A.1 PARTS Schema Definition A.2 PARTS Default Subschema A.3 PARTS Storage Schema Definitions A.3.1 PARTS Default Storage Schema A.3.2 Edited PARTS Default Storage Schema B PARTS Database Subschema B.1 PARTS Bachman Diagram B.2 PARTSS1 Subschema B.3 PARTSS2 Subschema B.4 PARTSS3 Subschema B.5 PARTSS4 Subschema B.6 PARTSS5 Subschema