Setup Log Reader Capture Agent

The Db2 Log Reader Capture performs three functions: Capturing changed data by mining the Db2 Log, managing the captured data, managing the captured data, publishing committed data directly to Engines using TCP/IP. The Publishing function manages the captured data until it has been transmitted and consumed by Engines, ensuring that captured data is not lost until the Engines, which may operate on other platforms, signal that data has been applied to their target datastores.

Configure Db2 Tables for Capture

In order for the Db2 Capture Agent to be able to extract the changed data for Db2 tables from the recovery log, the source Db2 table must be altered to allow for change data capture.

Syntax
 ALTER TABLE <schema.tablename> DATA CAPTURE CHANGES;

Keyword and Parameter Descriptions

<schema.tablename> is the fully qualified name of the source table for which changes are to be captured.
Note: Enabling change data capture will increase the amount of data written to the Db2 recovery log for each update to the source data table. Depending on the size of the tables and the volume of updates made to the table, the size of the active Db2 logs may have to be adjusted to accommodate the increase data.

Create Db2 Capture CAB File

The Db2 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.

Syntax 
 sqdconf create <cab_file_name>
                    --type=DB2
                    --ssid=<db2_system_id>
                    [--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>"]
                    [--exclude-program=<plan_name>]
                    --store=<store_cab_file_name>

Keyword and Parameter Descriptions

--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 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 time

[--encryption | --no-encryption] - Enables or disables NaCL encryption of the published CDC record payload. See Encryption of Published Data for more details. 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

Next, the configuration file must be updated by adding an entry for each table to be captured using the add command. Only one table and associated 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. 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.

Syntax 
 sqdconf add <cab_file_name>
                                                            --schema=<name> --table=<name> | --key=<name>
                                                            --datastore=<url>
                                                            [--active | --inactive] 
                                                            [--pending]

Keyword and Parameter Descriptions

<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:
  • <host_name> - Optional, typically omitted with only a / placeholder. If specified must match the [<localhost_name>| <localhost_IP>] of the server side of the socket connection.
  • <agent_alias> - Optional, typically omitted with only a / placeholder. If specified must match the <capture_agent_alias> or <publisher_agent_alias> assigned to the Capture/Publisher agent in the Controller Daemon sqdagents.cfg configuration file.
  • <subscriber_name> The name presented by a requesting target agent. Also referred to as the Engine name. Connection requests by Engines or the sqdutil utility must specify a valid <subscriber_name> in their cdc://<host_name>/<agent_alias>/<subscriber_name> connection URL.

[--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

Create a Capture configuration for the Db2 IVP tables and display the current content of the configuration file:
//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 
                //*
                //
  • 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

Encryption of Published Data

Precisely highly recommends the use of VPN or SSH Tunnel connections between systems both to simplify their administration and because the CPU intensive encryption task can be performed by dedicated network hardware. In the event that encryption is required and a VPN or SSH Tunnel cannot be used, SQData provides other options.

Implement NaCL Encryption

SQData provides for encryption by the Publisher using the same NaCl Public / Private Key used for authentication and authorization. While Captures and Publishers are typically initiated by the same USER_ID as the Capture Controller Daemon, those jobs explicitly identify the public / private key pair files in JCL DD statements. Precisely recommends that a second NACL Key pair is generated for the Capture / Publisher. A second authorized Key List will also be required by the Capture / Publisher containing the public keys for only those Engines subscribing to that Capture / Publisher and whose payload will be encrypted. Once the Controller Daemon passes the connection request to the Capture / Publisher a second handshake will be performed with the Engine and the CDC payload will be encrypted before being published and decrypted by the receiving Engine.

Syntax
 sqdconf create <cab_file_name> 
                    [--encryption | --no-encryption] 
                    [--auth-keys-list="<name>"]
Keyword and Parameters Descriptions
  • <cab_file_name> - This is where the Capture Agent configuration file, including its path is first created. There is only one CAB file per Capture Agent. In our example /home/sqdata/db2cdc/db2cdc.cab
  • [--encryption | --no-encryption] - Enables or disables NaCL encryption of the published CDC record payload. See Encryption of Published Data for more details. 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.
Example 1
  1. Turn off encryption 
    //*---------------------------------------------
                            //* Turn on Encryption for DB2 Capture
                            //*---------------------------------------------
                            //MODCONF EXEC PGM=SQDCONF
                            //SYSPRINT DD SYSOUT=*
                            //SYSOUT DD SYSOUT=*
                            //SQDPARMS DD *
                            modify /home/sqdata/db2cdc/db2cdc.cab 
                            --no-encryption 
                            //*
  2. Stop and restart the DB2 Capture Agent.
    Note: Precisely recommends zIIP processors be used to enhance CPU cycle efficiency and reduce CPU cost associated with NaCL software encryption. Enabling zIIP processing requires one additional option when starting the Capture / Publisher.
  3. Restart the agent and include the following --ziip option, as follows:

    --apply --start --ziip

    /home/sqdata/db2cdc/db2cdc.cab

Implement TLS Support

Transport Layer Security (TLS) is supported between all components on z/OS and between SQData clients on Linux and Change Data Capture components on z/OS, only.

z/OS TLS Capture and Apply Engines

SQData already operates transparently on z/OS under IBM's Application Transparent Transport Layer Security (AT-TLS) . Under AT-TLS no changes were required to the base code and only port numbers in the configuration need to be changed, as described below. For more information regarding AT-TLS see your z/OS Systems Programmer.

Once IBM's AT-TLS has been implemented on z/OS the following steps are all that is required by Daemon, Capture and Publisher components and on z/OS only, the SQDATA Apply Engine and SQDutil, to be in compliance with TLS:

  1. Request the new secure port to be used by the Daemon
  2. Request Certificates for MASTER, Daemon and APPLY Engine Tasks
  3. Stop all SQDATA tasks
  4. Update APPLY Engine source scripts with the new Daemon port. Note, port's are typically implemented using a Parser parameter so script changes may not be required.
  5. Update SQDUTIL JCL and/or SQDPARM lib member's, if any with the new Daemon port.
  6. Run Parse Jobs to update the Parsed Apply Engines in the applicable library referenced by Apply Engine Jobs.
  7. Update the Daemon tasks with new port
  8. If using the z/OS Master Controller, update the SQDPARM Lib members for the MASTER tasks with new Daemon port
  9. Start all the SQDATA tasks
    Note: There are no changes to connection URL's for clients on z/OS.

Linux TLS Apply and Replicate Engines

Linux clients connecting to z/OS Daemons running under IBM's (AT-TLS) and servicing z/OS Change Data Captures now support the TLS handshake. TLS connections to Change Data Capture components running on AIX and Linux are not supported at this time.

The only external prerequisite to enabling TLS on Linux is the GnuTLS secure communications library which implements TLS, DTLS and SSL protocols and technologies including the C language API used by SQData on Linux. On RPM-based Linux distributions, YUM (Yellowdog Updater Modified) can be used to install GnuTLS. For more information regarding YUM or other Package Managers see your Linux Systems Administrator.

Linux clients making TLS connections to z/OS, will by default perform the "typical TLS handshake" where the client uses the server's certificate for authentication and then proceeds with the rest of the handshake process. Specific changes to connection parameters are described below.

The following steps are all that are required on the client side to implement TLS on Linux for the "typical" client side handshake performed by an Engine:

  1. Request the new Port number that was assigned to the z/OS Daemon.
  2. Stop all running Connect CDC SQData Linux Engines, the local Daemon need not be stopped.
  3. Update Engine source DATASTORE URL to use the "cdcs:// URL syntax type to specify that a secure TLS connection is requested (changed from "cdc://" to "cdcs://").
  4. Update Engine source DATASTORE URL to use the TLS z/OS Daemon port. Note, the port number is typically implemented using a Parser parameter so script changes may not be required.
  5. Parse the Apply Jobs to create a new <engine>.prc file in the applicable directory.
  6. Start the Connect CDC SQData Linux clients.

Linux default Package Manager Notes

  1. If the Linux default package manager was used to install the GnuTLS library, we would expect it to be placed in either /lib64 or /usr/lib64. Two softlinks should have been created and libgnutls.so included in the "default library path" for example:
    lrwxrwxrwx 1 root root 20 Jul 8 2020 libgnutls.so -> libgnutls.so.28
                                lrwxrwxrwx 1 root root 20 Sep 11 2019 libgnutls.so.28 -> libgnutls.so.28.43.3

    If it is not in the default path we will be unable to locate the library. A special environmental variable can be used but Precisely only recommends doing so in a test environment:

    SQDATA_GNUTLS_LIBRARY=<path_to_softlink/>libgnutls.so

  2. If the SQDmon utility is used to connect to a remote z/OS Daemon running under IBM's (AT-TLS) , for example to request an "inventory" or "display" the status of a publisher a new --tls parameter must be specified:
    Syntax:
     sqdmon inventory //<host_name> [-s port_num | --service=port_num] [-- identity=<path_to_nacl_id/nacl_id>] [--tls]
  3. If the SQDutil is used to connect to a remote Publisher running under IBM's (AT-TLS) , to copy/move CDC records to a file, the "cdcs://" URL syntax type must be specified:
    Syntax
    sqdutil copy | move cdcs://<host_name> :<port_num>/<agent_name> <target_url> | DD:<dd_name>
  4. Although uncommon, if yours is a Mutual Auth aka Mutual Authentication implementation, which also includes authentication of the client by the server, then two environmental variables must be used to identify the client certificate and key. The server will then use the client side certificate to authenticate the client before proceeding with the rest of the handshake.
    SQDATA_TLS_CERT=</directory/file_name> 
                                SQDATA_TLS_KEY=</directory/file_name>
    The Linux client will by default use the system installed Certificate Authority (CA). If a local CA file is used, it must be specified using a third Environmental variable:
    SQDATA_TLS_CA=</directory/file_name>

Data Sharing Environments

For capture in a data sharing environment, select one data sharing group name to capture from. Capture against multiple members in a data sharing group will result in capturing the same data multiple times.

Prepare Db2 Capture Runtime JCL

Once the DB2 Capture configuration (.cab) file has been created, JCL similar to sample member SQDDB2C included in the distribution is used to Mount and optionally Start the DB2 Capture Agent process.

Example
//SQDDB2C JOB 1,MSGLEVEL=(1,1),MSGCLASS=H,NOTIFY=&SYSUID
                //*
                //*------------------------------------------------------
                //* Execute (Mount) DB2 CDCStore Capture Agent - SQDDB2C
                //*------------------------------------------------------
                //* Required parameters (lower case):
                //* config_file - Specifies the fully qualified name of the
                //* predefined DB2 capture agent configuration
                //* file (see sample JCL SQDCONDC)
                //*
                //* Optional parameters (lower case):
                //* --apply - Specifies that ALL pending changes to the config (.cab)
                //* file should be Applied before the Capture Agent is
                //* Started
                //* ** NOTE - This is normally NOT used in this job
                //* ** REMOVE if the status of pending changes is NOT
                //* ** known. Instead use SQDCONF to apply changes
                //*
                //* --start - Instructs the Capture Agent to Start processing
                //* ** NOTE - This is often used in this job but can
                //* ** be performed by a separate SQDCONF command
                //*
                //* --ziip - Instructs the Capture to utilize zIIP engines for
                //* encryption (if specified in the Capture Agent
                //* CAB file
                //*
                //* Note: 1) The Relational CDCStore Capture Agents include
                //* a second Publisher thread that manages Engine
                //* subscriptions
                //*-------------------------------------------------------
                //*
                //JOBLIB DD DISP=SHR,DSN=SQDATA.V400.LOADLIB
                // DD DISP=SHR,DSN=CSQ901.SCSQAUTH
                // DD DISP=SHR,DSN=CSQ901.SCSQANLE
                // DD DISP=SHR,DSN=DSNC10.SDSNLOAD
                //*
                //SQDDB2C EXEC PGM=SQDDB2C,REGION=0M
                //*SQDDB2C EXEC PGM=XQDDB2C,REGION=0M
                //SQDPUBL DD DSN=SQDATA.NACL.PUBLIC,DISP=SHR
                //SQDPKEY DD DSN=SQDATA.NACL.PRIVATE,DISP=SHR
                //SYSPRINT DD SYSOUT=*
                //SYSOUT DD SYSOUT=*
                //CEEDUMP DD SYSOUT=*
                //SQDLOG DD SYSOUT=*
                //*SQDLOG8 DD DUMMY
                //*SQDPARMS DD DISP=SHR,DSN=SQDATA.V400.PARMLIB(DB2CDC)
                //SQDPARMS DD *
                --apply --start 
                /home/sqdata/db2cdc/db2cdc.cab 
                //*
                //
                --apply --start --ziip --no-ddl-tracking

Prepare Db2 Capture Runtime JCL Notes

  • Precisely recommends zIIP processors be used to enhance CPU cycle efficiency and reduce CPU cost associated with NaCL software encryption.
  • While the SQDCONF utility is used to create the Capture Agent configuration and perform most of the other management and control tasks associated with the capture agent, on z/OS it cannot perform the function of the MOUNT command. On platforms other than z/OS, the MOUNT command brings an Agent on-line. On z/OS that function must be performed with an agent specific JOB or Started Task. Once the Capture Agent has been "mounted" on z/OS, the sqdconf utility can and should be used to perform all other functions as documented.
  • The first time this Job is run you may choose to include a special form of the sqdconf apply and start commands. After the initial creation, --apply should not be used in this JCL, unless all changes made since the agent was last Stopped are intended to take effect immediately upon the Start. The purpose of apply is to make it possible to add/modify the configuration while preparing for an implementation of changes without affecting the current configuration. Note, apply and start can and frequently will be separated into different SQDCONF jobs.
  • The Controller Daemon uses a Public / Private key mechanism to ensure component communications are valid and secure. While it is critical to use unique key pairs when communicating between platforms, it is common to use the same key pair for components running together on the same platform. Consequently, the key pair used by a Log Reader Capture agent may be the same pair used by it's Controller Daemon.