Set up the Data Integration environment, including public/private keys, storage agent, and log reader capture agent for secure data replication.
-
Generate z/OS public or private keys and authorized key file
The Controller Daemon uses a Public or Private key mechanism to ensure component communications are valid and secure. A key pair must be created for the SQDaemon Job System User-ID and the User-ID's of all the Agent Jobs that interact with the Controller Daemon. On z/OS, by default, the private key is stored in SQDATA.NACL.PRIVATE and the public key in SQDATA.NACL.PUBLIC. These two files will be used by the Daemon in association with a sequential file containing a concatenated list of the Public Keys of all the Agents allowed to interact with the Controller Daemon. The Authorized Keys file must contain at a minimum, the public key of the SQDaemon job System User-ID and is usually created with a first node matching the username running the SQDaemon job, in our example SQDATA.NACL.AUTH.KEYS.
The file must also include the Public key's of Engines running on z/OS or other platforms. The Authorized Keys file is usually maintained by an administrator using ISPF.
JCL similar to sample member NACLKEYS included in the distribution runs the SQDutil utility program using the keygen command and should be used to generate the necessary keys and create the Authorized Key List file. The JCL should be edited to conform to the operating environment and the job must be run under the user-id that will be used when the Controller Daemon job is run.
//NACLKEYS JOB 1,MSGLEVEL=(1,1),MSGCLASS=H,NOTIFY=&SYSUID //* //*---------------------------------------------------------------- //* Generate NACL Public/Private Keys and optionally AKL file //*---------------------------------------------------------------- //* Required DDNAME: //* SQDPUBL DD - File that will contain the generated Public Key //* SQDPKEY DD - File that will contain the generated private Key //* ** This file and its contents are not to be shared //* //* Required parameters: //* PARM - keygen *** In lower case *** //* USER - The system USERID or high level qualifier of the //* SQDATA libraries IF all Jobs will share Private Key. //* //* Notes: //* 1) This Job generates a new Public/Private Key pair, saves //* them to their respective files and adds the Public Key //* to an existing Authorized Key List, allocating a new //* file for that purpose if necessary. //* //* 2) An optional first step deletes the current set of files //* //* 3) Change the SET parms below for: //* HLQ - high level qualifier of the CDC Libraries //* VER - the 2nd level qualifier of the CDC OBJLIB & LOADLIB //* USER - the High Level Qualifier of the NACL Datasets //*-------------------------------------------------------------------- //* // SET HLQ=SQDATA // SET VER=V400 // SET USER=&SYSUID //* //JOBLIB DD DISP=SHR,DSN=SQDATA..&VER..LOADLIB //* //*-------------------------------------------------------------------- //* Optional: Delete Old Instance of the NACL Files //*------------------------------------------------------------------- //*DELOLD EXEC PGM=IEFBR14 //*SYSPRINT DD SYSOUT=* //*OLDPUB DD DISP=(OLD,DELETE,DELETE),DSN=&USER..NACL.PUBLIC //*OLDPVT DD DISP=(OLD,DELETE,DELETE),DSN=&USER..NACL.PRIVATE //*OLDAUTH DD DISP=(OLD,DELETE,DELETE),DSN=SQDATA.NACL.AUTH.KEYS //*-------------------------------------------------------------------- //* Allocate Public/Private Key Files and Generate Public/Private Keys //*-------------------------------------------------------------------- //SQDUTIL EXEC PGM=SQDUTIL //SQDPUBL DD DSN=&USER..NACL.PUBLIC, // DCB=(RECFM=FB,LRECL=80,BLKSIZE=21200), // DISP=(,CATLG,DELETE),UNIT=SYSDA, // SPACE=(TRK,(1,1)) //SQDPKEY DD DSN=&USER..NACL.PRIVATE, // DCB=(RECFM=FB,LRECL=80,BLKSIZE=21200), // DISP=(,CATLG,DELETE),UNIT=SYSDA, // SPACE=(TRK,(1,1)) //SQDPARMS DD * keygen //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SQDLOG DD SYSOUT=* //*SQDLOG8 DD DUMMY //*------------------------------------------------------------------- //* Allocate the Authorized Key List File --> Used only by the Daemon //*------------------------------------------------------------------- //COPYPUB EXEC PGM=IEBGENER //SYSPRINT DD SYSOUT=* //SYSIN DD DUMMY //SYSUT1 DD DISP=SHR,DSN=&USER..NACL.PUBLIC //SYSUT2 DD DSN=SQDATA.NACL.AUTH.KEYS, // DCB=(RECFM=FB,LRECL=80,BLKSIZE=21200), // DISP=(MOD,CATLG),UNIT=SYSDA,SPACE=(TRK,(5,5))- Since the Daemon and Capture Agents and zOS Apply Engines may be running in the same LPAR or system, they frequently run under the same System User-ID, in that case they would share the same public or private key pair.
- Changes are not known to the Daemon until the configuration files are reloaded, using the SQDmon Utility, or the sqdaemon process is stopped and started.
-
Setup data integration storage agent
The Db2 Log Reader Capture utilizes the Data Integration storage agent to manage the transient storage of both committed and in-flight or uncommitted units-of-work using auxiliary storage. The Storage Agent must be set up before configuring the Capture Agent.
-
Size transient storage pool
The Data Integration storage agent uses a memory mapped storage pool to speed captured change data on its way to Engines. It is designed to do so without "landing" the data, after it has been mined from a database log. Configuration of the Storage Agent requires the specification of both the memory used to cache changed data as well as the disk storage used if not enough memory can be allocated to hold large units-of-work and other concurrent workload.
Memory is allocated in 8MB blocks with a minimum of 4 blocks allocated or 32MB of system memory. The disk storage pool is similarly allocated in files made up of 8MB blocks. While ideally the memory allocated would be large enough to maintain the log generated by the longest running transaction and all other transactions running concurrently, that will most certainly be impractical if not impossible. Ultimately, there are two situations to be avoided which govern the size of the disk storage pool:
Large Units of Work - While never advisable, some batch processes may update very large amounts of data before committing the updates. Often such large units of work may be unintentional or even accidental but must still be accommodated. The storage pool must be able to accommodate the entire unit of work or a DEADLOCK condition will be created.
Archived Logs - Depending on workload, database logs will eventually be archived at which point the data remains accessible to the Capture Agent but at a higher cost in terms of CPU and I/O. Under typical circumstances, captured data should be consumed by Engines in a timely fashion making the CDCStore FULL condition one to be aware of but not necessarily concerned about. If however the cause is a stopped Engine, the duration of the outage could result in un-captured data being archived.
The environment and workload may make it impossible to allocate enough memory to cache a worse case or even the average workload, therefore we recommend two methods for sizing the storage pool based on the availability of logging information.
If detailed statistics are available:
- Gather information to estimate the worse case log space utilization (longest running Db2 transaction and all other Db2 transactions running concurrently) - We will refer to this number as MAX.
- Gather information to estimate the log space consumed by an "Average size" Db2 transaction and multiply by the number of average concurrent transactions - We will refer to this number as AVG.
- Plan to allocate disk files in your storage pool as large as the Average (AVG) concurrent transaction Log space consumed. Divide the value of AVG by 8 (number of MB in each block) - This will give you the Number-of-Blocks in a single file.
- Divide the value of MAX by 8 (number of MB in each block) and again by the Number of Blocks to calculate the number of files to allocate which we will refer to as Number-of-Files. Note, dividing the value of MAX by AVG and rounding to the nearest whole number should result in the same value for N.
Example
Number-of-Blocks = AVG / 8 (MB per block) Number-of-Files = MAX / 8 / Number-of-Blocks (which is the same as Number-of-Files = MAX / AVG)If detailed statistics are NOT available:
- Precisely recommends using a larger number of small disk files in the storage pool and suggests beginning with 256MB files. Dividing 256MB by the 8MB block size gives the Number-of-Blocks in a single file, 32.
- Precisely recommends allocating a total storage pool of 2GB (2048MB) as the starting point. Divide that number by 256MB to calculate the Number-of-Files required to hold 2GB of active LOG which would be 8.
Example
Number-of-Blocks = 256MB / 8MB = 32 Number-of-Files = 2048MB / 256 = 8Use these values to configure the CDCStore Storage Agent in the next section.
- Remember that it is possible to adjust these values once experience has been gained and performance observed. See the section "Display Storage Agent Statistics" in the Operations section below.
- Think of the value for Number-of-Files as a file Extent, in that another file will be allocated only if the MEMORY cache is full and all of the Blocks (Number-of-Blocks) in the first file have been used and none are released before additional 8MB Blocks are required to accommodate an existing incomplete unit of work or other concurrent units of work.
- While the number of Number-of-Blocks and Number-of-Files can be dynamically adjusted they will apply only to new files allocated. It will be necessary to stop and restart the Storage Agent for changes to MEMORY.
- Multiple Directories can also be allocated but this is only practical if the File system itself fills and a second directory becomes necessary.
-
Apply frequency
The size of the transient storage area is also affected by the frequency changed data is applied to a target. For example, changes from a Db2 source to an Oracle target may only need to be applied once a day.
In this example the transient storage could be sized large enough to store all of the changed data accumulated during the one-day period. Often however, the estimated size will prove to be inadequate. When that happens the capture will eventually stop mining the Db2 Log and wait for an Engine to connect and Publishing to resume. When the Capture does finally request the next log record from Db2, the required Db2 Archive Logs may have become inaccessible. This would occur if the wait period was long enough or the volume of data changing large enough that the Archive Log retention period was too short.
Best practices for Db2 Archive Log retention will normally ensure that the Archive Logs are accessible. In some environments however this can become an issue. Precisely recommends analysis of the total Db2 workload in all cases because even though only a fraction of all the existing tables may be configured for capture, the Db2 Log Reader capture potentially requires access to every log Archived since the last Apply cycle.
Precisely recommends switching to a streaming Apply model for your target or raising the Apply frequency as high as practical when capturing rapidly changing tables in high volume Db2 environments, especially if space is an issue.
-
Create zFS transient data filesystem
Once you have estimated the potential size of the transient data storage pool you must create a dedicated filesystem. That filesystem will then be assigned a mount point that will be referenced in the CDCStore configuration (.cab) file. As discussed previously this filesystem need not be large but it is critical that it be dedicated for this purpose because the Storage agent will create and remove files as needed based on the definition of the storage pool. It will expect the space it has been told it can use to be available when needed and will stop the capture if it is not. Double the amount of space you have estimated will be required. That will allow you to adjust the number of blocks and files in your configuration without having to add an additional mount point and filesystem.
JCL similar to the following sample member ALLOCZFS included in the distribution should be used to create the Transient Data Filesystem for the Db2 z/OS Capture Agent and mount it at the directory created previously, /home/sqdata/db2cdc/data. The JCL should be edited to conform to the operating environment.
//ALLOCZFS JOB 1,MSGLEVEL=(1,1),MSGCLASS=H,NOTIFY=&SYSUID //* //*----------------------------------------------------------------- //* Allocate Transient Storage Filesystem for DB2 z/OS Capture Agent //*----------------------------------------------------------------- //* Required parameters (set below): //* ZFS - the name of the VSAM cluster aggregate //* MB - the number of megabytes to allocate to the cluster //* DIR - the directory name of the ZFS mountpoint //* which was previously created using member ALLOCZDR //* SMS - the SMS class to be assigned to the cluster //* VOL - the DASD volume(s) used for cluster allocation //* //* Notes: //* 1) You must be a UID(0) User to Run this Job //* //* 2) This job contains six (6) steps as follows: //* - Unmount the existing file system - optional //* - Define the ZFS filesystem VSAM cluster aggregate //* - Format the ZFS filesystem aggregate //* - Format the ZFS filesystem aggregate //* - Create the mountpoint directory //* - Mount the ZFS filesystem //*----------------------------------------------------------------- //* // EXPORT SYMLIST=(ZFS,MB,DIR,SMS,VOL) // SET ZFS=SQDATA.TESTZFS // SET MB=2049 // SET DIR='/home/sqdata/db2cdc/data' // SET SMS=DBCLASS // SET VOL=WRK101 //* //*------------------------------------------------------------------ //* Optional - Unmount the Existing File System //*------------------------------------------------------------------ //*UNMOUNT EXEC PGM=IKJEFT01,DYNAMNBR=75,REGION=8M //*SYSPRINT DD SYSOUT=* //*SYSTSPRT DD SYSOUT=* //*SYSTERM DD DUMMY //*SYSUADS DD DSN=SYS1.UADS,DISP=SHR //*SYSLBC DD DSN=SYS1.BRODCAST,DISP=SHR //*SYSTSIN DD *,SYMBOLS=JCLONLY //* UNMOUNT FILESYSTEM('&ZFS') /* //*------------------------------------------------------------------- //* Define the ZFS filesystem VSAM cluster aggregate //*------------------------------------------------------------------- //DEFINE EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SYSIN DD *,SYMBOLS=JCLONLY DELETE &ZFS CLUSTER SET MAXCC=0 DEFINE CLUSTER (NAME(&ZFS) - VOLUME(&VOL) - STORCLAS(&SMS) - LINEAR - MB(&MB 0) - SHAREOPTIONS(3)) /* //*------------------------------------------------------------------ //* Format the ZFS Filesystem Aggregate //*------------------------------------------------------------------ //FORMAT EXEC PGM=IOEAGFMT,REGION=0M,PARM=('-aggregate &ZFS -compat') //SYSPRINT DD SYSOUT=* //STDOUT DD SYSOUT=* //STDERR DD SYSOUT=* /* //*------------------------------------------------------------------- //* Create the Mountpoint Directory //*------------------------------------------------------------------- //CREATDIR EXEC PGM=IKJEFT01,REGION=64M,DYNAMNBR=99,COND=(0,LT) //SYSTSPRT DD SYSOUT=* //SYSTSIN DD *,SYMBOLS=JCLONLY PROFILE MSGID WTPMSG MKDIR '&DIR' MODE(7,7,5) /* //*-------------------------------------------------------------------- //* Mount the ZFS Filesystem //*-------------------------------------------------------------------- //MOUNT EXEC PGM=IKJEFT01,DYNAMNBR=75,REGION=8M,COND=(0,LT) //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSTERM DD DUMMY //SYSUADS DD DSN=SYS1.UADS,DISP=SHR //SYSLBC DD DSN=SYS1.BRODCAST,DISP=SHR //SYSTSIN DD *,SYMBOLS=JCLONLY MOUNT FILESYSTEM('&ZFS') - MOUNTPOINT('&DIR') - TYPE(ZFS) - MODE(RDWR) /* -
Create zFS transient data filesystem
Once you have estimated the potential size of the transient data storage pool you must create a dedicated filesystem. That filesystem will then be assigned a mount point that will be referenced in the CDCStore configuration (.cab) file. As discussed previously this filesystem need not be large but it is critical that it be dedicated for this purpose because the Storage agent will create and remove files as needed based on the definition of the storage pool. It will expect the space it has been told it can use to be available when needed and will stop the capture if it is not. Double the amount of space you have estimated will be required. That will allow you to adjust the number of blocks and files in your configuration without having to add an additional mount point and filesystem.
JCL similar to the following sample member ALLOCZFS included in the distribution should be used to create the Transient Data Filesystem for the Db2 z/OS Capture Agent and mount it at the directory created previously, /home/sqdata/db2cdc/data. The JCL should be edited to conform to the operating environment.
//ALLOCZFS JOB 1,MSGLEVEL=(1,1),MSGCLASS=H,NOTIFY=&SYSUID //* //*----------------------------------------------------------------- //* Allocate Transient Storage Filesystem for DB2 z/OS Capture Agent //*----------------------------------------------------------------- //* Required parameters (set below): //* ZFS - the name of the VSAM cluster aggregate //* MB - the number of megabytes to allocate to the cluster //* DIR - the directory name of the ZFS mountpoint //* which was previously created using member ALLOCZDR //* SMS - the SMS class to be assigned to the cluster //* VOL - the DASD volume(s) used for cluster allocation //* //* Notes: //* 1) You must be a UID(0) User to Run this Job //* //* 2) This job contains six (6) steps as follows: //* - Unmount the existing file system - optional //* - Define the ZFS filesystem VSAM cluster aggregate //* - Format the ZFS filesystem aggregate //* - Format the ZFS filesystem aggregate //* - Create the mountpoint directory //* - Mount the ZFS filesystem //*----------------------------------------------------------------- //* // EXPORT SYMLIST=(ZFS,MB,DIR,SMS,VOL) // SET ZFS=SQDATA.TESTZFS // SET MB=2049 // SET DIR='/home/sqdata/db2cdc/data' // SET SMS=DBCLASS // SET VOL=WRK101 //* //*------------------------------------------------------------------ //* Optional - Unmount the Existing File System //*------------------------------------------------------------------ //*UNMOUNT EXEC PGM=IKJEFT01,DYNAMNBR=75,REGION=8M //*SYSPRINT DD SYSOUT=* //*SYSTSPRT DD SYSOUT=* //*SYSTERM DD DUMMY //*SYSUADS DD DSN=SYS1.UADS,DISP=SHR //*SYSLBC DD DSN=SYS1.BRODCAST,DISP=SHR //*SYSTSIN DD *,SYMBOLS=JCLONLY //* UNMOUNT FILESYSTEM('&ZFS') /* //*------------------------------------------------------------------- //* Define the ZFS filesystem VSAM cluster aggregate //*------------------------------------------------------------------- //DEFINE EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SYSIN DD *,SYMBOLS=JCLONLY DELETE &ZFS CLUSTER SET MAXCC=0 DEFINE CLUSTER (NAME(&ZFS) - VOLUME(&VOL) - STORCLAS(&SMS) - LINEAR - MB(&MB 0) - SHAREOPTIONS(3)) /* //*------------------------------------------------------------------ //* Format the ZFS Filesystem Aggregate //*------------------------------------------------------------------ //FORMAT EXEC PGM=IOEAGFMT,REGION=0M,PARM=('-aggregate &ZFS -compat') //SYSPRINT DD SYSOUT=* //STDOUT DD SYSOUT=* //STDERR DD SYSOUT=* /* //*------------------------------------------------------------------- //* Create the Mountpoint Directory //*------------------------------------------------------------------- //CREATDIR EXEC PGM=IKJEFT01,REGION=64M,DYNAMNBR=99,COND=(0,LT) //SYSTSPRT DD SYSOUT=* //SYSTSIN DD *,SYMBOLS=JCLONLY PROFILE MSGID WTPMSG MKDIR '&DIR' MODE(7,7,5) /* //*-------------------------------------------------------------------- //* Mount the ZFS Filesystem //*-------------------------------------------------------------------- //MOUNT EXEC PGM=IKJEFT01,DYNAMNBR=75,REGION=8M,COND=(0,LT) //SYSPRINT DD SYSOUT=* //SYSTSPRT DD SYSOUT=* //SYSTERM DD DUMMY //SYSUADS DD DSN=SYS1.UADS,DISP=SHR //SYSLBC DD DSN=SYS1.BRODCAST,DISP=SHR //SYSTSIN DD *,SYMBOLS=JCLONLY MOUNT FILESYSTEM('&ZFS') - MOUNTPOINT('&DIR') - TYPE(ZFS) - MODE(RDWR) /* -
Create z/OS data integration CAB file
The Data Integration storage agent configuration (.cab) file is a binary file created and maintained by the SQDconf utility.
While this section focuses primarily on the initial configuration of the Storage agent, sequences of SQDconf commands to create and configure the storage agent should be saved in a shell script or a zOS PARMLIB member both for migration to other operating environments and for recovery.
The SQDconf create command will be used to prepare the initial CDCStore configuration (.cab) file for the CDCStore Storage agent used by the Db2 zOS, UDB (DB2 or LUW) and Oracle Capture agents:
Syntax
sqdconf create <cab_file_name> --type=store --alias=cdcstore [--number-of-blocks=<blocks_per_file>] [--number-of-logfiles=<number_of_files>] --data-path=<directory_name>Keyword and Parameter Descriptions
Keywords and Parameters Description <cab_file_name>Path and name of the Storage Agent Configuration (.cab) file. The directory must exist and the user-id associated with the agent must have the right to create and delete files in that directory. There is a one to one relationship between the CDCStore Storage Agent and Capture Agent. Precisely recommends including the Capture Agent alias as the third node in the directory structure and first node of the file name, for example, /home/sqdata/db2cdc/db2cdc_store.cab. In a Windows environment .cfg may be substituted for .cab since .cab files have special meaning in Windows. --type=storeAgent type must be "store" for the Storage Agent. --alias=cdcstoreAgent type must be "store" for the Storage Agent. [--number-of-blocks=<blocks_per_file> | -b <blocks_per_file>]The number of 8MB blocks allocated for each file defined for transient CDC storage. The default is 32. [--number-of-logfiles=<number_of_files> | -n <number_of_files>]The number of files that can be allocated in a data-path. Files are allocated as needed one full file at a time, during storage agent operation. CDCStore recycles blocks when possible before new storage is allocated. The default is 8. --data-path=<directory_name>The path and directory name, where the storage agent will create transient storage files. The directory must exist and the user-id associated with the agent must have the right to create and delete files in that directory. In our example, /home/sqdata/db2cdc/data. Example
Create the SQData CDCStore Storage Agent configuration for a Db2 zOS capture using JCL similar to sample member SQDCONDS included in the distribution with the appropriate storage pool parameters. While those parameters are included in-line below we recommend that they be maintained separately in a file referenced by the SQDPARM DD:
//SQDCONDS JOB 1,MSGLEVEL=(1,1),MSGCLASS=H,NOTIFY=&SYSUID //* //*-------------------------------------------------------- //* Create CAB File for DB2 CDCStore Storage Agent //*-------------------------------------------------------- //* Note: 1) Parameter Keywords must be entered in lower case //* //* 2) Parameters Values are Case Sensitive. //* //* 3) The transient storage directory(s) should be sized //* to hold the largest anticipated unit-of-work in //* addition to any concurrent inflight transactions //* //* Steps: 1) (optional) delete the existing Storage CAB File //* 2) Create a new Storage CAB File //* 3) 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=* //*CDCSTORE DD PATHDISP=(DELETE,DELETE), //* PATH='/home/sqdata/db2cdc/db2cdc_store.cab' //* //*------------------------------------------------------------ //* Create a New Capture CDCStore CAB File //*----------------------------------------------------------- //CRSTORE EXEC PGM=SQDCONF //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SQDPARMS DD * create /home/sqdata/db2cdc/db2cdc_store.cab --type=store --alias=cdcstore --number-of-blocks=32 --number-of-logfiles=8 --data-path=/home/sqdata/db2cdc/data //* //*------------------------------------------------------- //* Display the Contents of the CDCStore CAB File //*------------------------------------------------------ //DISPLAY EXEC PGM=SQDCONF //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SQDPARMS DD * display /home/sqdata/db2cdc/db2cdc_store.cab --details /*- See the SQDconf Utility Reference for more details.
- The SQDconf create command defines the .cab file name and the location and size of the transient data store. Once created, this command should never be run again unless the storage agent is being recreated.
- Unlike the Capture or Publisher configuration files, changes to the CDCStore configuration file take effect immediately and do not require the usual stop or apply or start sequence.
- The Directory path references, in the example /home/sqdata/<type>cdc/ can be modified to conform to the operating environment but must match the SQData Variable Directory created in the Prepare Environment Section for the Capture.
-
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, and publishing committed data directly to Engines using TCP or 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.