CONTENTS Title Page Copyright Page Preface Technical Changes and New Features 1 Introduction 1.1 VAX Data Distributor and Digital Database Products 1.2 Database Location Considerations 1.2.1 Maintaining Central Databases 1.2.2 Maintaining Local Databases 2 Overview 2.1 Overview of VAX Data Distributor 2.1.1 Transfers 2.1.2 Local and Remote Databases 2.1.3 Transfers and Database Distribution 2.1.3.1 One Source to Many Targets Data Distribution 2.1.3.2 Many Sources to One Target Data Distribution 2.1.3.3 Data Exchange 2.1.4 Selecting Subsets of Data 2.1.5 Methods of Transfer 2.1.5.1 Extraction and Extraction Rollup Transfers 2.1.5.2 Replication Transfers 2.2 Choosing a Method of Transfer 2.2.1 Classifying Your Database Tasks 2.2.2 Transfer Choices and Considerations 2.2.2.1 Types of Source Database Used 2.2.2.2 Location of Data Distributor 2.2.2.3 Data Currency and Volume of Data Transferred 2.2.2.4 Target Disk Storage Usage 2.2.2.5 Source Disk Storage Usage 2.2.2.6 Source Database Transaction Rate 2.2.2.7 Source Database Availability 3 Components of VAX Data Distributor 3.1 Overview 3.2 SQL 3.3 Source Database 3.4 Target Database 3.5 Transfer Monitor 3.6 Transfer Database 3.7 Transfer and Schedule Definitions 3.8 Copy Process 3.9 Special Data Distributor System Tables for Replications 4 Preparations for Distributing Data 4.1 Determining Product Versions and Licenses 4.2 Requirements for Locating Data Distributor at Various Sites 4.3 Moving Applications to Target Sites 4.3.1 Using CDD/Plus with Data Distributor 4.3.2 Using Transfer Definitions to Provide Remote Copies of Database Definitions 4.4 Preparations at the Transfer Definition Site 4.5 Preparations at the Source Sites 4.6 Preparations at the Target Sites 4.7 Remote Access Using Proxy Accounts 4.7.1 Specifying Proxy Accounts in Transfer Definitions 4.7.2 Setting Up Proxy Accounts for the Copy Process 4.8 Preparing Database Access Using RMU/OPEN 4.9 Network Tuning Parameters 4.10 Performing Database Maintenance, Backup, Recovery, and Conversion 4.10.1 Maintenance, Backup, Recovery, and Conversion of Source Databases 4.10.1.1 Backing Up and Recovering a Source Database 4.10.1.2 Converting Source Databases 4.10.2 Maintenance, Backup, Recovery, and Conversion of Target Databases 4.10.2.1 Backing Up and Recovering a Target Database 4.10.2.2 Converting Target Databases 4.10.3 Maintenance, Backup, Recovery, and Conversion of Transfer Databases 4.10.3.1 Backing Up and Recovering a Transfer Database 4.10.3.2 Converting Transfer Databases 4.10.4 Changing Data Definitions and Restructuring Databases 4.10.4.1 Changing Definitions in and Restructuring Source Databases 4.10.4.2 Changing Definitions in and Restructuring Target Databases 5 Creating Database Transfers 5.1 Extraction and Extraction Rollup Transfers 5.1.1 Extraction and Extraction Rollup Transfer Definition Elements 5.1.1.1 Identifying Source Databases 5.1.1.2 Target Nodes and Directories 5.1.1.3 Target Database Parameters 5.1.1.4 Using the Wildcard Character 5.1.1.5 Authorization Identifiers 5.1.1.6 Location of Copy Process Log Files 5.1.1.7 Using Command Procedures 5.1.2 Identifying Rows 5.1.3 Forming Select Expressions 5.1.4 Transferring Views 5.1.5 Creating and Starting an Extraction or an Extraction Rollup Transfer 5.2 Replication Transfers 5.2.1 Replication Transfer Definition Elements 5.2.1.1 Identifying the Source Database 5.2.1.2 Target Nodes and Directories 5.2.1.3 Target Database Parameters 5.2.1.4 The Wildcard Character 5.2.1.5 Authorization Identifiers 5.2.1.6 Location of Copy Process Log Files 5.2.1.7 Using Command Procedures 5.2.2 Identifying Rows 5.2.2.1 The Effect of Inserting, Altering, and Dropping Rows 5.2.3 Forming Select Expressions 5.2.4 Transferring Views 5.2.5 Creating and Starting a Replication Transfer 6 Working with Transfers 6.1 Creating a Schedule for Transfers 6.2 Transfer States 6.3 Executing the Transfer 6.4 Modifying Your LOGIN.COM Procedure for the Copy Process 6.5 Stopping Transfers 6.6 Changing and Deleting Transfers 6.7 Changing and Deleting Schedules 6.8 Displaying Transfer Information 6.9 Using Log Files 6.9.1 Using Copy Process Log Files 6.9.2 Copy Process Option for Logging Individual Row Keys 6.9.3 Using Transfer Monitor Log Files 6.10 User-added Data Definitions in a Target Database 6.10.1 The Effects of Transfer Reexecution on User-added Definitions 6.10.1.1 Extraction and Extraction Rollup Transfers 6.10.1.2 Replication Transfers 6.10.2 Adding Indexes to Target Tables 6.10.3 Adding Constraints for Target Tables 6.10.4 Adding Triggers for Target Tables 6.10.5 Modifying Protection for Target Databases and Tables 6.10.6 Adding Tables to Target Databases 6.10.7 Adding Views to Target Databases 6.11 User-added Data in a Target Database 6.12 Special System Tables Supporting Replications 6.13 Applying Application Requirements to Transfers 6.13.1 Storing Database Definitions in the CDD/Plus Data Dictionary 6.13.2 Identifying the Location of the Database 6.13.3 Determining the State of a Target Database 6.13.4 Modifying Row Descriptions in Application Programs 6.13.5 Modifying Transaction Share Modes and Lock Types for Extraction or Extraction Rollup Databases 6.13.6 Modifying Transaction Share Modes and Lock Types for Replication Databases 6.13.7 Evaluating Application Schedules for Extraction or Extraction Rollup Databases 6.13.8 Evaluating Application Schedules for Replication Databases 6.13.9 Providing Data Definitions Without Data at the Target Site 6.14 Replication Transfers Performance Option 6.14.1 Performance Considerations 6.14.2 Aiding Performance with Indexing 6.14.3 Optional Indexing 6.14.4 Creating the Indexes 6.14.4.1 Index Creation for Replication Initialization and Reinitialization 6.14.4.2 Index Creation for Replication Updates 6.14.5 Dropping the Indexes 6.14.6 Creating Indexes for Replication Transfers - Summary 6.15 Limiting Concurrent Transfers on a CPU 7 Using Command Procedures with Transfers 7.1 Defining and Executing Command Procedures 7.1.1 Executing Command Procedures 7.1.1.1 Executing Remotely or Locally Stored Command Procedures on a Local Node 7.1.1.2 Executing Remotely Stored Command Procedures on a Remote Node 7.1.2 Completing the Transfer with DDAL$EPILOGUE.COM 7.1.3 Executing Multiple Command Procedures 7.1.4 Passing Parameters to Command Procedures 7.1.5 Executing a Command Procedure on a Remote Node 7.1.6 Finding Command Procedure Information in the Copy Process Log File 7.2 Determining Transfer Status 7.2.1 Using DDAL$CP_CONTINUE to Affect Transfer Execution 7.2.1.1 Determining Prologue Success Status with Prologue Command Procedures 7.2.1.2 Determining Transfer Success Status with Epilogue Command Procedures 7.2.1.3 Determining Epilogue Success Status with Epilogue Command Procedures 7.2.2 Using the SHOW TRANSFER STATUS Statement 7.2.3 Using the Copy Process Log for Status Information 7.3 Typical Uses of Command Procedures 7.3.1 Defining Indexes, Constraints, Triggers, Additional Views, and Protection Information 7.3.2 Defining Target Database Protection Using Access Control Lists 7.3.3 Bringing Up the Network Line at Transfer Time 7.3.4 Storing Data Definitions in the Data Dictionary at a Target Site 7.3.4.1 Using the SUBMIT/REMOTE Command to run a Remote Command Procedure 7.3.4.2 Using the TYPE Command to Run a Remote Command Procedure 8 Transferring Data to Existing Databases 8.1 Overview 8.2 Using Transfers to Existing Databases 8.2.1 Extraction Transfers to an Existing Database 8.2.1.1 Using an Epilogue Procedure to Add Indexes, Constraints, and New Views 8.2.1.2 Updating the Target Database Dictionary 8.2.1.3 Using Prologue Procedures for Transfer Reexecution 8.2.1.4 Changing Table Definitions 8.2.1.5 Changing Domain Definitions 8.2.1.6 Changing View Definitions 8.2.1.7 Dropping Indexes and Constraints 8.2.1.8 Database Access Conflicts 8.2.2 Replication Transfers to an Existing Database 8.2.3 Writing to Target Databases 8.2.3.1 New Databases 8.2.3.2 Existing Databases 8.2.4 Multiple Transfers to an Existing Database 8.2.5 Transfer Failure and Storage Usage 8.2.6 Database Consistency 8.2.7 Altering or Dropping Transfers 8.3 Restrictions 9 SQL Statements for VAX Data Distributor 9.1 CREATE SCHEDULE Statement 9.2 CREATE TRANSFER Statement 9.3 DROP SCHEDULE Statement 9.4 DROP TRANSFER Statement 9.5 HELP Statement 9.6 REINITIALIZE TRANSFER Statement 9.7 SHOW TRANSFER Statement 9.8 START TRANSFER Statement 9.9 STOP TRANSFER Statement 10 RDO Statements for VAX Data Distributor 10.1 DEFINE SCHEDULE Statement 10.2 DEFINE TRANSFER Statement 10.3 DELETE SCHEDULE Statement 10.4 DELETE TRANSFER Statement 10.5 HELP Statement 10.6 REINITIALIZE TRANSFER Statement 10.7 SHOW TRANSFER Statement 10.8 START TRANSFER Statement 10.9 STOP TRANSFER Statement A The Transfer Database and Special-Purpose System Tables A.1 VAX Data Distributor Transfer Database Tables A.1.1 DDAL$INVOKE_TIME Transfer Database Table A.1.2 DDAL$TRANSFERS Transfer Database Table A.1.3 DDAL$ROLLUP_DATABASES Transfer Database Table A.1.4 DDAL$TRANSFER_DATABASE Transfer Database Table A.1.5 DDAL$TRANSFER_FIELDS Transfer Database Table A.1.6 DDAL$TRANSFER_RELATIONS Transfer Database Table A.1.7 DDAL$TRANSFER_SCHEDULE Transfer Database Table A.1.8 DDAL$TRANSFERS_STATUS Transfer Database Table A.1.9 DDAL$TRANSFER_VIEWS Transfer Database Table A.2 VAX Data Distributor System Tables in Source Databases A.2.1 RDB$TRANSFERS System Table A.2.2 RDB$TRANSFER_RELATIONS System Table A.2.3 RDB$CHANGES System Table A.2.4 RDB$CHANGES_MAX_TSER System Table A.3 VAX Data Distributor System Tables in Target Databases A.3.1 RDB$VINTAGE Table A.3.2 RDBVMS$TRANSFER_OWNER Table B VAX Data Distributor Logical Names B.1 DDAL$CHANGES_INDEX Logical Name B.2 DDAL$CP_CONTINUE Logical Name B.3 DDAL$CP_LOG_OPTIONS Logical Name B.4 DDAL$CP_READ_CHANGES Logical Name B.5 DDAL$MAX_COPY_PROCESS Logical Name B.6 DDAL$PURGE Logical Name C VAX Data Distributor Operational Information C.1 Operational Procedures C.1.1 Re-Creating the Transfer Database C.1.2 Starting and Stopping the Transfer Monitor C.2 Transfer States and Transfer Status Codes C.3 Special-Purpose System Tables Used with Replication Source Databases C.4 Metadata Maintained by Data Distributor in Target Databases C.5 Access Rights for Transfer Database and Special-Purpose System Tables C.6 Transaction Activity During Transfer Execution C.6.1 Common Operations C.6.2 Extraction or Extraction Rollup Transfer Operation C.6.2.1 Transfer to a New Target Database C.6.2.2 Transfer to an Existing Target Database C.6.3 Initial or Reinitialized Replication Transfer Operation C.6.3.1 Transfer to a New Target Database C.6.3.2 Transfer to an Existing Target Database C.6.4 Replication Update Transfer Operation C.7 Inserting, Altering, and Dropping Rows in Replication Transfers Glossary EXAMPLES 2-1 Selecting a Subset of Tables for the Target Database 2-2 Selecting a Subset of Columns for the Target Database 2-3 Selecting a Subset of Rows for the Target Database 5-1 Extraction Transfer Definition 5-2 Replication Transfer Definition 6-1 Schedule Definition 6-2 Schedule Definition Specifying Absolute Time 6-3 A Transfer in the SCHEDULED State 6-4 Customizing LOGIN.COM to Skip Portions of Its Code 6-5 Deleting a Transfer Definition 6-6 Displaying a Transfer Definition 6-7 Displaying a Schedule Definition 6-8 Displaying the Status of a Transfer 6-9 Sample Copy Process Log File 6-10 Sample Summary Statistics with Overflow Values Recorded 6-11 Transfer Monitor Log File 7-1 Prologue and Epilogue Files Specified in CREATE TRANSFER 7-2 Prologue Information Within a Copy Process Log File 7-3 DDAL$EPILOGUE.COM Output in Copy Process Log File 7-4 Epilogue File Named in a Transfer Definition 7-5 Epilogue Command Procedure Using SUBMIT 7-6 Remote Command Procedure Executed by SUBMIT 7-7 Epilogue Command Procedure Using TYPE 7-8 Remote Command Procedure Executed by TYPE FIGURES 1-1 Data Distributor and Digital Database Products 1-2 A Network Containing a Central Database 2-1 Subset of Tables Transferred to a Target Database 2-2 Subset of Columns Transferred to a Target Database 2-3 Selecting Rows for Transfer to a Target Database 2-4 Remote Nodes Requiring Database Subsets 2-5 Subsets of Extractions at Remote Nodes 2-6 Extraction Databases 2-7 Extraction Rollup Databases 3-1 Data Transfer With Data Distributor Components 4-1 Proxy Access for Remote Nodes 5-1 Identifying Rows for an Extraction TABLES 2-1 Data Location Requirements 2-2 Application Type and Transfer Method 2-3 Source Database Types 2-4 Licensing Requirements for Source Databases 2-5 Storage Requirements for Transfers That Create a Target Database 2-6 Storage Requirements for Transfers to an Existing Target Database 2-7 Storage Requirements for Source Databases 3-1 Components of Data Distributor 6-1 Indexes for the RDB$CHANGES Table 6-2 Equivalence Names for Index Logicals 7-1 Statuses Corresponding to DDAL$CP_CONTINUE 7-2 Epilogue Failure Statuses for DDAL$CP_CONTINUE 7-3 Transfer Statuses A-1 VAX Data Distributor Transfer Database: DDAL$INVOKE_TIME Table A-2 VAX Data Distributor Transfer Database: DDAL$TRANSFERS Table A-3 VAX Data Distributor Transfer Database: DDAL$ROLLUP_DATABASES Table A-4 VAX Data Distributor Transfer Database: DDAL$TRANSFER_DATABASE Table A-5 VAX Data Distributor Transfer Database: DDAL$TRANSFER_FIELDS Table A-6 VAX Data Distributor Transfer Database: DDAL$TRANSFER_RELATIONS Table A-7 VAX Data Distributor Transfer Database: DDAL$TRANSFER_SCHEDULE Table A-8 VAX Data Distributor Transfer Database: DDAL$TRANSFERS_STATUS Table A-9 VAX Data Distributor Transfer Database: DDAL$TRANSFER_VIEWS Table A-10 VAX Data Distributor Source Database: RDB$TRANSFERS System Table A-11 VAX Data Distributor Source Database: RDB$TRANSFER_RELATIONS System Table A-12 VAX Data Distributor Source Database: RDB$CHANGES System Table A-13 VAX Data Distributor Source Database: RDB$CHANGES_MAX_TSER System Table A-14 VAX Data Distributor Target Database: RDB$VINTAGE System Table A-15 VAX Data Distributor Target Database: RDBVMS$TRANSFER_OWNER Table B-1 Equivalence Strings for DDAL$CHANGES_INDEX B-2 Equivalence Strings for DDAL$CP_CONTINUE B-3 Keywords for DDAL$CP_LOG_OPTIONS B-4 Keywords for DDAL$CP_READ_CHANGES C-1 Transfer States C-2 Events Affecting Transfer States C-3 Status Codes for the DDAL$LAST_COMPLETION_STATUS Column C-4 Special-Purpose System Tables Access Rights C-5 Access Rights for Transfer Database Tables