DB2 Replication Guide and Reference

Additional Apply Program Operations

The following sections provide information about performing the additional Apply program functions of scheduling subscriptions, 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.
Note: You must still ensure that the Capture program has processed through the log up to the end-of-period timestamp to ensure that all data up until that time has been captured.

Returning Control to Users with ASNDONE

ASNDONE is a user exit program that the Apply program invokes after set 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 inititiate further actions, such as issuing a message or generating an alert.

To use ASNDONE:

Modify the ASNDONE file to meet your site's requirements.
Compile the program and place the executable in the appropriate directory.
Start the Apply program with the NONOTIFY invocation parameter to call the ASNLOAD program.

The parameters passed to ASNDONE are as follows:

Control server name
Set name
Apply qualifier
WHOS_ON_FIRST value
Trace option
Status value

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 target 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 subscription 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 SYNCHPOINT 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 ]