Installing Extract Components on Db2 z/OS

The Oracle GoldenGate Db2 z/OS Extract uses SQL objects to access and read the Db2 log. These Oracle GoldenGate Db2 z/OS objects require a minimum hardware platform of z10, a minimum operating system release of 1.13, and a minimum Db2 release of 11. The components consist of executable load modules, SQL stored procedures and functions, and external programs called via the stored procedures. these components are:

  1. External programs (authorized) includes the following programs:

    1. oggib001 – Initialization and utility program

    2. oggrb001 – Log read program functionality

    3. oggmt001 – Stand-alone program that monitors ECSA and 64-bit memory

    4. oggjt001 – Setup program for the oggmt001 startup JCL run from oggib001 program

    5. oggfr001 – Utility for use by a DBA under guidance from Oracle Support

  2. SQL stored procedure and function includes demo_db2_setupb_os390.sql with the OGGINITB and OGGREADB SQL.

  3. JCL procedure, oggtask.jcl

Note:

These external names, SQL and JCL names are the default names, which you can edit and update. This process is discussed in the subsequent sections.

The Replication Process for Db2 z/OS Extract figure illustrates the replication process for the Db2 z/OS Extract and its mainframe components.

Figure 4-1 Replication Process for Db2 z/OS Extract


Replication Process for Db2 z/OS Extract

The process starts and runs as shown using the numbers 1 through 9 in the figure, which is given below:

  1. Extract reads the parameters, including the JCL parameters, from the parameter file created during installation.

  2. Extract reports the startup information and prepares to write the trail files.

  3. ODBC is used to gather information from the Db2 database and start replication.

  4. The OGGINITB SQL stored procedure starts to prepare shared memory and to gather other data needed for replication.

  5. The OGGIB001 external program called by the SQL stored procedure starts the memory monitor task using the OGGJT001 job setup program.

  6. The OGGMT001 memory monitor task starts monitoring the ECSA and 64-bit shared memory.

  7. The OGGREADB SQL Function calls the external program OGGRB001.

  8. The OGGRB001 external program repeatedly calls the Db2 log read program to create a result set that returns 1 to many log record buffers to the Extract.

  9. When a log record result set is complete, OGGRB001 ends after sending the result set to the Extract.

Extract repeats steps 7 to 9 until shut down or abnormal termination. If the memory task fails to start, OGGI001 program returns a flag indicating there was a JCL error or setup issue and Extract manages its own memory. If the memory task starts properly, the memory task tests constantly changing fields in the 48-byte ECSA shared memory. These fields stop changing if the Extract terminates for any reason. At that point, the memory manager waits in case the Extract or network is slow and releases the memory before shutting down after a configured time limit.

To install the components needed for Oracle GoldenGate for Db2 z/OS for Extract:
  1. Ensure that a library (PDSE) exists on the Db2 z/OS system and an entry for it is made in the authorized library list. This library is the location where the Oracle GoldenGate external program objects will reside.
  2. Ensure that an APF-authorized WLM environment exists that references the PDSE from the preceding step. Oracle recommends that NUMTCB value for the WLM environment be 10-40 for stored procedures. The NUMTCB value depends on the maximum number of Extracts that are running concurrently against the database and on how much throughput each Extract requires. If you want flexibility in setting NUMTCB, you specify it in the startup JCL for the WLM, but not in the creation panel.
  3. You can set up security for the WLM application environments and for creating stored procedures by completing the following:
    1. (Optional) Specify which WLM-established address spaces can run stored procedures. If you do not complete this step, then any WLM-established address space can run stored procedures.
    2. Grant access to users to create procedures in specific WLM address spaces.
    3. Grant access to users to create procedures in specific schemas. Use the GRANT statement with the CREATIN option for the appropriate schema.
    4. Grant access to users to create packages for procedures in specific collections. Use the GRANT statement with the CREATE option for the appropriate collection. 
    5. Grant access to refresh the WLM environments to the appropriate people.
    6. Add additional RACF authority to the appropriate people, allowing the WLM procedures to start the memory manager job.
  4. Ensure the ID used to run the WLM startup JCL procedure has permission to use RRSAF. Each time one of the Db2 WLM address spaces is started, it uses RRSAF to attach to Db2. See the Db2 11 for z/OS Installation and Migration Guide
  5. In the Linux or UNIX installation of Oracle GoldenGate for Db2 z/OS, there is a ZIP file called zOSPrograms.zip. Unzip zOSPrograms.zip to zOSPrograms.tar and copy zOSPrograms.tar in binary mode to your Db2 z/OS system into an HFS directory.
  6. On your Db2 z/OS system in USS or OMVS, change directories to the directory containing zOSPrograms.tar.
  7. Restore the objects with the command: tar -xovf zOSPrograms.tar.

    Note:

    In this command, the copy target is double-quote forward-slash single-quote authorized PDSE name single-quote double quote. The -X is an uppercase capital X not a lowercase x.

  8. Copy the objects to the authorized PDSE. Use the cp –X ogg[irmj][abt][0-9]* “//’authorized_PDSE_name’” where authorized_PDSE_name is the name of the APF authorized PDSE, which is intended for the Oracle GoldenGate objects. Using this command installs the objects with the default names.
  9. Installing the scripts with different names allows you to conform with system protocols, or it allows you to run multiple versions of Oracle GoldenGate. To install the scripts with different names, it is recommended to create a shell script that renames the programs before copying them to the PDSE. An example of the shell script is given in the following code snippet.
    #!/bin/bash
# Copy new programs renaming them to version 21.12 names.
cp oggib001 oggi2112
cp oggrb001 oggr2112
cp oggmt001 oggm2112
cp oggjt001 oggj2112
cp -X oggi2112 “//’SYS4.WLMDSNA.AUTHLOAD’”
cp -X oggr2112 “//’SYS4.WLMDSNA.AUTHLOAD’”
cp -X oggm2112 “//’SYS4.WLMDSNA.AUTHLOAD’”
cp -X oggj2112 “//’SYS4.WLMDSNA.AUTHLOAD’”

    You can run the script using chmod +x command. You can copy and reuse this script for new versions.

  10. You must create the SQL procedures using your SQL tool of choice so that Oracle GoldenGate can call the Extract objects. The Oracle GoldenGate stored procedures should have permission granted to only those users that use them for replication.

    An example SQL script in the Oracle GoldenGate install directory contains the SQL statements to set up the stored procedures on the Db2 z/OS instance. The demo_db2_setupb_os390.sql script is for Db2 v11.1 and higher and can run from any SQL tool on any platform that can connect to your Db2 z/OS instance. This script must run on the Db2 instance that you use with your Extract. The script provided in the remote installation directory is in ASCII format. The same script is restored through zOSPrograms.tar on the Db2 z/OS system in EBCDIC and is suitable for use through native Db2 z/OS tools such as SPUFI.

    Edit the following line before running the scripts:

    • Modify the WLM ENVIRONMENT line to use the correct name for the WLM environment that you will use for Oracle GoldenGate.

Note:

The oggifi0001 schema name is configurable using the TRANLOGOPTIONS REMOTESCHEMA schemaname parameter. The procedure names are not configurable. Each of the external names in the script and the PDSE can be renamed as long as the script names and the PDSE object names match. Changing these names is part of the procedure that allows migration to new versions or if specific naming procedures must be adhered to on Db2 z/OS. The following table contains a check list of components that you may wish to edit and/or update:

Table 4-3 List of Editable Components

Component From Rename Where

oggib001

tar file

  

authorized PDSE

oggrb001

tar file

  

authorized PDSE

oggmt001

tar file

  

authorized PDSE & proc library

oggjt001

tar file

  

authorized PDSE & Extract parm

oggpr001

tar file

  

procedure library & Extract parm

proclib

 MVS  

add Extract parm if needed

step libraries

MVS

  

WLM and oggpr001 procedure library

remoteschema

    

demo_db2_setupb_os390.sql and Extract parm

WLM name

MVS

  

demo_db2_setupb_os390.sql

external program

    

demo_db2_setupb_os390.sql

Note:

Remember to perform all these steps after every new patch installation.