How do I perform a manual full refresh instead of using the Apply program to do the full refresh?

转自：http://www-01.ibm.com/support/docview.wss?rs=58&context=SS9UMX&q1=manual+refresh&uid=swg21221699&loc=en_US&cs=utf-8&lang=en

Technote (FAQ)

Question

This article 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. When you perform a manual full refresh, the Apply program assumes that it has initialized your point-in-time copies.

Answer

You might want to use this technique in the following situations:

• To automate the loading of many copies of large tables
• To full-refresh tables that have referential integrity constraints, in order to bypass referential integrity checking

The steps below describe the manual full refresh process for a set that contains user copy targets for which the corresponding source tables are regular tables, not views.

The SQL statements below modify rows in the schema.IBMSNAP_REGISTER,
schema.IBMSNAP_PRUNCNTL, and schema.IBMSNAP_SIGNAL tables on the Capture control server where the master table is located, and they modify rows in the
schema.IBMSNAP_SUBS_SET table on the Apply control server.

1. Ensure that the Apply program is stopped, or use the following SQL statement to deactivate the subscription set:

UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE = 0
WHERE SET_NAME = ' set name' AND APPLY_QUAL = ' Apply qualifier';

2. Disable the full refresh capability for each source table in the subscription set, as shown in the following SQL statement:

UPDATE ASN.IBMSNAP_REGISTER SET DISABLE_REFRESH = 1 WHERE SOURCE_OWNER = ' source owner' AND
SOURCE_TABLE = ' source table';

3. If the Capture program is not running on the Capture control server, start the Capture program.

4. Update the SYNCHPOINT and SYNCHTIME columns in the
schema.IBMSNAP_PRUNCNTL table for each row that corresponds to a member
of the subscription set, as shown in the following SQL statements:

IMPORTANT: You might need to add additional columns to the WHERE clause to make the statement update only the correct rows. You might also need to set the synchpoint column to null if the column already contains zeroes otherwise the update is not logged.

UPDATE ASN.IBMSNAP_PRUNCNTL SET SYNCHPOINT = X'00000000000000000000', SYNCHTIME = CURRENT TIMESTAMP WHERE APPLY_QUAL = ' Apply qualifier'
AND SET_NAME = ' subscription set name' ; 

5. Use the following SQL statement to signal the Capture program to start saving changes in the change-data (CD) tables for the source table in the subscription set:

INSERT INTO ASN.IBMSNAP_SIGNAL (SIGNAL_TIME, SIGNAL_TYPE, SIGNAL_SUBTYPE, SIGNAL_INPUT_IN, SIGNAL_STATE)
(SELECT CURRENT TIMESTAMP, 'CMD', 'CAPSTART', MAP_ID, 'P'
FROM ASN.IBMSNAP_PRUNCNTL
WHERE SOURCE_OWNER = ' source owner'
AND SOURCE_TABLE = ' source table'
AND APPLY_QUAL = ' Apply qualifier'
AND SET_NAME = ' subscription set name'
AND TARGET_OWNER = ' target owner'
AND TARGET_TABLE = ' target table');

Please make sure that you issue the above sql statement for one source/target table at a time.

6. Use the following method to verify that the Capture program has processed the update that you made in the step above:

Query the schema.IBMSNAP_ PRUNCNTL table to look at the value in the SYNCHPOINT column. Capture changes the value from the hex zeroes you provided to a nonzero hex value as soon as Capture finds the log records written for the updates issued in step 4. If this value no longer appears as zeroes, then the Capture program has performed the necessary processing for you to continue to step 7.

7. Unload from the master table and load the target table using your preferred method.

8. Update the schema.IBMSNAP_SUBS_MEMBR table as shown in the following SQL statement to specify that members of the subscription set were loaded:

UPDATE ASN.IBMSNAP_SUBS_MEMBR SET MEMBER_STATE = 'L'
WHERE SET_NAME = ' set name'
AND APPLY_QUAL = ' Apply qualifier';

9. Update the schema.IBMSNAP_SUBS_SET table as shown in the following SQL statement:

IMPORTANT: You might need to add additional columns to the WHERE clause to make the statement update only the correct row.

UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE = 1,
LASTRUN = CURRENT TIMESTAMP,
LASTSUCCESS = CURRENT TIMESTAMP,
SYNCHTIME = CURRENT TIMESTAMP,
SYNCHPOINT = NULL
WHERE SET_NAME = ' subscription set name'
AND APPLY_QUAL = ' Apply qualifier'

10. Start the Apply program.