Transcription
IBM InfoSphere Data ReplicationVersion 10.1.3SQL Replication Guide and Reference SC19-3638-00
IBM InfoSphere Data ReplicationVersion 10.1.3SQL Replication Guide and Reference SC19-3638-00
NoteBefore using this information and the product that it supports, read the information in “Notices and trademarks” on page469. Copyright IBM Corporation 1994, 2012.US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contractwith IBM Corp.
ContentsChapter 1. Planning for SQL Replication 1Migration planning . . . . . . . . . . . . 1Memory planning . . . . . . . . . . . . 1Memory used by the Capture program . . . . 1Memory used by the Apply program . . . . . 3Storage planning . . . . . . . . . . . . . 3Log impact for DB2 source servers . . . . . . 3Log impact for target servers . . . . . . . . 4Storage requirements of target tables and controltables . . . . . . . . . . . . . . . . 4Space requirements for spill files for the Captureprogram . . . . . . . . . . . . . . . 6Space requirements for spill files for the Applyprogram . . . . . . . . . . . . . . . 6Space requirements for diagnostic log files (z/OS,Linux, UNIX, Windows) . . . . . . . . . 7Conflict detection planning . . . . . . . . . 7Non-DB2 relational source planning . . . . . . 8Transaction throughput rates for Capture triggers 8Log impact for non-DB2 relational source servers 8Coexistence of existing triggers with Capturetriggers . . . . . . . . . . . . . . . 8Locks for Oracle source servers . . . . . . . 9Required changes for Sybase triggers onlittle-endian platforms . . . . . . . . . . 9Code page conversion planning . . . . . . . . 9Replication between databases with compatiblecode pages . . . . . . . . . . . . . . 9Code pages for SQL Replication . . . . . . 10Replication planning for DB2 for z/OS . . . . . 11Performance tuning. . . . . . . . . . . . 11Chapter 2. Authorization requirementsfor SQL Replication . . . . . . . . . 13Authentication requirements on Linux, UNIX, andWindows . . . . . . . . . . . . . .Authorization requirements for administration .Authorization requirements for the Captureprogram . . . . . . . . . . . . . .Authorization requirements for the Apply programAuthorization requirements for Capture triggers onnon-DB2 relational databases . . . . . . .Managing user IDs and passwords for remoteservers (Linux, UNIX, Windows) . . . . . . 13. 13. 1415. 17. 17Chapter 3. Configuring servers for SQLReplication . . . . . . . . . . . . . 19Required: Setting DATA CAPTURE CHANGES onDB2 source tables and DB2 for z/OS system tablesConnectivity requirements for SQL Replication .Connecting to System i servers from WindowsConnecting to non-DB2 relational servers . .Creating control tables for SQL Replication . . .Creating control tables for SQL Replication . .Creating control tables (System i) . . . . . Copyright IBM Corp. 1994, 2012.19202021212122Creating control tables for non-DB2 relationalsources . . . . . . . . . . . . . . .Creating multiple sets of Capture control tablesCreating control tables in a multiple-partitioneddatabase . . . . . . . . . . . . . .Setting up the replication programs . . . . . .Setting up the replication programs (Linux,UNIX, Windows) . . . . . . . . . . .Creating SQL packages to use with remotesystems (System i) . . . . . . . . . . .Setting up the replication programs (z/OS) . . .Capture for multiple database partitions . . . .Replication of partitioned tables: Version 9.7 FixPack 1 or earlier (Linux, UNIX, Windows) . . .Replication of partitioned DB2 tables: Version 9.7Fix Pack 2 or later (Linux, UNIX, Windows) . .Running DB2 Query Patroller in a SQLReplication environment . . . . . . . . .Setting up journals (System i) . . . . . . .232324252527292929313233Chapter 4. Registering tables andviews as SQL Replication sources . . . 39Registering DB2 tables as sources . . . . . . .Registering non-DB2 relational tables as sources . .Registration options for source tables . . . . . .Registering a subset of columns (verticalsubsetting). . . . . . . . . . . . . .Change-capture replication and full-refreshcopying. . . . . . . . . . . . . . .After-image columns and before-image columnsBefore-image prefix . . . . . . . . . . .Stop the Capture program on error . . . . .Options for how the Capture program storesupdates. . . . . . . . . . . . . . .Preventing the recapture of changes(update-anywhere replication) . . . . . . .Options for conflict detection (update-anywherereplication) . . . . . . . . . . . . .Registering tables that use remote journaling(System i) . . . . . . . . . . . . . .Referential integrity on the target table when thesource is System i . . . . . . . . . . .Using relative record numbers (RRN) instead ofprimary keys (System i) . . . . . . . . .How views behave as replication sources . . . .Views over a single table . . . . . . . . .Views over a join of two or more tables . . . .Registering views of tables as sources . . . . .Maintaining CCD tables as sources . . . . . .39414243434447474848525354555555565858Chapter 5. Subscribing to sources forSQL Replication . . . . . . . . . . . 61Planning how to group sources and targets .Planning the number of subscription-setmembers . . . . . . . . . . . 61. 61iii
Planning the number of subscription sets perApply qualifier . . . . . . . . . . . .Creating subscription sets . . . . . . . . .Processing options for subscription sets . . . . .Specifying whether the subscription set is activeSpecifying how many minutes worth of data theApply program retrieves . . . . . . . . .Load options for target tables with referentialintegrity . . . . . . . . . . . . . .Specifying how the Apply program replicateschanges for subscription-set members . . . .Defining SQL statements or stored procedures forthe subscription set . . . . . . . . . . .Options for scheduling replication of subscriptionsets . . . . . . . . . . . . . . . .Scheduling the subscription set . . . . . . .Creating subscription-set members . . . . .Target table types . . . . . . . . . . .Common properties for all target table types . .62636565666768696971717385Chapter 6. Replicating special datatypes in SQL Replication . . . . . . . 91General data restrictions for SQL Replication .Large object data types . . . . . . . .Replication of new DB2 Version 9.7 data types(Linux, UNIX, Windows) . . . . . . . .Replication of tables with identity columns . . 91. 92. 93. 94Chapter 7. Subsetting data in an SQLReplication environment . . . . . . . 97Subsetting data during registration . . .Subsetting source data using views . .Defining triggers on CD tables to preventrows from being captured . . . . .Subsetting data during subscription . . . . . . .specific. . . . .97989899Chapter 8. Manipulating data in anSQL Replication environment . . . . 101Enhancing data by using stored procedures orstatements . . . . . . . . . . . .Mapping source and target columns that havedifferent names. . . . . . . . . . .Creating computed columns . . . . . .SQL. . 102. 102. 103Chapter 9. Operating the Captureprogram for SQL Replication. . . . . 105Starting the Capture program (Linux, UNIX,Windows, and z/OS) . . . . . . . . . .Starting the Capture program from a known pointin the DB2 log . . . . . . . . . . . .Starting the Capture program (System i) . . .Default operating parameters for the Captureprogram . . . . . . . . . . . . . .Descriptions of Capture operating parameters .Methods of changing Capture parameters . . .Altering the behavior of a running Captureprogram . . . . . . . . . . . . . .Changing saved operating parameters in theIBMSNAP CAPPARMS table . . . . . . .ivSQL Replication Guide and Reference. 105. 107. 107. 108. 110. 119. 121. 122Stopping the Capture program . . . . . . .Reinitializing Capture . . . . . . . . . .Suspending the Capture program (Linux, UNIX,Windows, z/OS) . . . . . . . . . . . .Resuming Capture (Linux, UNIX, Windows, z/OS)123124124125Chapter 10. Operating the Applyprogram for SQL Replication. . . . . 127Starting the Apply program (Linux, UNIX,Windows, z/OS) . . . . . . . . . . . .Starting an Apply program (System i) . . . . .Default operating parameters for the Applyprogram . . . . . . . . . . . . . . .Descriptions of Apply operating parameters . . .Methods of changing Apply operating parametersChanging saved Apply parameters in theIBMSNAP APPPARMS table (z/OS, Linux, UNIX,Windows) . . . . . . . . . . . . . .Stopping the Apply program . . . . . . . .Where the Apply program stores details aboutreferential integrity failures . . . . . . . . .Modifying the ASNDONE exit routine (z/OS,Linux, UNIX, Windows) . . . . . . . . . .Modifying the ASNDONE exit routine (System i)Refreshing target tables by using the ASNLOADexit routine . . . . . . . . . . . . . .Refreshing target tables with the ASNLOAD exitroutine (Linux, UNIX, Windows) . . . . . .Refreshing target tables with the ASNLOAD exitroutine (z/OS) . . . . . . . . . . . .Customizing ASNLOAD exit behavior (z/OS,Linux, UNIX, Windows) . . . . . . . . .Refreshing target tables with the ASNLOAD exitroutine (System i) . . . . . . . . . . .Refreshing one table in a multi-tablesubscription set. . . . . . . . . . . .Ensuring that utilities used for full refresh waitfor committed data . . . . . . . . . 50Chapter 11. Operating the replicationprograms (z/OS) . . . . . . . . . . 153Using system-started tasks to operate thereplication programs . . . . . . . . . .Using JCL to operate replication programs . .Starting the Apply program on z/OS with JCL .Working with running SQL replication programsby using the MVS MODIFY command . . . .Starting the Capture program on z/OS with JCLUsing Automatic Restart Manager (ARM) toautomatically restart replication and publishing(z/OS). . . . . . . . . . . . . . .Migrating your replication environment todata-sharing mode (z/OS) . . . . . . . . 153. 153. 154. 155157. 158. 159Chapter 12. Changing an SQLReplication environment . . . . . . 161Registering new objects . . . .Changing registration attributes forobjects. . . . . . . . . .Adding columns to source tables . . . .registered. . . . . . . 161. 162. 162
Handling of ALTER TABLE ALTER COLUMN SETDATA TYPE operations . . . . . . . . . .Stop capturing changes for registered objects . . .Making registrations eligible for reactivation . . .Removing registrations . . . . . . . . . .Changing Capture schemas. . . . . . . . .Creating new subscription sets . . . . . . .Adding new subscription-set members to existingsubscription sets . . . . . . . . . . . .Disabling subscription-set members from existingsubscription sets . . . . . . . . . . . .Enabling subscription-set members to existingsubscription sets . . . . . . . . . . . .Changing properties of subscription sets . . . .Changing subscription set names . . . . . . .Splitting a subscription set . . . . . . . . .Merging subscription sets . . . . . . . . .Changing Apply qualifiers of subscription sets . .Deactivating subscription sets . . . . . . . .Removing subscription sets. . . . . . . . .Coordinating replication events with databaseapplication events . . . . . . . . . . . .Setting an event END SYNCHPOINT by usingthe USER type signal . . . . . . . . . .When to use the Capture CMD STOP signal . .Performing a CAPSTART handshake signaloutside of the Apply program . . . . . . .Performing a CAPSTOP signal . . . . . .Adjusting for Daylight Savings Time (System i)Options for promoting your replicationconfiguration to another system . . . . . . 83183184187188189190Chapter 13. Maintaining an SQLReplication environment . . . . . . 193Maintaining source systems . . . . . . . .Access to source tables and views . . . . .Source logs and journal receivers . . . . . .Maintaining control tables . . . . . . . . .The RUNSTATS utility for SQL Replication(Linux, UNIX, Windows, z/OS) . . . . . .Rebinding packages and plans (z/OS, Linux,UNIX, Windows) . . . . . . . . . . .Reorganizing your control tables . . . . . .Pruning dynamic control tables maintained bythe Capture programs (Linux, UNIX, Windows,z/OS) . . . . . . . . . . . . . . .CD and UOW table pruning . . . . . . .Recommendations for pruning other dynamiccontrol tables . . . . . . . . . . . .Preventing replication failures and recoveringfrom errors . . . . . . . . . . . . .Maintaining target tables . . . . . . . . .193193193196197197197. . .mode. . . . . 213Chapter 15. Scheduling SQLReplication programs on variousoperating systems . . . . . . . . . 219Scheduling programs on Linux and UNIXoperating systems . . . . . . . . . . . . 219Scheduling programs on Windows operatingsystems . . . . . . . . . . . . . . . 219Scheduling programs on z/OS operating systems220Scheduling programs on the System i operatingsystem . . . . . . . . . . . . . . . 220Chapter 16. Replication services(Windows). . . . . . . . . . . . . 221Description of Windows services for replicationCreating a replication service . . . . . .Starting a replication service . . . . . .Stopping a replication service . . . . . .Viewing a list of replication services . . . .Dropping a replication service . . . . . .221222222223223223Chapter 17. How the SQL Replicationcomponents communicate. . . . . . 225The Replication Center, ASNCLP, the Captureprogram or triggers, and the Apply program . .The Capture program and the Apply program .The Capture triggers and the Apply program. .The administration tools and the Replication AlertMonitor . . . . . . . . . . . . . .The Replication Alert Monitor, the Captureprogram, and the Apply program . . . . . 225. 226. 227. 228. 229Chapter 18. Checking the status of theSQL Replication programs. . . . . . 231Checking the status of replication programs (z/OS,Linux, UNIX, Windows) . . . . . . . . . . 231Checking the status of the Capture and Applyjournal jobs (System i) . . . . . . . . . . 232Monitoring the progress of the Capture program(System i) . . . . . . . . . . . . . . 232198199Chapter 19. Customizing and runningSQL scripts for replication. . . . . . 235200Chapter 20. Naming rules for SQLReplication objects . . . . . . . . . 237200202Chapter 14. Comparing and repairingtables. . . . . . . . . . . . . . . 205Table compare utility (asntdiff) . .Running the asntdiff utility in parallel(z/OS). . . . . . . . . . .Table repair utility (asntrep) . . .How the compare utility handles DB2 SQLcompatibility features. . . . . . . . 205. 210. 213Chapter 21. System commands forSQL Replication (Linux, UNIX,Windows, z/OS) . . . . . . . . . . 239asncap: Starting Capture. . . .asnccmd: Operating Capture . .asnapply: Starting Apply . . .asnacmd: Operating Apply . . .asnanalyze: Operating the Analyzer.239248251257258Contentsv
asnpwd: Creating and maintaining password filesasnscrt: Creating a replication service . . . .asnsdrop: Dropping a replication service . . .asnslist: Listing replication services . . . . .asntdiff: Comparing data in source and targettables (Linux, UNIX, Windows) . . . . . .asntdiff: Comparing data in source and targettables (z/OS) . . . . . . . . . . . .asntdiff –f (input file) command option. . . .asntrc: Operating the replication trace facility. .asntrep: Repairing differences between source andtarget tables . . . . . . . . . . . . .261. 265. 268. 269. 270. 274. 281. 284. 291Chapter 22. System commands forSQL replication (System i) . . . . . . 295ADDDPRREG: Adding a DPR registration (Systemi) . . . . . . . . . . . . . . . . .ADDDPRSUB: Adding a DPR subscription set(System i) . . . . . . . . . . . . . .ADDDPRSUBM: Adding a DPR subscription-setmember (System i) . . . . . . . . . . .ANZDPR: Operating the Analyzer (System i). . .CHGDPRCAPA: Changing DPR Capture attributes(System i) . . . . . . . . . . . . . .CRTDPRTBL: Creating the replication control tables(System i) . . . . . . . . . . . . . .ENDDPRAPY: Stopping Apply (System i) . . . .ENDDPRCAP: Stopping Capture (System i) . . .GRTDPRAUT: Authorizing users (System i) . . .INZDPRCAP: Reinitializing DPR Capture (Systemi) . . . . . . . . . . . . . . . . .OVRDPRCAPA: Overriding DPR Captureattributes (System i) . . . . . . . . . . .RMVDPRREG: Removing a DPR registration(System i) . . . . . . . . . . . . . .RMVDPRSUB: Removing a DPR subscription set(System i) . . . . . . . . . . . . . .RMVDPRSUBM: Removing a DPR subscription-setmember (System i) . . . . . . . . . . .RVKDPRAUT: Revoking authority (System i) . . .STRDPRAPY: Starting Apply (System i) . . . .STRDPRCAP: Starting Capture (System i) . . . .WRKDPRTRC: Using the DPR trace facility(System i) . . . . . . . . . . . . . 68375Chapter 23. SQL Replication tablestructures . . . . . . . . . . . . . 381Tables at a glance . . . . . . . . .Tables at the Capture control server . . .IBMSNAP AUTHTKN table (System i) .IBMSNAP CAPENQ table (z/OS, Linux,Windows) . . . . . . . . . .IBMSNAP CAPMON table . . . . .IBMSNAP CAPPARMS table . . . .IBMSNAP CAPSCHEMAS table . . .IBMQREP COLVERSION table . . .IBMSNAP CAPTRACE table . . . .CCD table (non-DB2) . . . . . . .CD table . . . . . . . . . . .IBMQREP IGNTRAN table. . . . .viSQL Replication Guide and Reference. . . . . . .UNIX,. . . . . . . . . . . . . . . . . . .381388390391391393397397398399400401IBMQREP IGNTRANTRC table . . .IBMSNAP PARTITIONINFO table . .IBMSNAP PRUNCNTL table . . . .IBMSNAP PRUNE LOCK table . . .IBMSNAP PRUNE SET table . . . .IBMSNAP REG EXT (System i) . . .IBMSNAP REGISTER table. . . . .IBMSNAP REG SYNCH table (non-DB2relational) . . . . . . . . . .IBMSNAP RESTART table . . . . .IBMSNAP SEQTABLE table (Informix) .IBMSNAP SIGNAL table . . . . .IBMQREP TABVERSION table . . .IBMSNAP UOW table . . . . . .Tables at the Apply control server . . .ASN.IBMSNAP APPENQ table . . .ASN.IBMSNAP APPLEVEL table . .ASN.IBMSNAP APPLY JOB (System i).ASN.IBMSNAP APPLYMON table . .ASN.IBMSNAP APPPARMS table . .ASN.IBMSNAP APPLYTRACE table .ASN.IBMSNAP APPLYTRAIL table . .ASN.IBMSNAP FEEDETL table . . .ASN.IBMSNAP SUBS COLS table . .ASN.IBMSNAP SUBS EVENT table. .ASN.IBMSNAP SUBS MEMBR table .ASN.IBMSNAP SUBS SET table . . .ASN.IBMSNAP SUBS STMTS table . .Tables at the target server . . . . . .Base aggregate table . . . . . . .Change aggregate table . . . . . .CCD targets . . . . . . . . . .Point-in-time table. . . . . . . .Replica table . . . . . . . . .User copy table. . . . . . . . 6456Appendix A. UNICODE and ASCIIencoding schemes for SQL replication(z/OS). . . . . . . . . . . . . . . 459Rules for choosing an encoding schemeSetting encoding schemes . . . . . 459. 459Appendix B. Starting the SQLReplication programs from within anapplication (Linux, UNIX, Windows) . . 461Appendix C. How the Captureprogram processes journal entrytypes for SQL replication (System i)Contacting IBM. 463. . . . . . . . . . 465How to read syntax diagrams . . . . 467Notices and trademarks . . . . . . . 469Trademarks . 472Index . . . . . . . . . . . . . . . 475
Chapter 1. Planning for SQL ReplicationWhen planning for SQL Replication, you might need to consider planning formigration, memory, storage, conflicts, source systems, code page conversion, andperformance.Migration planningPlanning migration involves planning for issues that might arise while migratingfrom one version of replication to another.If you are migrating from an existing replication environment, certain migrationissues need to be considered. WebSphere Information Integration Migrating toReplication Version 9 describes how to migrate to Version 9 replication. To migrateto Version 9, your servers must first be at Version 8. WebSphere InformationIntegration Migrating to SQL Replication Version 8 describes how to migrate toVersion 8 replication. It also describes h
IBM InfoSphere Data Replication Version 10.1.3 SQL Replication Guide and Reference SC19-3638-00. IBM InfoSphere Data Replication Version 10.1.3 SQL Replication Guide and Reference SC19-3638-00. Note Before using this information and the product that it supports,
