DB2 Replication Guide and Reference

Using Your Own Full Refresh Technique for External CCD Tables

This section uses an example to describe the manual load procedure to use when the source table is a CCD table and there is a Capture program at the source site. If there is no Capture program at the source site, you will need to modify the procedure.

In this example:

• The source table has been defined as a replication source.

• A subscription has been defined with the following attributes:

  • Subscription name: CCDSUB

  • Subscription qualifier: CCDQUAL

  • Source Table name: SOURCE_TABLE

  • Target Table name: SOURCE_CCD

  • Target Type: Staging table used as a source for copies

• A second subscription has been defined with the following attributes:

  • Subscription name: UCSUB

  • Subscription qualifier: UCQUAL

  • Source Table name: SOURCE_CCD

  • Target Table name: SOURCE_USER_COPY

  • Target Type: User copy

The following steps describe the manual load procedure:

1. Disable full refresh

   Issue the following SQL on the server where the SOURCE_TABLE is located:

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

   Issue the following SQL on the server where the SOURCE_CCD table is located:

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

2. Deactivate the subscriptions (or stop the Apply processes)

   Issue the following SQL on the control server for the CCD subscription:

   UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE = 0
   WHERE SET_NAME = 'CCDSUB' AND APPLY_QUAL = 'CCDQUAL'
   

   Issue the following SQL on the control server for the user copy subscription:

   UPDATE ASN.IBMSNAP_SUBS_SET ACTIVATE = 0
   WHERE SET_NAME = 'UCSUB' AND APPLY_QUAL = 'UCQUAL'
   

3. If it is not already started, start the Capture program on the server where the SOURCE_TABLE is located. It is very important that you start the Capture program before proceeding to the next step.

4. Update the ASN.IBMSNAP_PRUNCNTL row for the CCD subscription

   Issue the following SQL on the server where the SOURCE_TABLE is located:

   UPDATE ASN.IBMSNAP_PRUNCNTL
            SET_SYNCHPOINT = x'000000000000000000000',
                SYNCHTIME  = CURRENT TIMESTAMP
   WHERE SET_NAME = 'CCDSUB' AND APPLY_QUAL = 'CCDQUAL'
   

5. Verify that the Capture program has processed the PRUNCNTL update

   Issue the following SQL on the server where the SOURCE_TABLE is located:

   SELECT MIN(SYNCHPOINT),SYNCHTIME
        FROM ASN.IBMSNAP_PRUNCNTL
   WHERE SET_NAME = 'CCDSUB' AND APPLY_QUAL = 'CCDQUAL'
   

   Make a note of these values since they will be used later (in this example as ccdsynchp and ccdsyncht).

6. Unload the SOURCE_TABLE and load the SOURCE_USER_COPY using DB2 or vendor utility programs

   Note:

   If you want to bypass the full refresh, you can skip this step. However, source updates committed between the last change processed by the Apply program and the update of ASN.IBMSNAP_PRUNCNTL will not be replicated.

7. Update the ASN.IBMSNAP_REGISTER row for the SOURCE_CCD

   Issue the following SQL on the server where the SOURCE_CCD is located:

   UPDATE ASN.IBMSNAP_REGISTER
       SET CCD_OLD_SYNCHPOINT = ccdsynchp,
           SYNCHPOINT = ccdsynchp,
           SYNCHTIME = ccdsyncht
   WHERE SOURCE_OWNER = 'source owner' AND
         SOURCE_TABLE = 'SOURCE_CCD'
   

   Note that ccdsynchp and ccdsyncht are the values from Step 5.

8. Update the ASN.IBMSNAP_SUBS_SET row for the SOURCE_CCD

   Issue the following SQL on the control server for the CCD subscription:

   UPDATE ASN.IBMSNAP_SUBS_SET
       SET LASTRUN = CURRENT TIMESTAMP
           LASTSUCCESS = CURRENT TIMESTAMP
           SYNCHTIME = CURRENT TIMESTAMP
           SYNCHPOINT = NULL
   WHERE SET_NAME = 'CCDSUB' AND APPLY_QUAL = 'CCDQUAL'
   

9. Update the ASN.IBMSNAP_PRUNCNTL row for the user copy subscription

   Issue the following SQL on the server where the SOURCE_CCD is located:

   UPDATE ASN.IBMSNAP_PRUNCNTL
            SET SYNCHPOINT = ccdsynchp,
                SYNCHTIME = ccdsyncht
   WHERE SET_NAME = 'UCSUB' AND APPLY_QUAL = 'UCQUAL'
   

   Note that ccdsynchp and ccdsyncht are the values from Step 5.

10. Update the ASN.IBMSNAP_SUBS_SET row for the SOURCE_USER_COPY

    Issue the following SQL on the control server for the user copy subscription:

    UPDATE ASN.IBMSNAP_SUBS_SET
        SET LASTRUN = ccdsyncht,
            LASTSUCCESS = ccdsyncht
            SYNCHTIME = ccdsynchp
            WHERE SET_NAME = 'UCSUB' AND APPLY_QUAL = 'UCQUAL'
    

11. Start the Apply program or set ACTIVATE = 1 for the applicable subscription set row in ASN.IBMSNAP_SUBS_SET

    Issue the following SQL on the control server for the CCD subscription:

    UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE = 1
    WHERE SET_NAME = 'CCDSUB' AND APPLY_QUAL = 'CCDQUAL'
    

    Issue the following SQL on the control server for the user copy subscription:

    UPDATE ASN.IBMSNAP_SUBS_SET SET ACTIVATE = 1
    WHERE SET_NAME = 'UCSUB' AND APPLY_QUAL = 'UCQUAL'
    

    Note:

    Since this example uses two Apply qualifiers, you would need to start an Apply process for each qualifier.

[ DB2 List of Books | Search the DB2 Books ]