This chapter describes how to operate the Capture and Apply programs generally. For information about operating either of these programs (for example, starting, stopping, or scheduling) in a specific operating system environment, see Operations. This chapter also describes regular database maintenance, monitoring replication, handling gaps, and modifying your replication configuration.
This section describes what you need to know before you start the Capture program, when you should perform a warm or cold start of the Capture program, and how to stop the Capture program with an event.
Before starting the Capture program, make sure that you complete the following post-installation tasks:
Defining replication sources and subscriptions creates the following control tables in the control server:
You can also create these control tables manually by running the DPCNTL.* file from the RUN SQL Files window.
When you start or restart the Capture program, you can include any of the following keywords: COLD, WARM, or WARMNS. If you are starting the Capture program for the first time, specify either COLD or WARM to cold start the Capture program. If you are restarting the Capture program after being shut down or after a failure, specify either WARM or WARMNS to warm start the Capture program. The following sections describe cold and warm starts, including how the Capture program handles warm starts, when it switches to an automatic cold start, and how to prevent an automatic cold start by forcing a warm start.
When you cold start the Capture program, it deletes all rows from the CD tables and the UOW table and begins reading the end of the database log. Specify a cold start by including the COLD keyword when you start the Capture program. A warm start can also become a cold start in certain circumstances; see Automatic cold start.
After a cold start, the Apply program performs a full refresh of the target tables. You can specify the LOADX keyword when you start the Apply program to improve the performance of the full refresh, or you can use the technique described in Loading target tables offline using DJRA.
When you stop the Capture program or if it fails, it writes information in the warm start control table to enable a warm start. There are cases when the Capture program cannot save warm start information. For example, an operator might cancel the Capture program or stop DB2. In this case, the Capture program uses information in the CD, UOW, or register tables to resynchronize to the time it stopped and thus allow a warm start.
When you restart the Capture program with the WARM or WARMNS keywords, it looks in the warm start table (or in the CD, UOW, or register tables) to determine if it can warm start or if it must cold start. If there is sufficient warm start information, the Capture program warm starts, otherwise it attempts a cold start; see Automatic cold start.
After a successful warm start, the Capture program deletes old rows in the warm start table.
If the Capture program cannot warm start, it attempts to perform a cold start. But, if you specify the WARMNS keyword, the Capture program does not cold start. The Capture program automatically switches to a cold start when:
The first time you start the Capture program, you see message ASN0102W, indicating that warm start failed. The Capture program switches to a cold start. You can ignore this message when first starting the Capture program.
In each of these cases, the Capture program issues an informational message and performs a cold start. This cold start also causes a gap in the change data capture sequence because the Capture program jumps to a new position in the database log.
To prevent the Capture program from attempting a cold start, specify the WARMNS keyword when starting the Capture program. If the warm start is not possible, instead of cold starting, the Capture program terminates. When the Capture program terminates in this way, the control tables remain intact. You must correct the problem that caused the Capture program to terminate before you attempted to restart it. If you do not correct the problem, the Capture program continues to terminate or performs a cold start every time that you restart it.
If you are starting the Capture program for the first time, or after stopping both the Capture and Apply programs, use the following steps:
See Defining replication sources and Defining replication subscription sets.
Wait for the initialization message that indicates that the Capture program is running. The Capture program does not capture changes until the Apply program starts and completes a full refresh.
The Apply program performs a full refresh for all subscription-set members. When the full refresh is complete, the Capture program begins capturing changes to the source tables.
This section describes what you need to know before you start the Apply program and how to use the Apply program for forward recovery. For information about operating the Apply program (for example, starting, stopping, or scheduling) in a specific operating system environment, see Operations.
Before you start the Apply program, ensure that:
See the Apply for OS/390 program directory for information on BIND programs to create the Apply program packages. You must bind the Apply program to both the source and target databases.
See Optional: Configuring the Apply program manually for Windows and OS/2 for information on bind programs to create the Apply packages for Windows and OS/2.
See Optional: Configuring the Apply program manually for UNIX platforms for information on bind programs to create Apply packages for UNIX platforms.
See Creating packages to use with remote systems for information about the CRTDPRPKG command, which creates the packages that are necessary for DataPropagator Relational for AS/400 to work with remote systems.
If you run the Apply program using a DBLIB connection for either Sybase or Microsoft SQL Server, and you have a slow network, you can significantly improve your overall replication performance. DB2 DataPropagator can use buffers to hold replicated data and send each buffer over the network rather than sending individual updates. You set the size of the buffers using the create server option statement. To take advantage of this improvement:
SELECT PKGNAME FROM SYSCAT.PACKAGES WHERE PKGNAME LIKE 'ASN%'
The package names change with each release and with each service update, but this query retrieves names that are specific to your service level.
ASN6A001+ ASN6B001+ ASN6C001+ ASN6F001+ ASN6I001+ ASN6M001+ ASN6P001
create server option apply_packet_size for server type sybase setting 16384; create server option apply_buffer_size for server type sybase setting 16384;
Sample sever options for Microsoft SQL Server are:
create server option apply_packet_size for server type mssqlserver setting 16384; create server option apply_buffer_size for server type mssqlserver setting 16384;
You can set the packet and buffer size to any appropriate value less than or equal to the maximum setting for Sybase or Microsoft SQL Server, and adjust as necessary.
DJX_ASYNC_APPLY=TRUE
In addition to the regular maintenance that you perform for your databases, running replication requires you to perform the following maintenance:
You should reorganize the CD tables and UOW table about once a week if they are heavily used. For DB2 for OS/390 Version 5 or later, specify the PREFORMAT keyword. Preformatting the table space speeds up the Capture program's insert processing. If the table space is compressed, you must also specify the KEEPDICTIONARY keyword.
Because subscription predicates are very selective and can filter out a majority of the transaction updates, there is no general rule for how frequently you should reorganize target tables, but it should be at least as frequently as you reorganize the source tables.
At the end of each subscription cycle, the Apply program inserts a row into the Apply trail control table. To prevent this table from growing too large, you need to delete these rows regularly. You can delete these rows whenever you like because although the Apply program writes to this table, it does not read from it. The subscription statistics and error diagnostics written to this table are for your benefit, and they are used by the Replication Monitor. An easy way to manage the growth of this table is to add an SQL statement to the subscription set; for example:
DELETE FROM ASN.IBMSNAP_APPLYTRAIL WHERE LASTRUN < (CURRENT TIMESTAMP - 7 DAYS);
Neither the Capture program nor the Apply program automatically prunes CCD tables, and there is no command for pruning these tables. Condensed CCD tables are updated in place, so do not grow continually. Noncondensed CCD tables contain history, which you likely want to retain.
A condensed, noncomplete, internal CCD table grows, and with enough update activity, can approach the size of a complete CCD table. Because only the most recent changes are retrieved from it, there is no value in letting this table grow. To prune transactions that have already been replicated from this table, add an SQL statement to the internal CCD's subscription; for example:
DELETE FROM my.internal_ccd WHERE IBMSNAP_COMMITSEQ <= (SELECT MIN(SYNCHPOINT) FROM ASN.IBMSNAP_PRUNCNTL);
This statement prunes the table based on the least active subscriptions, not just those subscriptions that refer to the source associated with the internal CCD table, so you might want to modify the statement for more aggressive pruning.
The following operational procedures typically require exclusive use of DB2 table spaces or the catalog:
REORG
BIND PACKAGE
BIND PLAN
GRANT
REVOKE
Because these operational procedures do not coexist well with the Capture and Apply programs' issuing dynamic SQL (which implicitly locks catalog tables) or accessing table spaces, you should stop both the Capture and Apply programs when running utilities (and other similar operational procedures) to avoid any possible contention.
You can use the Replication Monitor, included with the DJRA, to periodically generate a report that shows how your replication network is working:
To start the Replication Monitor, click Monitor replication on the main DJRA window. From the Replication Administration Scheduler window, you can schedule the monitor to run periodically or you can run it once.
Occasionally, a gap can occur during the capturing of changed data for a source table. For example, if you shut down the Capture program and then cold start it, it deletes all rows from the CD table. In this case, updates might be made that the Capture program does not capture. Or, any updates that were in the CD table could have been deleted (by the cold start) before the Apply program could replicate them.
When a gap exists, the Apply program attempts a full refresh unless the target table is a noncomplete CCD table. If the Apply program cannot perform a full refresh, data integrity could be lost. For noncomplete CCD tables, you can avoid potential data-integrity loss that could result from a cold start of the Capture program by using the following steps:
After you begin replication, you can change the configuration, including changing replication sources or subscriptions, removing sources or subscriptions, deactivating subscriptions, and cloning subscriptions.
Using either the DB2 Control Center or DJRA, you can view an existing replication source. With the Control Center, if you selected the Table will be used for update anywhere check box, you can change the conflict-detection level defined for the replication source. All other fields and controls are unavailable for changes after you successfully define the replication source. With DJRA, you can change the set of columns available for replication. 30
If you plan to change the replication source definition, use the Capture REINIT command. You can also stop or suspend the Capture program and then warm start or reinitialize the Capture program to begin capturing changes for the changed replication source. For information about the Capture program for your operating system environment, see Operations.
When you no longer need a replication source, you can remove the object from the DB2 Control Center or DJRA and remove its control information from the control tables.
Attention:
The Control Center and DJRA drop the table space for a DB2 replication source if it is empty. DJRA does not drop non-IBM database containers (table spaces, dbspaces, or segments). For the Control Center, you can ensure that the table space is never dropped by changing the settings on the Replication page of the Tools Settings notebook.
From the DB2 Control Center or DJRA, you can control the active status of a subscription set. This feature is useful when you want to temporarily deactivate a subscription set without removing it. When you deactivate a subscription set, the Apply program completes its current processing cycle and then suspends operation for that subscription set. In the Control Center, when you deactivate a subscription set, the icon for the subscription set is greyed out.
Using the DB2 Control Center, you can clone a subscription set to another server. Cloning creates a copy of an existing subscription set on a different target server, using a different Apply qualifier. This copy includes only subscription information; it does not include copy table, table space, or index definitions. You can clone one or more subscription sets at a time. The Control Center updates the control tables at the control server.
For information about copying your entire replication environment to another system, see Copying your replication configuration to another system.
Using the DB2 Control Center, you can change a subset of the subscription-set values, primarily those that do not affect the structure of the target tables. You can change the following values in the Change Replication Subscription window and subwindows:
To view or change existing subscription-set members using DJRA, click the List Members or Add a Column to Target Tables button. Fill in the required information in the window, such as source-server name and source-table names, then optionally fill in the source-column name or SQL expression and the target-column name to add new columns or add computed columns to the target table. 31
Removing a subscription-set definition deletes information about it from the control tables and, optionally, deletes the target tables from the target server. For non-IBM target tables, you can choose whether to drop the nickname and the corresponding target table when you remove the subscription set using DJRA.
Using the DB2 Control Center, select one or more replication subscription objects from the contents pane and select Remove from the pop-up menu. Using DJRA, you must first remove all members from the subscription set, then you can remove the empty subscription set.