A synonym for a log table allows reading from the database log records for a selected table. The synonym contains the same columns as the table itself plus three additional columns that identify changes to the table.

This synonym can be used as a source in a data flow with the special load type of "IUD Processing." The name is an abbreviation for Insert/Update/Delete.

In addition, there are specific requirements for supported databases.

Microsoft SQL Server 2008

Microsoft SQL Server 2008 supports Change Data Capture in the Enterprise Evaluation and Developer editions.

Change Data Capture must be enabled at the database level since it is disabled by default. This can be done from the Microsoft SQL Server Management Studio. To enable CDC, you must be connected as a member of the sysadmin server role. Connect to the database you want to enable and issue the following commands:

exec sys.sp_cdc_enable_db
select name, is_cdc_enabled from sys.databases

You will see a list of all databases and a 1 if CDC is enabled for the database, a 0 otherwise.

In addition, the SQL Server Agent and two jobs must be running. The jobs are created automatically when the database is enabled. For example, if the database is called "main" the jobs would be named "cdc.main_capture" and "cdc.main_cleanup."

To check if the SQL Server Agent is running, in the Microsoft SQL Server Management Studio in the Object Explorer window, check the icon for SQL Server Agent. It should have a green arrow. If it does not, right-click SQL Server Agent and select Start.

Each table to be used must have CDC enabled. You must be a member of the db_owner database role to run the stored procedure. Issue the following commands, where tablename is the name of the table.

exec sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name =
'tablename', @roll_name = NULL
select is_tracked_by_cdc FROM sys.tables WHERE name = 'tablename'

You will see a 1 if the table is enabled, a 0 otherwise.

UDB and DB2

To use Change Data Capture the database must be configured to use database logging. In most organizations this must be done by a database administrator. Note that CDC support for UDB and DB2 requires the CLI interface.

Note: iSeries users that are using the user ID that will run the CDC flow should start the program strsql and issue the following commands:

CREATE COLLECTION databaseCHGPRF CURLIB(database)

UPDATE DATABASE CFG FOR database USING DFT_SQLMATHWARN YES
UPDATE DATABASE CFG FOR database USING LOGARCHMETH1 LOGRETAIN
UPDATE DBM CFG USING DISCOVER DISABLE
UPDATE DBM CFG USING DISCOVER_INST DISABLE

On all platforms the user id to run the flows should be an administrator on the DataMigrator server.

Note: For DB2/2400, the user that runs the flows should also be:

• An Administrator, or belong to the Administrators group on DB2.
• Associated with a current library on DB2.

GRANT DBADM ON database TO USER userid

The table to be logged must have capture mode enabled. This can be done from DB2 command center or a stored procedure, where tablename is the name of the table.

ALTER TABLE tablename DATA CAPTURE CHANGES

Note that if you recreate a database table, you must re-enable capture mode.

To see which tables have capture mode enabled, you can issue the following DB2 select statement.

SELECT CREATOR, NAME, TYPE
    FROM SYSIBM.SYSTABLES WHERE DATA_CAPTURE = 'Y'

Note: For z/OS, use DATACAPTURE (without underscore).

ORACLE 10g and 11g

To use the Change Data Capture, the database must be configured to use archiving. This can be done from ORACLE SQL Developer or from a stored procedure. In most organizations, this must be done by a database administrator.

ALTER SYSTEM ARCHIVE LOG START;

To enable logging a user ID must be granted the following where userid represents the user ID to be used.

GRANT DBA TO userid;
GRANT EXECUTE ON dbms_logmnr TO userid;
GRANT SELECT ANY TRANSACTION TO userid;
GRANT execute catalog role TO userid;

The table to be logged must have all data columns logged by issuing the following command where tablename represents the table to be logged.

ALTER TABLE tablename ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Note that if you recreate a database table, you must re-issue this command.

Note: Change Data Capture for ORACLE uses the DBMS_LOGMNR package to extract values from the ORACLE redo log files. This does not support the following data types: LONG, LONG RAW, CLOB, BLOB, NCLOB, ADT, and COLLECTION.

The date and time formats must be altered so that they can be retrieved properly. The following commands must be added to the profile in effect when using Change Data Capture.

To edit your profile, right-click the server name and select My Console, Edit My Profile and add the lines shown below.

Note: If you are logged in as a System Administrator, right-click the server name and select My Console, Edit Server Profile.

ENGINE SQLORA ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
ENGINE SQLORA ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SSXFF';