CONTENTS Title Page Copyright Page Preface Technical Changes and New Features 1 Introduction 1.1 Database Maintenance 1.2 Evaluating and Improving Performance 1.3 VAXcluster Environment 1.4 Creating a Sample Database Part 1: Maintenance 2 Monitoring A Database 2.1 The VAX DBMS Monitor Process 2.1.1 How the Monitor Works 2.1.2 Listing Active User Information 2.2 Information About Database Characteristics 3 Opening and Closing a Database 3.1 Opening a Database 3.1.1 Using the DBO/OPEN Command 3.1.2 Binding to a Database 3.2 Closing a Database 4 Backing Up and Restoring a Database 4.1 Backing Up a Database 4.1.1 Backup Types 4.1.1.1 Full Backups 4.1.1.2 Incremental Backups 4.1.1.3 Partial Incremental Backups 4.1.2 Backup Modes 4.1.2.1 Offline Backup 4.1.2.2 Online Backup 4.1.3 Backing Up After-Image Journal Files 4.1.4 Verifying the Database During a Backup 4.1.5 Adding Checksums to the Database Backup File 4.1.6 Backing Up a Database Directly to Tape 4.1.7 Using the Multi-Threaded Backup Utility 4.2 Restoring a Database 4.2.1 Full Restores 4.2.2 Incremental Restores 4.2.3 Restoring and Recovering Individual Area Files 4.2.4 Modifying Database Characteristics When Restoring 4.2.4.1 Modifying After-Image Journaling Characteristics 4.2.4.2 Modifying SPAM Threshold Values 4.2.4.3 Modifying Blocks per Page 4.2.4.4 Adding Snapshots During a Restore 4.2.5 Restoring a Database by Area 4.2.6 Verifying a Database Backup File 4.2.7 Using the Multi-Threaded Restore Utility 4.2.8 Moving Database Files and CDD/Plus Information 4.2.8.1 Moving Database Files 4.2.8.2 Moving CDD/Plus Instance Information 4.3 Backup Strategies 5 Journaling and Recovery 5.1 Update Transactions 5.2 Recovery-Unit Journals 5.2.1 Using the /RECOVER_JOURNAL Qualifier 5.2.2 Defining the DBM$RUJ Logical Name 5.2.3 Displaying the Contents of an .RUJ File 5.2.4 Using BATCH UPDATE Access Mode 5.2.5 The Automatic Recovery Procedure 5.3 After-Image Journaling 5.3.1 Displaying the Contents of an .AIJ File 5.3.2 Journaling After-Images to Tape 5.4 Using DBO/RECOVER to Recover a Database 5.4.1 How the DBO/RECOVER Command Works 5.4.2 Recovery Using Tape 6 Modifying a Database 6.1 Changing Database Metadata 6.1.1 Adding Definitions to a Schema and Storage Schema 6.1.2 Deleting Definitions from a Schema or Storage Schema 6.1.3 Modifying Existing Schema and Storage Schema Definitions 6.1.4 Modifying, Adding, and Deleting a Subschema 6.1.5 Modifying, Adding, and Deleting a Security Schema 6.1.6 Example of Adding Definitions to the PARTS Schema and Storage Schema 6.2 Changing the Physical Database Characteristics 6.2.1 Changing Database Locking Characteristics 6.2.2 Changing Buffer Specifications 6.2.3 Controlling After-Image Journaling 6.2.4 Controlling Snapshots 6.2.4.1 Determining the Current Snapshot Settings 6.2.4.2 Allowing and Enabling Snapshots 6.2.4.3 Using the Different Snapshot Modes 6.2.4.4 Monitoring Snapshot Activity 6.2.4.5 Changing Snapshot File Sizes 6.2.5 Increasing Space Allocation and Extension Values 6.2.6 Expanding the CALC Range of an Area 6.2.7 Modifying SPAM Threshold Values 6.2.8 Updating CDD/Plus Instance Information 6.2.9 Changing the Maximum Number of Database Users 6.2.10 Changing the Maximum Number of Nodes for VAXcluster Users 6.2.11 Changing the Default Placement of the Recovery-Unit Journal File 6.2.12 Changing the Open Mode of the Database 6.3 Changing the Metadata and the Accompanying Supporting Structures 6.3.1 Beginning and Ending a DRU Session 6.3.2 Getting Help in DRU 6.3.3 Creating a Change File 6.3.4 Opening and Closing a Change File 6.3.5 Defining Changes 6.3.5.1 Removing Areas from a Record WITHIN Clause 6.3.5.2 Changing Set Insertion and Retention Modes to AUTOMATIC MANDATORY or AUTOMATIC FIXED 6.3.5.3 Specifying That Duplicates Are Not Allowed on Sorted Sets 6.3.5.4 Changing Sort Keys and Sort Order on Sorted Sets 6.3.5.5 Changing Set ORDER and MODE Clauses 6.3.5.6 Changing B-Tree Characteristics 6.3.6 Analyzing the Changes 6.3.7 Executing Changes 6.3.8 Releasing Restructured Areas 6.3.9 Recentralizing Metadata 6.3.10 Removing Changes from a Change File 6.3.11 Displaying Information About Records, Sets, and Changes 6.3.12 Defining the DRU Environment 6.3.13 Creating Macros 6.3.14 Editing Within the DRU Utility 7 Deleting Databases and Updating CDD/Plus Information 7.1 Deleting a Database 7.2 Deleting a Schema 7.3 Deleting a Storage Schema or a Subschema 7.4 Deleting a Security Schema 7.5 Database Instance Information 8 Internal Representation of the Database 8.1 Database Storage Page Structure 8.1.1 Page Header 8.1.2 Line Index 8.1.3 Free Space 8.2 Storage Segment Structure 8.2.1 User-Stored and SYSTEM Records 8.2.2 Index Node Records 8.2.3 Fragmented Storage Records 8.3 Database Storage Page with Snapshots 8.3.1 Transaction Sequence Number 8.3.2 Page Tail 8.3.3 Comparison of Live Page and Snap Page Dumps 8.3.4 Page Tail on Snapshot Page 8.4 Space Area Management (SPAM) Page Structure 9 Verifying and Altering a Database 9.1 Verifying the Integrity of a Database 9.1.1 Example of Database Corruption 9.1.2 Causes of Database Corruption 9.2 Using DBALTER 9.2.1 Binding to a Database 9.2.2 Clearing a Corruption Flag 9.2.3 Selecting the Area Page for Altering 9.2.4 Displaying Page Contents 9.2.5 Changing Page Contents 9.2.6 Moving Database Files 9.2.7 Moving Data 9.2.8 Clearing an Inconsistent Flag 9.2.9 Changing the Radix 9.2.10 Verifying Alterations 9.2.11 Keeping an Audit Trail of Alterations 9.2.12 Completing Transactions 9.2.13 Exiting from the DBALTER Utility 9.2.14 Getting Online Information About DBALTER Commands 10 Handling Bugcheck Dumps 10.1 Types of Bugcheck Dumps 10.1.1 Locations of Bugcheck Dump Files 10.1.2 Defining the DBM$BUGCHECKDIR Logical Name 10.2 When to Report a Bugcheck Dump 10.2.1 Examining a Bugcheck Dump 10.2.2 Contents of a Bugcheck Dump 10.3 Submitting an SPR Part 2: Performance 11 Performance Tuning Overview 11.1 Utilities and Tools to Evaluate Performance 11.1.1 Analyzing Space Usage 11.1.2 Monitoring Locking 11.1.3 Displaying Database Statistics 11.1.4 Dumping Database Information 11.1.5 Operating System Utilities 11.2 VAXcluster Performance Considerations 11.3 Establishing a Context 11.4 Using a Test Database 11.4.1 Make Changes One at a Time 11.4.2 Develop Unload/Reload Procedures 11.5 Changing Operating System Parameters 11.6 Sample Performance Evaluation Procedure 11.7 Sample List of Performance-Related Database Changes 11.8 Making Performance Changes in Order of Difficulty 12 Analyzing Record Storage Problems 12.1 Using the DBO/ANALYZE Command 12.2 Interpreting DBO/ANALYZE Output 12.2.1 The Report Heading 12.2.2 Storage Area Space Utilization Histogram 12.2.3 Structuring Records in an Area 12.2.3.1 Volatile Storage Area 12.2.3.2 Static Storage Area 12.2.4 Record Type Summary Analysis 12.2.4.1 Average Length in Bytes 12.2.4.2 Percentage of Fragmented Records 12.2.4.3 Percentage of Total Space and Used Space 12.2.5 Set Analysis 12.2.5.1 Members and Clustered Reads 12.2.5.2 Experimenting with Buffer Configurations 12.2.6 Index Sets 12.2.6.1 Nodes 12.2.6.2 Index Levels 12.2.7 Set Histograms 13 Using Database Dumps in Performance Analysis 13.1 Performance-Related Qualifiers for the DBO/DUMP Command 13.2 Dumping Area Pages 14 Using Database Statistics 14.1 Selecting a Display Mode 14.2 Selecting a Display Page 14.3 Displaying Statistics for Individual Files 14.4 Selecting a Display Format 14.4.1 Graphic Display Format 14.4.2 Numbers Display Format 14.4.3 Time Plot 14.5 Getting Online Help 14.6 Interpreting Database Statistics 14.7 Understanding the Display Pages 14.7.1 I/O Statistics 14.7.2 Database Verb Statistics 14.7.3 Index Statistics 14.7.4 Virtual Memory Usage Statistics 14.7.5 Summary Locking Statistics 14.7.6 AIJ Statistics 14.7.7 PIO Statistics 14.7.8 Record Statistics 14.7.9 Snapshot Statistics 14.7.10 I/O Stall Time 14.7.11 Transaction Durations 14.7.12 Stall Messages 14.7.13 Lock Statistics for One Lock Type 14.7.14 Lock Statistics for One Statistics Field 14.8 Writing Statistics to a File 14.9 Formatted Binary Output Part 3: VAXcluster 15 Using VAX DBMS in a VAXcluster Environment 15.1 Terms and Concepts 15.1.1 Definition of a VAXcluster 15.1.2 Difference Between Single-Node and VAXcluster Environment 15.1.3 Sharing Disk Files 15.1.4 Dual-Pathed Disks 15.1.5 Disk Naming Conventions 15.1.6 Common System Disk 15.1.7 The Distributed Lock Manager 15.2 VAX DBMS in a VAXcluster 15.2.1 Determining Whether VAX DBMS Is Operating in a VAXcluster Environment 15.2.2 Using Multiple Monitor Processes per Database 15.2.3 Distributing Access to Root File Information 15.2.4 Using VAX DBMS in a Local Area VAXcluster 15.3 Deciding Where to Place Database Files 15.3.1 Disk Device Considerations 15.3.2 Steps in Deciding Where to Place Database Files 15.4 CDD/Plus Data Dictionary Requirements 15.5 Creating a Database in a VAXcluster 15.5.1 Making Sure Your Database Is Accessible 15.5.2 Reducing Loss of Database Access 15.5.3 Example: Creating the PARTS Database 15.5.4 Summary: Creating a Database in a VAXcluster Environment 15.6 Converting a Single-Node Database 15.6.1 Using Backup and Restore to Move Database Files 15.6.1.1 Example: Converting the PARTS Database 15.6.1.2 Summary: Converting Databases 15.6.2 Using DBALTER DEPOSIT to Move Database Files 15.7 Automatic Recovery Procedure 15.8 Maintaining and Monitoring A Database 15.8.1 Backup and Restore Operations 15.8.2 After-Image Journaling 15.8.3 Monitoring The Database EXAMPLES 4-1 Multi-Threaded Restore Example 8-1 Page Header 8-2 Line Index Entries 8-3 Locked and Unlocked Free Space 8-4 User-Stored Record 8-5 SYSTEM Record with Clusters 8-6 Example of DYNAMIC Cluster 8-7 Index Node Record with Scroll 8-8 Fragmented Record 8-9 Example of TSN Index with Line Index 8-10 Example of Page Tail 8-11 Comparative Dumps of Live Page and Snapshot Page 8-12 Example of a SPAM Page Dump 12-1 The Report Heading 12-2 Storage Area Space Utilization Histogram 12-3 Volatile Storage Area 12-4 Static Storage Area 12-5 Record Type Summary Analysis 12-6 Set Analysis Summary 12-7 Index Summary 12-8 Set Histograms 14-1 Graph Display Format 14-2 Numbers Display Format 14-3 Time Plot Display Format 14-4 Sample Interval of One Second 14-5 I/O Statistics Display Page 14-6 Database Verb Statistics Display Page ( 1 ) 14-7 Database Verb Statistics Display Page ( 2 ) 14-8 Index Statistics Display Page 14-9 Virtual Memory Usage Statistics Display Page 14-10 Summary Locking Statistics Display Page 14-11 AIJ Statistics Display Page 14-12 PIO Statistics Display Page 14-13 Record Statistics Display Page 14-14 Snapshot Statistics Display Page 14-15 I/O Stall Time Display Page 14-16 Transaction Durations Display Page 14-17 Stall Messages Display Page 14-18 Locking (Page Locks) Display Page 14-19 Locking (Locks Requested) Display FIGURES 4-1 Multi-Threaded Backup Set-Up 4-2 Multi-Threaded Restore Set-Up 4-3 PARTS Database Files in Their New Locations 8-1 Database Storage Page Format 8-2 Page Header Format 8-3 Line Index Format 8-4 Storage Segments Portion of a Page 8-5 Storage Segment 8-6 Storage Record Structure 8-7 General Format of Cluster 8-8 Pointer Cluster for a Set 8-9 DYNAMIC Cluster Structure 8-10 Data Run 8-11 Index Node 8-12 Scroll Entries 8-13 Record Fragmentation 8-14 Storage Segment for a Fragmented Record 8-15 Transaction Sequence Number (TSN) Index 8-16 Page Tail Structure 8-17 Space Area Management (SPAM) Page Format 12-1 Sample Database Structure 15-1 Example of a Single-Node Environment 15-2 Example of a VAXcluster Environment 15-3 Adding an HSC50 to a VAXcluster Environment 15-4 Sample Distribution of PARTS Database Files TABLES 1-1 Regular Maintenance Activities 6-1 Threshold Values and Available Free Space 6-2 DRU Area Walking Execution Summary 11-1 Performance Problem Areas