Refresh preparation - connect_cdc_sqdata - Latest

Preparing for Dynamic Refresh requires both some one-time and source table specific consideration. Dynamic refresh will utilize different linear dataset(s) to minimize the impact on the flow of existing Change Data captured while the refresh is in process. While the directory where the capture .cab file is located will be used by default, it must have enough free space to contain the largest refresh slice. For this reason a secondary mount point may be needed that can be adjusted to accommodate subsequent initial load / refresh requests.

One time activities:

1. Creation of the Connect CDC SQData refresh control table (SQDATA.REFRESH_REQUEST_LOG) on the source system. Job CRDREFR in the Connect CDC SQData CNTL library can be used to create this control table.
2. Examine the Db2 statistics for the table(s) that will be the source for the Dynamic Refresh. Determine the number of rows to be extracted at one time (the refresh slice size) for the largest table. Multiply the slice size by the average length or size of the rows in that table to estimate how much space is required for a single slice. Decide if the default location, the directory where the capture .cab file is located will be adequate. If not, prepare a second transient area following the instructions used to Create zFS Transient Data Filesystem for the existing Db2/z Log Reader Capture Agent. This section assumes the directory created is: /home/sqdata/db2cdc/refreshdata
3. If required, register the new transient area in the Capture Configuration .cab files using the sqdconf utility. Using using JCL similar to sample member SQDCONDC included in the distribution, modify the configuration to include the new transient area (Note, this is not presently a supported function of the ISPF panels):

//*-------------------------------------------
//* Add a Db2 Refresh Transient area
//*-------------------------------------------
//CREFAREA EXEC PGM=SQDCONF
//SYSOUT DD SYSOUT=*
//SQDPARMS DD *
 modify /home/sqdata/db2cdc/db2cdc.cab 
 --work-storage=/home/sqdata/db2cdc/refreshdata
/*

Individual Table Refresh activities:

1. The Db2 Capture Task requires Read access to any table to be Refreshed, ie GRANT SELECT on <schema.tablename> TO < sqdata_user>; This is because the Capture actually pauses reading the Db2 Log and then Reads the Table to be used as the Refresh source. It may be possible or desirable to Grant select privileges to all tables in a Schema. Talk to your Db2 DBA to see what is possible in your environment.
2. The space required for a single slice for each table should be compared to the size of the existing transient storage area to confirm it will fit or if a larger Refresh transient area is required.
3. Consideration given to the exiting source application workload to determine an optimal time to schedule the initial load or refresh.
4. Determine if the table is published to multiple downstream Engines and targets datastores. The load/refresh can be disabled as needed for individual Engine subscriptions.