Change Data Capture (CDC) Configuration

The CDC Configuration section of the synonym properties has the following parameters, which are created for a table log.

Note: Each permitter has a default value of a global variable whose name begins with &&CDC_ and is followed by the parameter name. Normally you would just type over or select a value to replace the default with a constant value, then save the synonym. Alternatively, the values could be set dynamically in a process flow in a Set Variables object.

The following options are available:

DB Log Parameters
DATA_ORIGIN

This is set to DBMSLOG to indicate the origin of the data is a DBMS table log.

START

Indicates the starting point for reading log records.

  • CHKPT. After the last transaction retained in checkpoint file. This is the default.
  • CUR_TRAN. The first transaction in the DBMS log after the job started.
  • CUR_LOG. The first available transaction in the current active DBMS log.

    Note: This option is not supported by IBM DB2 on Mainframe (IBM System z).

  • Timestamp. The first transaction after a selected point in time (YYYYMMDDHHMMSS).

    Note: This option is available for DB2 synonyms only.

CHKPT_SAVE

Indicates if the last processed transaction should be saved in the checkpoint file.

  • YES. Retains the last processed transaction in the checkpoint file.
  • NO. Does not retain the last processed transaction. This option facilitates testing, because use of Sample Data or Test Transforms does not reset the checkpoint.
CHKPT_FILE

Indicates the location and name of the file to use to store checkpoint information in a:

  • Physical location. The full directory and file name in the format for the operating system used. This option should be used when there are multiple flows that will access the log for the same table. Each flow should have a unique synonym with a unique name for the checkpoint file.
  • Application directory. The file is created in the same application directory as the synonym.
  • Blank. This is the default. If no file name is specified, a file is created with the same name as the synonym and the extension chp.

iSeries Parameters

The following three parameters, LOG_NAME, LOG_LOCATION, and COMMIT_MODE are available on iSeries platforms only, with the DB2 database. The LOG_NAME and LOG_LOCATION parameters can be used when you are required to use a non-standard journal and/or a non-current location. An example would be an alternate library, where the journal and location are explicitly manually defined by a database administrator (versus a CREATE COLLECTION). Another example would be a requirement to use a remote journal, instead of the local one.

LOG_NAME

Indicates the name of the journal.

LOG_LOCATION

Indicates the location name.

COMMIT_MODE

Supports the transactional commitment control.

  • ON. Normal transaction mode with a commit used. This is the default.
  • OFF. No commit issued or DBMS uses auto-commit mode.
  • DTC. Distributed transactions mode is used.
Listening Parameters
POLLING

Indicates the polling interval in seconds, or how often the database log is scanned. The default is 1 second.

TIMEOUT

Indicates the timeout interval in seconds. If there is no activity in this time interval, the processing will stop. A value of zero means there is no limit. The default value is 1 second.

Note that the two options above work together. For example, if POLLING is 2 and TIMEOUT is 10, the server polls the log every 2 seconds for new transactions. If there are no new transactions after 10 seconds, then polling stops.

Read Limits
MAXLUWS

Indicates the maximum number of database transactions to process before stopping the job. A value of zero (0) means all transactions. The default value is 1 transaction.

Note: For normal Change Data Capture processing, specify a location for CHKPT_FILE and set CHKPT_SAVE to YES. Then set START to CHKPT so that processing starts at the last saved checkpoint.

However, note that any operation that reads from the log table, including Test Transforms, resets the checkpoint. In that case, a test transform uses the data and that data is no longer available to subsequent runs of the data flow.


iWay Software