Database Configuration

Learn about database configuration settings for Oracle GoldenGate for Db2 z/OS. The database settings are required for Oracle GoldenGate.

Install 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 zEC12, a minimum operating system release of 2.4, and a minimum Db2 release of 12.1. 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 authorized programs include the following:

   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 templates are included in the SQL script zOS_OGG_Setup_Template.sql with the OGGINITB and OGGREADB SQL.

3. JCL procedure, oggtask.jcl

Note:

The external names for the SQL and JCL name values are the default, 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

The process starts and runs as shown in the figure ablve, using the numbers 1 through 9. These steps are listed 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 the 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, OGGIB001 program returns a flag indicating there was a JCL error or setup issue and Extract begins to manage its 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 Extract:

1. Ensure that a library (PDSE) exists on the Db2 z/OS system and ensure that 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 procedure 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, 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 users access to create procedures in specific WLM address spaces.
   3. Grant user access to create procedures in specific schemas. Use the GRANT statement with the CREATIN option for the appropriate schema.
   4. Grant user access 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 starts, it uses RRSAF to attach to Db2. See the Db2 12 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 located in the Oracle Goldengate installation directory at lib/utl/zOSutils.zip, which contains a file calledzOSPrograms.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.
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, intended for the Oracle GoldenGate objects. Using this command installs the objects with the default names.

   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.

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, creating a shell script that renames the programs before copying them to the PDSE is recommended. 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 scriptname 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 template in the Oracle GoldenGate install directory contains the SQL statements to set up the stored procedure and function on the Db2 z/OS instance. The SQL script zOS_OGG_Setup_Template.sql contained in zOSutils.zip is for Db2 v12.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 format 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 Extract parameter. The procedure and function names, OGGINITB and OGGREADB, in the template are not configurable. You can rename each external name in the scripts and the PDSE if 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-2 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			zOS_OGG_Setup_Template.sql and Extract parm
WLM name	MVS		zOS_OGG_Setup_Template.sql
external program			zOS_OGG_Setup_Template.sql

Note:

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

Use Shared Memory Manager for Extract

Oracle GoldenGate Extract starts a separate task, or job, from the WLM to monitor shared memory usage. This job is a minimal task that runs in MVS, but it is not a WLM. The username of this script starts under must have permissions to execute when starting from the WLM (usually using something like RACF). This monitored shared memory consists of a small 48 to 64 byte ECSA area, and a larger 64-bit area based on the Extract buffer size.

Specific fields in shared memory get updated for every read performed by the Extract. These fields are updated whether or not the script returns any data. The monitor checks those fields to ensure the Extract has not become inactive. If the Extract is inactive, the shared memory is released, and the monitor ends. You can control the Memory Manager using the remote_memory_options parameter in the Extract’s parameter file.

You can specify multiple sub-parameters to configure the monitor task. You can configure the wait interval and inactive time the monitor uses by specifying sub-parameters of the remote memory options, as shown in the following example:

remote_memory_options wait_interval 2000 inactive_time 01:00

The wait interval is expressed in hundredths of seconds in the example and causes the monitor to wait 20 seconds between each memory check. If the monitor has checked for 1 hour (format HH:MM) and the Extract is still inactive, then the monitor will shut down after releasing the shared memory. If the Extract returns to an active state during that hour, the monitor will reset its state and continue monitoring.

The wait_interval can have values from 100 to 6000 and the default is 1000. The inactive_time can be from 00:10 to 12:00 and the default is 00:30. If the monitor does not start properly, the Extract displays a warning message in the Extract report and the Extract continues the processing. The Extract will attempt to release ECSA memory when it shuts down.

The remote memory parameter has three options to make this feature work. The syntax for these parameters is:

• task_procedure proc name

• task_library proc library

• task_setup task setup program

Example:

remote_memory_options task_procedure OGGPR001 
remote_memory_options task_library TEST.PROCLIB
remote_memory_options task_setup OGGJT001

You may specify multiple options in a single command, as shown below:

remote_memory_options task_procedure OGGPR001 task_library TEST.PROCLIB task_setup OGGJT001

Note:

The values for the remote memory parameter are case insensitive.

The default values are procedure name OGGPR001 and the task setup program OGGJT001. There is no default for task library as the procedure might be installed in one of the MVS system default procedure libraries. The task library parameter is only needed if the procedure is not in a system default library.

The memory task will start with a simple JOB card and an EXEC procedure name with parameters passed from the Extract. Some z/OS systems may require various other parameters on the job card. The JOB parameters can also be modified using the remote memory parameter, as shown in the example given below.

remote_memory_options task_jobname [valid MVS job name (see below)]
remote_memory_options task_acct_info [valid MVS acct value (see below)]
remote_memory_options task_programmer [valid MVS programmer name, Can use single quotes]
remote_memory_options task_class [valid MVS job class A to Z or 0 to 9]
remote_memory_options task_msgclass [valid MVS msgclass A to Z or 0 to 9]
remote_memory_options task_msglevel [valid MVS message level n or (,n) or (n,n)  n=valid digit]
remote_memory_options task_priority [valid MVS priority 0-15]

You can specify the JOB name using two valid characters and an asterisk, such as AA*. The default JOB name is GG*. The asterisk is replaced by six random numbers when it is specified. Otherwise, if you specify a one to eight byte character name, it must be a valid MVS job name.

Specify account values in any of the following valid MVS formats:

• OTXI

• ‘MY ACCT’

• (ACCT,1234,ABC)

For parameters, like acct_info and programmer, that allow special characters, enclose those in single quotes. In addition, the MVS rules about using double single quotes or ampersands within quotes continue to apply. The Extract does minimal validation for these parameters and leaves the complete validation to the MVS process. Extract will accept the first one if you specify duplicate parameters and ignore any duplicates.

A sample procedure JCL file will be included in the zOSPrograms.zip file. This JCL does not replace the WLM procedure. The monitor, created in the WLM, uses the JCL to run in MVS. A JCL procedure allows more flexibility if you add commands or JCL that support job output archiving or other procedures.

Note:

During installation and setup for the memory manager, temporarily turning off the memory manager may be necessary when you must wait for RACF setup issues, initiator usage, job class usage, or other similar issues related to the memory manager.

The Extract will operate in its legacy mode in this instance and release ECSA, and the 64-bit shared memory. The Extract shows the release of shared memory at the end of the Extract report.

Oracle does not recommend this as a permanent solution. When used, the user is responsible for monitoring the release of ECSA and 64-bit memory. The parameter to turn on this feature begins with an underscore to remind the user that the system is running in this mode. Other remote memory options can be left in place and used by removing the option to turn memory management off. The remote memory management parameter defaults to being on, and it is specified as shown:

remote_memory_options _remote_memory_on
remote_memory_options _remote_memory_off

The JCL has the following format:

//*==================================================================== 
//* EXAMPLE JCL FOR RUNNING THE COMMON MEMORY MONITOR PROCEDURE
//* ADDRESS SPACE NEEDING AN AUTHORIZED LOAD LIBRARY
//* NOTE: THE PROGRAM OGGMT001 CAN BE RENAMED IN THE LIBRARY BUT THE
//*       NEW NAME MUST MATCH THE PROGRAM NAME IN THIS JCL
//*==================================================================== 
//OGGDSNNA PROC RGN=0K TR=,EX=,MEM=,LEN=,SEC=,DUR=,VER=
//OGGDSNNX EXEC PGM=OGGMT001,REGION=&RGN,TIME=NOLIMIT,
// PARM='&TR &EX &MEM &LEN &SEC &DUR &VER'
//*--------------------------------------------------------------------
//* REPLACE &PREFIX.**.AUTHLOAD LIBRARIES WITH SITE SPECIFIC FILE(S)
//* ALSO REPLACE THE CEE LIBRARY WITH SITE SPECIFIC FILE
//* DSNN COULD REPRESENT A DB2 SPECIFIC LOAD LIBRARY IF ONE EXISTS
//*--------------------------------------------------------------------
//STEPLIB  DD DISP=SHR,DSN=&PREFIX..WLMDSNN.USER.AUTHLOAD
//         DD DISP=SHR,DSN=CEE.SCEERUN
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*       
    

Modify the libraries marked with PREFIX so that they work in your system. If you renamed the program OGGMT001 you copied from the zOSPrograms.tar file, you must change it in the JCL. The null parameters on the PROC statement are there for information purposes. The job OGGJT001 setup program supplies those values using information passed from the Extract. You may also specify as many step library dataset names as required. The JCL procedure supplied in the zOSPrograms.tar file gives an example using more than one step library.