DB2 Replication Guide and Reference

Additional Apply Program Operations

The following section provides information about performing the additional Apply program functions of scheduling subscriptions, refreshing target tables, returning control to users, initiating a forward recovery, and loading tables within a subscription set.

Scheduling Subscriptions with the Event Table

You can include an event name in your replication subscription. Your subscription cycles can run according to a timer, an event occurrence, or both. See Setting the Copying Schedule: Time or Event Based for more information about the purpose and usage of event scheduling.

You can schedule a subscription by manipulating the event table. The DDL statement for the event table appears as follows:

CREATE TABLE
  ASN.IBMSNAP_SUBS_EVENT (
  EVENT_NAME CHAR(18) NOT NULL,
  EVENT_TIME TIMESTAMP NOT NULL,
  END_OF_PERIOD TIMESTAMP);
CREATE UNIQUE INDEX ASN.IBMSNAP_SUBS_EVENT
  ON ASN.IBMSNAP_SUBS_EVENT (EVENT_NAME ASC, EVENT_TIME ASC);

For example, to start the Apply program at 6:00 p.m. on 9/24/97 and copy changes from the source server up to 4:00 p.m. on the same date, you would insert the following values in ASN.IBMSNAP_SUBS_EVENT:

('EVENTA','1997-09-24-18.00.00.00000','1997-09-24-16.00.00.00000'),

A subscription becomes eligible for processing when EVENT_TIME falls between LASTSUCCESS and the control server's CURRENT TIMESTAMP. The END_OF_PERIOD column enables you to specify a timestamp upper limit for change data.

Refreshing Target Tables with ASNLOAD

ASNLOAD is a user exit program that the Apply program invokes when you specify the LOADX invocation parameter. The Apply program calls the ASNLOAD program whenever it performs a full refresh of a target table.

You can use the ASNLOAD program as shipped with the Apply program, or you can modify it. As shipped, ASNLOAD uses the EXPORT utility to export data from the source table and uses the LOAD utility to fully refresh the target table.

You can modify ASNLOAD to call any IBM or vendor utility. See the prolog section in the sample program (ASNLOAD.SMP) for instructions on how to modify the program to meet the requirements of your installation.

You must use ASNLOAD to fully refresh tables with referential integrity constraints in order to bypass referential integrity checking.

Files Generated

If you invoke ASNLOAD, the following files are generated:

<apply_qual>.IXF
This file contains the data exported from the source.
<apply_qual>.EXP
This file contains error, warning, or informational messages issued by the EXPORT APIs.
<apply_qual>.LOA
This file contains error, warning, or informational messages issued by the LOAD APIs.

Error Handling

If an error occurs while the Apply program calls ASNLOAD, or if ASNLOAD returns a nonzero return code, the Apply program issues a message, stops processing that subscription, and processes the next subscription.

Returning Control to Users with ASNDONE

If you specify the NOTIFY parameter when starting the Apply program, the user exit program ASNDONE is called after subscription processing completes, regardless of success or failure. You can modify ASNDONE to meet the requirements of your installation. For example, the user exit can examine the UOW table to discover rejected transactions and initiate further actions, such as issuing a message or generating an alert.

See the prolog section in the sample program (ASNDONE.SMP) for instructions on how to modify the program.

Initiating a Forward Recovery with the Apply Program

In cases of an incomplete rollback or partially restored backup, you might need to copy a "window" of changes for completion.

To use the Apply program to limit the range of changes to those excluded from the rollback:

Determine how much data is lost.
Check the IBMSNAP_LOGMARKER value in the point-in-time table. The highest value is the most recently committed change.
Use SQL to identify the range of changes. Either:
- Use the WHERE clause on the Rows page of the Advanced Subscription Definition notebook.
  or
- Manually update the PREDICATES column of the control server subscriptions table (ASN.IBMSNAP_SUBS_MEMBR).
For example, if your timestamp shows the last committed change to be 941106174322, your SQL should reflect changes that occur after that time.

Loading the Tables within a Subscription Set

The following section describes how to load the tables of a subscription set as an alternative to using the LOADX option when running the Apply program. This technique involves performing your own full refresh on behalf of the Apply program. By doing your own full refresh, the Apply program assumes that it has initialized your point-in-time copies. You may want to use this technique in the following situations:

To automate the loading of many, large copies of tables.
To fully-refresh tables with referential integrity constraints, in order to bypass referential integrity checking.

Note:

Before you begin, ensure that the Capture program is running.

The following example assumes that you want to perform a load of the tables within the replication subscription SET001 with an Apply program qualifier of APPLY001.

Optional: It is recommended that you disable the full refresh capability for the applicable source tables as shown in the following SQL statements; the Apply program then issues an error message, rather than performing a full refresh if the following procedure is not performed correctly.
```
UPDATE ASN.IBMSNAP_REGISTER SET DISABLE_REFRESH = 1
WHERE SOURCE_OWNER = 'source owner' AND
SOURCE_TABLE = 'source table'
```
Ensure that the Apply program is inactive, or deactivate the applicable replication subscription as shown in the following SQL statements:
```
UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE = 0
WHERE SET_NAME = 'SET001' AND APPLY_QUAL = 'APPLY001'
```
Update SYNCHPOINT and SYNCHTIME in ASN.IBMSNAP_PRUNCNTL for each row that corresponds to a member of the replication subscription as shown in the following SQL statements:
```
UPDATE ASN.IBMSNAP_PRUNCNTL
SET SYNCHPOINT = x'000000000000000000000',
SYNCHTIME  = CURRENT TIMESTAMP
WHERE SET_NAME = 'SET001' AND APPLY_QUAL = 'APPLY001'
```
Verify that the Capture program has processed the updates in the step above.
- If the Capture program has not started capturing changes for these tables, this update initiates the process. ASN.IBMSNAP_TRACE will contain GOCAPT messages for the source tables.
- If the Capture program has previously started capturing changes for these tables (other subscriptions exist for the tables and these have already been activated), you can verify that this pruning control table update was successful by querying the same SYNCHPOINT values that were updated to hex zeroes. When these values no longer appear as zeroes, the Capture program has performed the necessary processing for you to continue.
Unload the source table and load the target table, using DB2 or vendor utility programs.

Update ASN.IBMSNAP_SUBS_SET as shown in the following SQL statements:

UPDATE ASN.IBMSNAP_SUBS_SET
SET LASTRUN = CURRENT TIMESTAMP,
LASTSUCCESS = CURRENT TIMESTAMP,
SYNCHTIME = CURRENT TIMESTAMP,
SYNCHPOINT = NULL
WHERE SET_NAME = 'SET001' AND APPLY_QUAL = 'APPLY001'

Start the Apply program, or set ACTIVATE = 1 for the applicable replication subscription row in ASN.IBMSNAP_SUBS_SET as shown in the following SQL statements:
```
UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE = 1
WHERE SET_NAME = 'SET001' AND APPLY_QUAL = 'APPLY001'
```

Note:

In cases where you are trying to identify problems by manually resetting values in the PRUNCNTL table, note that the PRUNCNTL table's SYNCPOINT values need to match the CD_OLD_SYNCHPOINT values in the REGISTER table. If the values do not match, then the Apply program will want to perform a full refresh.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]