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 Enable TRANDATA for Non-Oracle Databases

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

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

  2. 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 must also have the MONITOR2 privilege.

Size and Retain Logs

More data gets logged when tables are defined with DATA CAPTURE CHANGES 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 the Extract for long periods.

  • Your network is unreliable or slow.

Use the DSN6LOGP MAXARCH system parameter in the DSNTIJUZ installation job to control log retention.

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 after that.

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 again.

Note:

The IBM documentation makes recommendations to improve 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, which degrades performance. For example, Db2 reserves taped archives for a single recovery task, leading to Extract not being able to read an archive tape that is being used to recover a table until the recovery completes. You could use DFHSM or an equivalent tool to move the archive logs seamlessly between online DASD storage and tape, but Extract will have to wait until the transfer completes. Delays in Extract processing increase the latency between source and target data.

If the Oracle GoldenGate Extract can or will be reading from tape, either physical tape or virtual tape, it is highly recommended to ensure that the following ZPARMs are considered:
DEALLCT

The DEALLCT subsystem parameter determines the length of time that an archive read tape unit is to be allowed to remain unused before it is deallocated.

Update: option 43 on panel DSNTIPB

DSNZPxxx: DSN6LOGP DEALLCT

Oracle recommends that DEALLCT be set to 0, 1 (1 second deallocation delay). The default is to deallocate immediately. This will prevent Db2 from immediately reallocating a tape between reads from Extract.

MAXRTU

The MAXRTU subsystem parameter specifies the maximum number of dedicated tape units that can be allocated to concurrently read archive log tape volumes.

Update: option 43 on panel DSNTIPB

DSNZPxxx: DSN6LOGP MAXRTU

The default is 2 tape units, but it should be set to the number of expected processes that may read concurrently from tape+1. For example, if there are 4 Extracts and 2 other applications that may read from tapes, then MAXRTU should be at least 7 (4 + 2 + 1).

For best performance and least delays when reading from tape, Oracle GoldenGate Extract should use:
TRANLOGOPTIONS BUFSIZE 33554432

This will be an indication to Extract to read up to 32MB at a time from the transaction log, instead of the default of 1MB per read. This setting will not increase the memory allocations Oracle GoldenGate uses on z/OS, either ECSA (64 bytes), or 64-bit shared memory (1MB). Db2 transient memory allocations may increase while the Extract reads the large buffers.

Also, in the Extract parameter file, prior to the DBLOGIN parameter, the following may also be used to provide the ability to further tune the Db2 driver TCP communication buffering. For example:
SETENV(DB2SOSNDBUF='524288') 
SETENV(DB2SORCVBUF='524288')

This would inform the Db2 driver to use 512KB for the send and receiver buffers. Other values could be used, and would be tunable for each install.

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 Extract uses 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 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.