The Db2/z Log Reader Capture Agent configuration (.cab) file is created and maintained by the sqdconf utility using JCL similar to sample member SQDCONDC included in the distribution. While this section focuses primarily on the initial configuration of the Capture Agent, sequences of SQDCONF commands to create and configure the capture agent can and should be stored in parameter files in case they need to be recreated. See SQDCONF Utility Reference for a full explanation of each command, their respective parameters and the utility's operational considerations.
sqdconf create <cab_file_name>
--type=DB2
[--ssid=<value>]
[--single-member]
[--ccsid=<coded_character_set_identifier>]
[--plan=<sqdata_plan>]
[--exclude-plan=<value>]
[--auto-exclude-plan=<y or n>]
[--exclude-user=<user_id>]
[--exclude-correlation-id=<value>]
[--encryption | --no-encryption]
[--auth-keys-list="<name>"]
[--high-threshold=<number>]
[--low-threshold=<number>]
--store=<store_cab_file_name>| Keyword | Description |
|---|---|
| [--ssid=<value>] |
Db2 z/OS only. Subsystem ID or the Db2/z Data Sharing Group or Member Name. See Note below regarding Data Sharing Environments. |
| [--single-member] - Db2 z/OS only | Consultation with Precisely support highly recommended. |
| --ccsid=<coded_character_set_identifier> |
The coded character set identifier or code page number of the Db2 Subsystem, default is 1047. |
| [--plan=<sqdata_plan>] |
Plan name used to connect to the DB2 subsystem. The default Plan is named SQDV4000 and need not be explicitly specified. This is an optional parameter that can be used to specify another Plan as needed. |
| [--exclude-plan=<name>] |
Exclude transactions associated with the given plan name from capture. This parameter can be repeated multiple time. |
| [--auto-exclude-plan=<y | n] |
Optionally exclude from capture Data that has been updated by an Apply Engine running under Connect CDC SQData's default Db2 Plan SQDV4000. Default is No (n). |
| [--exclude-user=<user_id>] |
Rarely used, will exclude database updates made by the specified User. |
| [--exclude-correlation-id=<value>] |
Exclude transactions with the given correlation id value from capture. This parameter can be repeated multiple times. While the translation of correlation Id can be modified, the defaults for are:
|
| [--encryption | --no-encryption] | Enables or disables NaCL encryption of the published CDC record payload. Precisely recommends zIIP processors be used to enhance CPU cycle efficiency and reduce CPU cost associated with NaCL software encryption. |
| [--auth-keys-list="<name>"] | Required for NaCL software encrypted CDC record payload. File name must be enclosed in quotes and must contain public key(s) of only the subscribing Engines requiring encryption of the CDC record payload. See --encryption option. |
| --store=<store_cab_file_name> |
Path and name of the Storage Agent Configuration (.cab) file. In our example, /home/sqdata/db2cdc/db2cdc_store.cab |
- Capture against multiple members in a data sharing group will result in capturing the same data multiple times.
- Controlling which member is used facilitate fail over rather than allowing Db2 to select the member name to capture from.
The configuration file must be updated by adding an entry for each table to be captured using the add command. Onlyonetable andoneassociated datastore (target subscription) can be added at a time. Precisely highly recommends keeping a Recover/Recreate configuration file Job or shell script available should circumstances require recovery.
Add each source table to the list of source tables to be captured in the Capture Configuration (.cab) file. Each table is identified by its name and schema. A datastore representing a single target subscription must be specified for each table added. Additional target subscriptions can be "added" but that is performed with the modify command It is important to develop a standard for how Datastores will be identified, particularly if a large number will be defined. The source is marked inactive by default but remember, even sources marked active will not be captured until changes to the configuration file are applied.
sqdconf add <cab_file_name>
--schema=<name> --table=<name> | --key=<name>
--datastore=<url>
[--active | --inactive]
[--pending]| Keyword | Description |
|---|---|
| <cab_file_name> |
Must be specified and must match the name specified in the previous create command. |
| --schema=<name> | Schema name, owner, or qualifier of a table. Different databases use different semantics, but a table is usually uniquely identified as S.T where S is referenced here as schema. This parameter cannot be specified with --key. |
| --table=<name> |
A qualified table name in the form of schema.name that identifies the source. This may be used in place of two parameters, --schema and --table. Both cannot be specified. |
| --key=<name> |
Same as --table |
| --datastore=<url> | -d <url> |
While most references to the term datastore describe physical entities, a datastore URL represents a target subscription and takes the form: cdc://[localhost]/[<agent_alias>]/<subscriber_name> where:
|
| [--active | --inactive] |
Mark a table as active or in-active for capture. The table will remain in the current state until the capture is stopped, applied and re-started. The default is --inactive. |
| [--pending] |
This parameter allows a table to be added to the configuration before it exists in the database catalog. |
Example
//SQDCONDC JOB 1,MSGLEVEL=(1,1),MSGCLASS=H,NOTIFY=&SYSUID
//*
//*--------------------------------------------------------------------
//* Create CAB File for the Db2 Log Reader Capture Agent
//*--------------------------------------------------------------------
//* Note: 1) Parameter Keywords must be entered in lower case
//* 2) Parameters Values are Case Sensitive.
//* 3) Engine Name should be in Upper Case for z/OS JCL
//*
//* Steps: 1) (optional) delete the existing Capture CAB File
//* 2) Create a new Capture CAB File
//* 3) Add Tables to the new capture CAB File
//* 4) Display the contents of the new CAB File
//*--------------------------------------------------------------------
//*
//JOBLIB DD DISP=SHR,DSN=SQDATA.V400.LOADLIB
//*
//*-------------------------------------------
//* Optional - Delete existing CAB File
//*-------------------------------------------
//*DELETE EXEC PGM=IEFBR14
//*SYSPRINT DD SYSOUT=*
//*CONFFILE DD PATHDISP=(DELETE,DELETE),
//* PATH='/home/sqdata/db2cdc/db2cdc.cab'
//*
//*-----------------------------------------------
//* Create Db2 Capture Configuration CAB File
//*-----------------------------------------------
//CRCONF EXEC PGM=SQDCONF
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SQDPARMS DD *
create /home/sqdata/db2cdc/db2cdc.cab
--type=db2
--ssid=DBBG
--store=/home/sqdata/db2cdc/db2cdc_store.cab
//*
//*--------------------------------------------------------------------
//* Add Tables to the Capture CAB File
//*--------------------------------------------------------------------
//* Modify to specify the Table(s) to be Captured initially.
//* Tables can be added later using a modified version of this Job
//* or using the SQDATA ISPF panel interface
//*--------------------------------------------------------------------
//*
//*-----------------------------------------------------
//* Publish Table SQDATA.EMP to Subscription DB2TODB2
//*-----------------------------------------------------
//ADDEMP EXEC PGM=SQDCONF
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SQDPARMS DD *
add /home/sqdata/db2cdc/db2cdc.cab
--table=SQDATA.EMP
--datastore=cdc:////DB2TODB2
--active
//*
//*-----------------------------------------------------
//* Publish Table SQDATA.DEPT to Subscription DB2TODB2
//*-----------------------------------------------------
//ADDDEPT EXEC PGM=SQDCONF
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SQDPARMS DD *
add /home/sqdata/db2cdc/db2cdc.cab
--table=SQDATA.DEPT
--datastore=cdc:////DB2TODB2
--active
//*
//*-------------------------------------------
//* Display configuration file
//*-------------------------------------------
//DISPLAY EXEC PGM=SQDCONF
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SQDPARMS DD *
display /home/sqdata/db2cdc/db2cdc.cab
//*
//Notes:
- The sqdconf create command defines the location of the Capture agent's configuration file. Once created, this command should never be run again unless you want to destroy and recreate the Capture agent.
- Destroying the Capture agent cab file means that the current position in the log and the relative location of each engine's position in the Log will be lost. When the Capture agent is brought back up it will start from the beginning of the oldest active log and will resend everything. After initial configuration, changes in the form of add and modify commands should be used instead of the create command. Note: You can not delete a cab file if the Capture is mounted. And a create on an existing configuration file will fail.
- There must be a separate ADD step in the Job for every source table to be captured.
- The Job will fail if the same table is added more than one time for the same Target Datastore/Engine. See section below "Adding/Removing Output Datastores".
- The <subscriber_name> is case sensitive in that all references should be either upper or lower case. Because references to the "Engine" in z/OS JCL must be upper case, references to the Engine in these examples are all in upper case for consistency.
- The display step when run against an active configuration (.cab) file will include other information including:
- The current status of the table (i.e. active, inactive)
- The starting and current point in the log where data has been captured
- The number of inserts, updates and deletes for the session (i.e. the duration of the capture agent run)
- The number of inserts, updates and deletes since the creation of the configuration file