Connect CDC SQData

The implementation of UDB (Db2/LUW) change data capture frequently requires the initial load of the target datastores in order for replication to achieve full synchronization of the source and target.

Connect CDC SQData provides the ability to refresh an entire table during replication. This allows for loading small to medium sized tables within the replication flow vs having to perform an utility unload/load. Larger tables may still require a utility unload/load.

Refresh works automatically with no-map replication scripts. Other apply scripts that perform mapping, transformation, filtering, etc. are not supported automatically. If a refresh is initiated and the records are published to a non-replicate engine, the engine will fail unless you provide for the special refresh CDC records in the apply script. Please contact Precisely https://www.precisely.com/support for assistance.

If you have a table that is published to both a no-map replication engine and another type of apply engine, you can disable the refresh for the 2nd apply engine as described below.

Notes:

1.Before you can refresh a table during replication, you must create the Connect CDC SQData refresh control table (SQDATA.REFRESH_REQUEST_LOG) on the source system. The following SQL can be used to create this control table:

CREATE TABLE SQDATA.REFRESH_REQUEST_LOG

(                                        

 STCK_START         CHAR(18) NOT NULL    

,STCK_END           CHAR(18) NOT NULL    

,STATUS             CHAR NOT NULL        

,TABLE_TO_REFRESH   VARCHAR(514) NOT NULL

,COMMENT            VARCHAR(256) NOT NULL

)                                        

IN SQDATADB.SQDATATS                      

DATA CAPTURE CHANGES                      
 

2.The refresh event is tracked in the refresh control table and you can browse this table to see your scheduled table refresh activity.

3.The capture must be running in order to perform a refresh.

4.The refresh is treated as a single transaction or unit-of-work (UOW), so consideration must be given to any restrictions that the target database may have with regard to large units-of-work.

5.A large refresh transaction will impact the throughput of other changes for the table that are occurring.

6.The current release of Connect CDC SQData only supports table refresh for no-map replication engines

The table refresh can be accomplished with a the sqdconf Utility.

Syntax

sqdconf --refresh --key=<TABLENAME>  <cab_file_name>

 

Keyword and Parameter Descriptions

--key= <TABLE_NAME> - Specifies Source object UDB table name. In our example the first table is SQDATA.EMP

<cab_file_name>= Must be specified and must match the name specified in a previous create command.

Example 1

Refresh table SQDATA.EMP to all replication engines defined in the Capture CAB file udbcdc1.cab using the SQDCONF Utility:

                               

sqdconf --refresh --key=SQDATA.EMP /home/sqdata/udbcdc1/udbcdc1.cab

/*                  

 

Disabling a table refresh

 

As noted above, all engines configured to received CDC data for a table will receive the published unit-of-work. To prevent a specific apply engine from receiving a table refresh unit-of-work, you can run an sqdconf batch job to block the refresh from that engine.

Syntax

sqdconf modify --block-refresh --target=<engine_name>

 

Example 1

Block the SQDATA.EMP table refresh from being published to UDBTOORA using the SQDCONF Utility:

sqdconf modify /home/sqdata/udbcdc1/udbcdc1.cab

  --block-refresh --target=UDBTOORA        

//*

 

Example 2

Un-Block all refreshes published to UDBTOORA using the SQDCONF Utility:

sqdconf modify /home/sqdata/udbcdc1/udbcdc1.cab

  --allow-refresh --target=UDBTOORA