Db2 z/OS

This section lists details about configuring Oracle GoldenGate for Db2 z/OS.

Topics:

Prepare Database Users and Privileges

Learn about creating database users and assigning privileges for Oracle GoldenGate for Db2 z/OS.

Topics:

Database User for Oracle GoldenGate Processes

Oracle GoldenGate requires a database user account. Create this account and assign privileges according to the following guidelines.

Assign the Db2 privileges listed in the following table to the user by which Extract and Replicat will be running. These are in addition to any permissions that Db2 ODBC requires. All Extract privileges apply to initial-load and log-based Extract processes, except where noted. The following authorities can be provided by granting either SYSCTRL or DBADM plus SQLADM authority to the user running the Oracle GoldenGate processes.

Table 4-1 Privileges Needed by Oracle GoldenGate for Db2 z/OS

User privilege	Extract	Replicat
`MONITOR2` (does not apply to initial-load Extract)	Y	N
`SELECT ON` the following `SYSIBM` tables: `SYSTABLES` `SYSCOLUMNS` `SYSTABLEPART` `SYSKEYS` `SYSINDEXES` `SYSCOLAUTH` `SYSDATABASE` `SYSFOREIGNKEYS` `SYSPARMS` `SYSRELS` `SYSROUTINES` `SYSSYNONYMS` `SYSTABAUTH` `SYSAUXRELS`	Y	Y
`SELECT` on source tables^Foot 1	Y	N
`INSERT`, `UPDATE`, `DELETE` on target tables	N	Y
`CREATE` `TABLE`^Foot 2	N	Y
`EXECUTE` on ODBC plan (default is DSNACLI)	Y	N
Privileges required by `SQLEXEC` procedures or queries that you will be using.^Foot 3	Y	N

^Footnote 1

SELECT on source tables required only if tables contain LOB columns, or for an initial-load Extract, if used.

^Footnote 2

Required if using ADD CHECKPOINTTABLE from the command line interface to use the database checkpoint feature.

^Footnote 3

SQLEXEC enables stored procedures and queries to be executed by an Oracle GoldenGate process.

Prepare Database Connection, System, and Parameter Settings

Learn about configuring database connection, system and parameters settings for Oracle GoldenGate for Db2 z/OS.

Topics:

Configure a Database Connection

This section contains instructions for setting up the Extract and Replicat connections to a Db2 z/OS database.

Topics:

Ensuring ODBC Connection Compatibility

To ensure that you configure the Db2 ODBC initialization file correctly, follow the guidelines in the Db2 UDB z/OS ODBC Guide and Reference manual. One important consideration is the coding of the open and close square brackets (the [ character and the ] character). The square bracket characters are "variant" characters that are encoded differently in different coded character set identifiers (CCSID), but must be of the IBM-1047 CCSID in the ODBC initialization file. Db2 ODBC does not recognize brackets of any other CCSID. Note the following:

The first (or open) bracket must use the hexadecimal characters X'AD' (0xAD).
The second (or close) bracket must use the hexadecimal characters X'BD' (0xBD).

To set the correct code for square brackets, use any of the following methods.

Use the hex command in OEDIT and change the hex code for each character appropriately.
Use the iconv utility to convert the ODBC initialization file. For example, to convert from CCSID IBM-037 to IBM-1047, use the following command:
```
iconv -f IBM-037 -t IBM-1047 ODBC.ini > ODBC-1047.ini 
```
```
mv ODBC-1047.ini ODBC.ini 
```
Change your terminal emulator or terminal configuration to use CCSID IBM-1047 when you create or alter the file.

Specifying the Number of Connection Threads

Every Oracle GoldenGate process makes a database connection. Depending on the number of processes that you will be using and the number of other Db2 connections that you expect, you might need to adjust the following Db2 system parameters on the DSNTIPE DB2 Thread Management Panel:

MAX USERS (macro DSN6SYSP CTHREAD)
MAX TSO CONNECT (macro DSN6SYSP IDFORE)
MAX BATCH CONNECT (macro DSN6SYSP IDBACK)

If using RRSAF, allow:

Two DB2 threads per process for each of the following:
- Extract
- Replicat
- The Admin Client command DBLOGIN (logs into the database)
- DEFGEN utility (generates data definitions for column mapping)
One extra Db2 thread for Extract for IFI calls.
One extra Db2 thread for each SQLEXEC parameter statement that will be issued by each Extract and Replicat process. For more information about SQLEXEC, see the Reference for Oracle GoldenGate.

If using CAF, there can be only one thread per Oracle GoldenGate process.

Database Configuration

No special Db2 z/OS database settings are required for Oracle GoldenGate.

Specify the Path to the Initialization File

Specify the ODBC initialization file by setting the DSNAOINI environment variable in the z/OS UNIX profile, as in the following example:

export DSNAOINI="/etc/odbc810.ini"

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

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
SQL stored procedure and function includes demo_db2_setupb_os390.sql with the OGGINITB and OGGREADB SQL.
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

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

Extract reads the parameters, including the JCL parameters, from the parameter file created during installation.
Extract reports the startup information and prepares to write the trail files.
ODBC is used to gather information from the Db2 database and start replication.
The OGGINITB SQL stored procedure starts to prepare shared memory and to gather other data needed for replication.
The OGGIB001 external program called by the SQL stored procedure starts the memory monitor task using the OGGJT001 job setup program.
The OGGMT001 memory monitor task starts monitoring the ECSA and 64-bit shared memory.
The OGGREADB SQL Function calls the external program OGGRB001.
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.
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:

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.
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.
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.
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
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.
On your Db2 z/OS system in USS or OMVS, change directories to the directory containing zOSPrograms.tar.
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.
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.
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.
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-2 List of Editable Components

Component	From	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.

Use Shared Memory Manager for Extract

Oracle GoldenGate Extract starts a separate task, or job, from the WLM to monitor shared memory usage. This memory consists of a small 48 to 64 byte ECSA area, and a large 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, you must 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. 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 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.

Support Globalization Functions

Oracle GoldenGate provides globalization support and you should take into consideration when using this support.

Topics:

Replicating From a Source that Contains Both ASCII and EBCDIC

When replicating to or from a Db2 source system to a target that has a different character set, some consideration must be given to the encoding of the character data on the Db2 source if it contains a mix of ASCII and EBCDIC data. Character set conversion by any given Replicat requires source data to be in a single character set.

The source character set is specified in the trail header. Thus, the Oracle GoldenGate trail can contain either ASCII or EBCDIC data, but not both. Unicode tables are processed without any special configuration and are exempt from the one-character set requirement.

With respect to a source that contains both character encoding types, you have the following options:

You can use one Extract for all of your tables, and have it write the character data to the trail as either ASCII or as EBCDIC.
You can use different Extracts: one Extract to write the ASCII character data to a trail, and another Extract to write the EBCDIC character data to a different trail. You then associate each trail with its own Extract and Replicat process, so that the two data streams are processed separately.

To output the correct character set in either of those scenarios, use the TRAILCHARSETASCII and TRAILCHARSETEBCDIC parameters. The default is TRAILCHARSETEBCDIC. Without these parameters, ASCII and EBCDIC data are written to the trail as-is. When using these parameters, note the following:

If used on a single-byte Db2 subsystem, these parameters cause Extract to convert all of the character data to either the ASCII or EBCDIC single-byte CCSID of the subsystem to which Extract is connected, depending on which parameter is used (except for Unicode, which is processed as-is).
If used on a multi-byte Db2 subsystem, these parameters cause Extract to capture only ASCII or EBCDIC tables (and Unicode). Character data is written in either the ASCII or EBCDIC mixed CCSID (depending on the parameter used) of the Db2 z/OS subsystem to which Extract is connected.

Specifying Multi-Byte Characters in Object Names

If the name of a schema, table, column, or stored procedure in a parameter file contains a multi-byte character, the name must be double-quoted.

For more information about specifying object names, see Specifying Object Names in Oracle GoldenGate Input.

Prepare Tables for Processing

You must perform the following tasks to prepare your tables for use in an Oracle GoldenGate environment for Db2 z/OS.

Topics:

Disable Triggers and Cascade Constraints

Disable triggers, cascade delete constraints, and cascade update constraints on the target tables, or alter them to ignore changes made by the Oracle GoldenGate database user. Oracle GoldenGate replicates DML that results from a trigger or cascade constraint. If the same trigger or constraint gets activated on the target table, it becomes redundant because of the replicated version, and the database returns an error. Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ.

A delete is issued for emp_src.
It cascades a delete to salary_src.
Oracle GoldenGate sends both deletes to the target.
The parent delete arrives first and is applied to emp_targ.
The parent delete cascades a delete to salary_targ.
The cascaded delete from salary_src is applied to salary_targ.
The row cannot be located because it was already deleted in step 5.

Ensure Row Uniqueness for Tables

Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

Primary key
First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration.

Note:

If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.
If a table does not have an appropriate key, or if you prefer that the existing key(s) are not used, you can define a substitute key, if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.

Topics:

Using KEYCOLS to Specify a Custom Key

If a table does not have one of the preceding types of row identifiers, or if you prefer those identifiers not to be used, you can define a substitute key if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. For more information, see Reference for Oracle GoldenGate.

Handle Tables with ROWID Columns

Any attempt to insert into a target table that includes a column with a data type of ROWID GENERATED ALWAYS (the default) will fail with the following ODBC error:

ODBC error: SQLSTATE 428C9 native database error -798. {DB2 FOR OS/390}{ODBC DRIVER}{DSN08015} DSNT408I SQLCODE = -798, ERROR: YOU CANNOT INSERT A VALUE INTO A COLUMN THAT IS DEFINED WITH THE OPTION GENERATED ALWAYS. COLUMN NAME ROWIDCOL.

You can do one of the following to prepare tables with ROWID columns to be processed by Oracle GoldenGate:

Ensure that any ROWID columns in target tables are defined as GENERATED BY DEFAULT.
If it is not possible to change the table definition, you can work around it with the following procedure.

To workaround ROWID GENERATE ALWAYS:

For the source table, create an Extract TABLE statement, and use a COLSEXCEPT clause in that statement that excludes the ROWID column. For example:

TABLE tab1, COLSEXCEPT (rowidcol);

The COLSEXCEPT clause excludes the ROWID column from being captured and replicated to the target table.
For the target table, ensure that Replicat does not attempt to use the ROWID column as the key. This can be done in one of the following ways:
- Specify a primary key in the target table definition.
- If a key cannot be created, create a Replicat MAP parameter for the table, and use a KEYCOLS clause in that statement that contains any unique columns except for the ROWID column. Replicat will use those columns as a key. For example:
  
  MAP tab1, TARGET tab1, KEYCOLS (num, ckey);

Transaction Log Settings and Requirements

Know more about transaction log settings, requirements and the steps to add transaction logs for Oracle GoldenGate for Db2 z/OS.

Topics:

Prepare Db2 z/OS Transaction Logs for Oracle GoldenGate

Learn to configure the Db2 transaction logging to support data capture by Oracle GoldenGate Extract.

Oracle GoldenGate can capture Db2 transaction data from the active and archived logs. Follow these guidelines to configure the logs so that Extract can capture data.

To enable change capture for Oracle GoldenGate for Db2 z/OS, see Db2 z/OS: Enable Change Capture

Topics:

Enable Access to Log Records

Activate Db2 Monitor Trace Class 1 ("TRACE(MONITOR) CLASS(1) ") so that Db2 allows Extract to read the active log. The default destination of OPX is sufficient, because Oracle GoldenGate does not use a destination.

To Start the Trace Manually

Log on to Db2 as a Db2 user who has the TRACE privilege or at least SYSOPR authority.

Issue the following command:

start trace(monitor) class(1) scope(group)

To Start the Trace Automatically When Db2 is Started

Do either of the following:

Set MONITOR TRACE to "YES" on the DSNTIPN installation tracing panel.
Set 'DSN6SYSP MON=YES ' in the DSNTIJUZ installation job, as described in the Db2 UDB Installation Guide.

Note:

The primary authorization ID, or one of the secondary authorization IDs, of the ODBC plan executor also must have the MONITOR2 privilege.

Size and Retain Logs

When tables are defined with DATA CAPTURE CHANGES, more data is logged than when they are defined with DATA CAPTURE NONE . If any of the following is true, you might need to increase the number and size of the active and archived logs.

Your applications generate large amounts of Db2 data.
Your applications have infrequent commits.
You expect to stop Extract for long periods of time.
Your network is unreliable or slow.

To control log retention, use the DSN6LOGP MAXARCH system parameter in the DSNTIJUZ installation job.

Retain enough log data so that Extract can start again from its checkpoints after you stop it or after an unplanned outage. Extract must have access to the log that contains the start of the oldest uncommitted unit of work, and all logs thereafter.

If data that Extract needs during processing was not retained, either in online or archived logs, one of the following corrective actions might be required:

Alter Extract to capture from a later point in time for which log data is available (and accept possible data loss on the target).
Resynchronize the source and target tables, and then start the Oracle GoldenGate environment over again.

Note:

The IBM documentation makes recommendations for improving the performance of log reads. In particular, you can use large log output buffers, large active logs, and make archives to disk.

Use Archive Logs on Tape

Oracle GoldenGate can read Db2 archive logs on tape, but it will degrade performance. For example, Db2 reserves taped archives for a single recovery task. Therefore, Extract would not be able to read an archive tape that is being used to recover a table until the recovery is finished. You could use DFHSM or an equivalent tools to move the archive logs in a seamless manner between online DASD storage and tape, but Extract will have to wait until the transfer is finished. Delays in Extract processing increase the latency between source and target data.

Control Log Flushes

When reading the transaction log, Extract does not process a transaction until it captures the commit record. If the commit record is on a data block that is not full, it cannot be captured until more log activity is generated to complete the block. The API that is used by Extract to read the logs only retrieves full physical data blocks.

A delay in receiving blocks that contain commits can cause latency between the source and target data. If the applications are not generating enough log records to fill a block, Extract generates its own log records by issuing SAVEPOINT and COMMIT statements, until the block fills up one way or the other and is released.

In a data sharing group, each API call causes DB2 to flush the data blocks of all active members, eliminating the need for Extract to perform flushes.

To prevent Extract from performing flushes, use the Extract parameter TRANLOGOPTIONS with the NOFLUSH option.