Enabling the Database for Logging

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. IUD 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', @role_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.

IBM DB2 Universal Database

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. On all platforms the user ID to run flows using CDC should have the role of Administrator on the DataMigrator server.

Windows and UNIX

To enable the database for logging, issue the following commands from the DB2 command center:

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
GRANT DBADM ON database TO USER userid

In order to use Change Data Capture, each table must have data changes written to the log in an expanded format.

Enter the following command, where tablename is the name of the table.

ALTER TABLE tablename DATA CAPTURE CHANGES

To return a table back to normal logging, use the following command:

ALTER TABLE tablename DATA CAPTURE NONE

Note that if you recreate a database table, you must re-enable expanded logging.

To see which tables have expanded logging you can issue the following DB2 select statement.

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

zSeries

To enable the database for logging, issue the following DB2 commands:

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
GRANT DBADM ON database TO USER userid

In order to use Change Data Capture, each table must have data changes written to the log in an expanded format.

Enter the following command, where tablename is the name of the table.

ALTER TABLE tablename DATA CAPTURE CHANGES

To return a table back to normal logging, use the following command:

ALTER TABLE tablename DATA CAPTURE NONE

Note that if you recreate a database table, you must re-enable expanded logging.

To see which tables have expanded logging, you can issue the following DB2 select statement:

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

iSeries

The iSeries journaling does not require any special configuration other than to create a collection.

To create a collection for use with CDC, run the program STRSQL.

Issue the following command, where schema is the name of the collection.

CREATE COLLECTION schema

Change the current library to the schema you have created with the following command.

CHGCURLIB schema

You can confirm the current schema by issuing the command:

DSPLIBL

This command shows all the libraries and one should appear similar to this:

Library     Type     Device     Text
DBASE       CUR                 COLLECTION - created by SQL

Start the DataMigrator server and the Data Management Console. From a user ID that is a member of the Server Administrator role, expand Workspace, Configuration/Monitor, and then Configuration Files. Double-click on Server Profile to open edasprof.prf. Look for a line like

-SYSTEM CHGCURLIB DBASE

Ensure that the parameter, in this example DBASE, is the name of your collection. If you made a change to file, click the Save icon to save it.

Note: To use the STRSQL program to make changes to tables for testing Change Data Capture, start the program with the parameter shown, and issue a COMMIT command after the changes are made.

STRSQL COMMIT(*ALL) 
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 EXECUTE ON dbms_logmnr TO userid;
GRANT SELECT ANY TRANSACTION 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 line below is only required if you have more than one ORACLE connection, where connection is the name for the ORACLE connection used for Change Data Capture.

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

If you are logged in as a system administrator or without security, right-click the server name and select My Console, then Edit Server Profile.

ENGINE SQLORA SET CMDSERVER connection

iWay Software