Replicating IBM Db2 for z/OS data

Downloading archive of tersed files

To begin allocating a data set with the started task on the mainframe, the Db2 admin must:

1. Locate the z/OS started task in IBM Support's Fix Central database. The file name is ceclrd-VERSION.zip, where VERSION is the current version of the started task.

   5.1.0 and later Fix Central

2. Unzip the archive to obtain the tersed files.

Allocating a data set with the started task on the mainframe

The Db2 admin must complete the following steps to add and start the task which allocates a data set.

1. Extract the tersed files from ceclrd-VERSION.zip.
2. Transfer the *.TRS files to z/OS as binary files. Leave them on the Unix file system.
3. Transfer CECRDALC, CECRDCPY and CECRDTRS as text files to a data set appropriate for Job Control Language (JCL) scripts.
4. Edit the CECRDALC, which can be used to allocate data sets. Provide a job statement that is appropriate for your site. Set HLQ (high level qualifier) to the installation location. If necessary, provide the VOLUME or other characteristics of the data sets. Submit as a batch job.
5. Edit CECRDCPY, which can be used to copy *.TRS files from the Unix file system into data sets. Provide a JOB statement that is relevant to your site. Set HLQ to the installation location. Change PATH to the Unix file system directory where the *.TRS files are located. Submit as a batch job.
6. Edit CECRDTRS, which can be used to understand the data sets. Provide a job statement that is suitable for your site. Set HLQ to the installation location. Submit as a batch job.
7. Verify that HLQ.SCACLOAD contains the load modules, HLQ.SCACMSGS contains the English and translated messages, and HLQ.SCACSAMP contains the sample JCL scripts.
8. Optional: Remove the *.TRS files from the Unix file system. Remove HLQ.SCACLOAD.XMIT, HLQ.SCACLOAD.TRS, HLQ.SCACMSGS.TRS, and HLQ.SCACSAMP.TRS from your system.
9. See HLQ.SCACSAMP for the remaining JCL scripts.
10. Run the following command with the correct HLQ and VOLUME to APF authorize HLQ.SCACLOAD:

    S APF,F=ADD,V=VOLUME,D='HLQ.SCACLOAD'
    

11. Edit CECRDAPF, which can be used to confirm APF authorization. Provide a job statement that is appropriate for your site. Set HLQ to the installation location. Submit as a batch job. Check the job output to see whether HLQ.SCACLOAD is correctly APF-authorized. If you try to use the Db2 log reader without APF authorization, the job returns ABEND 047 code which means the job ended abnormally. Check that the batch job returns 0. If your user does not have privileges, request a z/OS admin to APF-authorize the load library HLQ.SCACLOAD.
12. Edit CECRDDLN, which can be used to provide a diagnostic logstream name. Provide a job statement that is appropriate for your site. Change the logstream name. Submit as a batch job.
13. Edit CECRDBPL, which can be used to bind the database plan. Provide a job statement that is appropriate for your site. Set HLQ to the installation location. Set DB2HLQ to the Db2 installation location. Change CECLRDPK to your selected Db2 package. Change CECLRDPL to your selected Db2 plan. Change the USERID to the wanted package/plan owner. Change SSID to the Db2 subsystem ID or group attachment name. Submit as a batch job.
14. Edit CECRDCFI, which can be used to create configuration data sets (HLQ.CACCFGD and HLQ.CACCFGX). Provide a job statement that is appropriate for your site. Set HLQ to the installation location. Change the STREAMNAME to the logstream that you generated in the previous step. Set LISTENPORT to the remote log reader port. Change the DB2SUBSYSTEMNAME to the Db2 subsystem ID or group attachment name. Change DB2PLANNAME to the Db2 plan name that you created in the previous step. Submit as a batch job.
15. Optional: Modify CECRDCFG and submit as a batch job to change the configuration.
16. Transfer the TLS root CA certificate as a text file to the z/OS data set.
17. Edit CECRDTL1, which can be used to set up TLS encryption. Provide a job statement that is appropriate for your site. Change LRSDKR to the name of the key ring. Change USERID to the remote log reader's user ID. Change PATH.TO.CA to the data set containing the root CA certificate. Change LRSDCA to the root CA label. Change hostname.example.com to the hostname. Change PATH.TO.REQ to the data set used to write the certificate request. Submit as a batch job.
18. Send the certificate request as a text file to your certificate authority (CA).
19. Use the certificate request to get a signed certificate chain from the CA. A certificate chain includes the host certificate, any intermediate CA certificates, and the root CA certificate, in that order.
20. Transfer the signed certificate chain as a text file to a z/OS data set.
21. Edit CECRDTL2, which is used to import the signed certificate chain. Provide a job statement that is appropriate for your site. Change PATH.TO.PEM to the data set containing the signed certificate chain. Change USERID to the remote log reader's user ID. Change LRSDPK to the name of the private key. Submit as a batch job.
22. Verify the contents of CECRDTLP and modify the AT-TLS profile to enable TLS encryption on the log reader's port.
23. Edit CECRDSRV, which can be used to start the log reader. Provide a JOB statement that is appropriate for your site, or remove it. Set HLQ to the installation location. Set DB2HLQ to the Db2 installation location. To start a previously initiated task, use the START command or submit it as a batch job.
24. Check the job output to see whether the Db2 log reader service is operating. Data Replication uses one task control block (TCB) for each running replication.

Enabling logging on system tables

Ask the Db2 admin to enable DATA CAPTURE CHANGES. Data Replication will attempt to enable DATA CAPTURE CHANGES on these tables if they do not already have it enabled:

• SYSCOLUMNS
• SYSTABLES
• SYSINDEXES
• SYSKEYS
• SYSKEYCOLUSE
• SYSTABLESPACE
• SYSSEQUENCES
• SYSSEQUENCESDEP
• SYSCHECKS
• SYSCHECKDEP
• SYSFOREIGNKEYS
• SYSRELS

Creating a user ID for Data Replication

1. Ask the Db2 admin to create a user ID for operating Data Replication. For ease of use, configure the Data Replication user ID with a non-changing password to avoid ongoing administration. If the site has a password change policy, then the password change on the mainframe must be coordinated with updated connectors.

2. Ask the Db2 admin to grant the following Db2 privileges for the user ID for operating data replication:

   SELECT ON SYSIBM.SYSCHECKS
   SELECT ON SYSIBM.SYSCHECKDEP
   SELECT on SYSIBM.SYSCOLAUTH
   SELECT on SYSIBM.SYSCOLUMNS
   SELECT on SYSIBM.SYSDATABASE
   SELECT on SYSIBM.SYSDATATYPES
   SELECT on SYSIBM.SYSDBAUTH
   SELECT on SYSIBM.SYSDUMMY1
   SELECT on SYSIBM.SYSFOREIGNKEYS
   SELECT on SYSIBM.SYSINDEXES
   SELECT on SYSIBM.SYSKEYS
   SELECT ON SYSIBM.SYSKEYCOLUSE
   SELECT on SYSIBM.SYSLEVELUPDATES
   SELECT on SYSIBM.SYSROUTINES
   SELECT on SYSIBM.SYSPACKSTMT
   SELECT on SYSIBM.SYSPARMS
   SELECT on SYSIBM.SYSRELS
   SELECT on SYSIBM.SYSSCHEMAAUTH
   SELECT ON SYSIBM.SYSSEQUENCES
   SELECT ON SYSIBM.SYSSEQUENCESDEP
   SELECT on SYSIBM.SYSSYNONYMS
   SELECT on SYSIBM.SYSTABAUTH
   SELECT on SYSIBM.SYSTABCONST
   SELECT on SYSIBM.SYSTABLES
   SELECT on SYSIBM.SYSTABLESPACE
   MONITOR2 system privilege
   SELECT on all replication source tables
   ALTER on all replication source tables that do not already have DATA CAPTURE CHANGES turned on
   EXECUTE on plan CECLRDPL (or the value of DB2PLANNAME in CECRDCFI)
   

3. Request the EXECUTE z/OS permission for APF-authorized load library in the dedicated WLM address space.