Enabling the Database for Logging in Relational Databases

In this section:

To use Change Data Capture, the source database must be configured to use database logging. In most organizations this must be done by a database administrator.

This example uses dminv as the source table. To create this table, see How to Create Sample Procedures and Data.

The database table must be enabled for logging and the procedure depends on the database being used.


Top of page

x
Configuring CDC in Microsoft SQL Server

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

To use logging, the source database must be configured to use database logging. In most organizations this must be done by a database administrator.

Logging must be enabled at the database level since it is disabled by default. The database table must be enabled for logging as well. To enable logging, you must be connected as a member of the sysadmin server role.

To enable logging on a database level, 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 logging 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, select 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. This can be done from Microsoft SQL Server Management Studio. 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 = 'dminv', @role_name = NULL
select is_tracked_by_cdc FROM sys.tables WHERE name = 'tablename'

Example:

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 = 'dminv'

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


Top of page

x
Configuring CDC in Oracle

Configuring ORACLE for Change Data Capture can be done from Oracle SQL Developer or SQL Plus. In most organizations, this must be done by a database administrator.

connect sys/* as sysdba

To use the Change Data Capture, the database must be configured to use archiving. Enter the command:

ALTER SYSTEM ARCHIVE LOG START;

When using ORACLE XE (Express Edition) enter the following commands instead:

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG
ALTER DATABASE OPEN

Confirm that archive log is enabled with the command:

select log_mode from v$database ;

You should see the following result:

LOG_MODE
------------
ARCHIVELOG

To retrieve log information a user ID needs to certain system tables through public views. Issue these commands where userid represents the user id running CDC jobs.

grant select on "SYS"."V_$LOG" to userid ;
grant select on "SYS"."V_$LOGMNR_CONTENTS" to userid ;
grant select on "SYS"."V_$DATABASE" to userid;

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;

You can add supplemental log information for an entire database by entering:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Note:


Top of page

x
Configuring CDC in IBM DB2 Universal Database on Windows and UNIX

Note that CDC support for UDB and DB2 requires the CLI interface. 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

The DB2 table must have capture mode enabled. This can be done from the DB2 Command Center or a stored procedure.

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

Top of page

x
Configuring CDC in IBM DB2 Universal Database on System Z

Note: CDC support for UDB and DB2 requires the CLI interface.

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

Top of page

x
Configuring CDC in IBM DB2 Universal Database on iSeries

In this section:

Note:

Journaling for an iSeries (IBM i) library can happen in one of three ways:

  1. The library is initially created by DB2 with the CREATE COLLECTION collection-name or CREATE SCHEMA schema-name command which creates the library itself, catalog resources with the library and standard journal resources within the library and turns on the journaling. The CREATE SCHEMA and CREATE COLLECTION commands are synonyms for each other and there are no differences in their actual functions.
  2. Add journal resources to an existing not journaling library and turn them on, effectively turning it into a Journal Library. This option also allows for non-standard names to be used in the journaling.
  3. Add a QDFTJRN Data Area to an existing not journaling library. This effectively redirects journaling to happen in an alternate library (that already has journaling enabled). Thus, the not journaling library becomes a Journal Library with journals elsewhere.

These three items are described in the following sections.



x
Creating a Journal Library

Start the System i Main Menu screen with the user ID that will be used to run the CDC flows.

To start Structured Query Language interface, type:

STRSQL

To create a collection, type:

CREATE COLLECTION LIBRARY1

To create DB2 table TABLE1 in the library LIBRARY1, type:

CREATE TABLE LIBRARY1/TABLE1(F1 INT, F2 CHAR (10 ))

To insert a row into the TABLE1, type:

INSERT INTO TABLE1 VALUES (1,'Rec 1 t1') 

Exit the Structured Query Language interface, press the functional key [PF3]



x
Turning a Library Into a Journaling Library

To turn a library into a journaling library, use the following commands:

To create a library called LIBRARY2, type:

CRTLIB LIBRARY2 

To create a journal receiver called QSQJRN0001 for the library LIBRARY2, type:

CRTJRNRCV JRNRCV(LIBRARY2/QSQJRN0001)  

To create a journal QSQJRN in the library LIBRARY2, with journal receiver QSQJRN0001, type:

CRTJRN JRN(LIBRARY2/QSQJRN) JRNRCV(LIBRARY2/QSQJRN0001)

To start Commitment Control with a lock level for ALL with default journal QSQJRN in library LIBRARY2, type:

STRCMTCTL LCKLVL(*ALL) DFTJRN(LIBRARY2/QSQJRN) 

To change current library to LIBRARY2, type:

CHGCURLIB LIBRARY2  

To start Structured Query Language interface, type:

STRSQL

To create DB2 table TABLE1 in the library LIBRARY2, type:

CREATE TABLE LIBRARY2/TABLE2(F1 INT, F2 CHAR (10 ))

To insert a row into the TABLE1, type:

INSERT INTO TABLE2 VALUES (1, ‘Rec 1 t2’)

To exit the Structured Query Language interface, press the functional key [PF3].

To ensure journaling, (to turn it ON), by adding a not journaling pre-existing DB2 table TABLE2 to an existing Journal Library, type:

STRJRNPF FILE(LIBRARY2/TABLE2) JRN(LIBRARY2/QSQJRN)

As a result of this statement, the message appears: “Object of type *FILE already being journaled”.

Note: Journaling can be turned on and off for individual files with the STRJRNPF and ENDJRNPF commands.

At this point, the table TABLE2 is log enabled, which means that journaling works for this table. You can proceed to DataMigrator, creating Table Log Records synonym for this table and CDC flows.



x
Enabling Journaling on a Not Journaling Library

There might be situations when you need to keep a table(s) in one library, and the journaling in a different library. In order to get these results, the not journaling library should get connected to a Journal library.

The following names will be used for the objects in the examples below:

To create a library called LIBRARY3, type:

CRTLIB LIBRARY3 

To Create a QDFTJRN data area in the not journaling library and have LIBRARY3 point to the Journal Library LIBRARY1, type:

CRTDTAARA DTAARA(LIBRARY3/QDFTJRN) TYPE(*CHAR) LEN(25)
 VALUE('LIBRARY1  QSQJRN    *FILE')

As a result of this, the following statement message appears: “Data area QDFTJRN created in library LIBRARY3”.

Note: VALUE() is a fix position string with schema starting in position 1, journal name in position 11 and *FILE in position 21. In other words VALUE('SSSSSSSSSSJJJJJJJJJJ*FILE') with blank padding for names less than 10.

To change the current library to the library LIBRARY2 and display the properties of that library, type:

CHGCURLIB LIBRARY3

To start Structured Query Language interface, type:

STRSQL

To create DB2 table TABLE1 in the library LIBRARY2, type:

CREATE TABLE LIBRARY3/TABLE3(F1 INT, F2 CHAR (10 ))

To insert a row into the TABLE1, type:

INSERT INTO TABLE3 VALUES (1, ‘Rec 1 t3’)

To exit the Structured Query Language interface, press the functional key [PF3].

List of Optional Commands

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

CHGCURLIB LIBRARY1

To change user profile, type:

CHGPRF CURLIB(LIBRARY1)

All tables that are created within this COLLECTION are being journaled. In other words all the tables in this library have journals and changed data capture enabled for them.

You can confirm the current schema by issuing the command:

DSPLIBL

The result of this command should resemble the following image:

To review the current library, type:

DSPLIBL

Scroll down the pages to view the journaling information.

Please observe the attributes of the table TABLE3 and notice that it is located in the library LIBRARY3, and yet its current journal is QSQJRN which belongs to the library LIBRARY1.


Top of page

x
Configuring CDC in IBM DB2 Universal Database on z/OS

Note: CDC support for UDB and DB2 requires the CLI interface.

The configuration file DSNAOINI (*.ini file) should contain the proper value for the MVSATTACHTYPE attribute. This configuration file should contain either:

MVSATTACHTYPE=CAF

or

MVSATTACHTYPE=RRSAF
MULTICONTEXT=0

Review the user ID that is specified in the DataMigrator connection for the DB2 adapter. This user ID needs to have SYSADM authority and an authority for monitoring DB2 logs. The latter implies that the system table SYSIBM.SYSUSERAUTH should contain the values for the MON1AUTH and MON2AUTH fields set to Y.

To ensure the data changes written to the log, enabling Change Data Capture for a table, enter the following DB2 commands, where tablename is the name of the table to be log enabled.

ALTER TABLE tablename DATA CAPTURE CHANGES
COMMIT WORK;
GRANT ALL ON tablename TO PUBLIC;
COMMIT WORK;

To verify the tables have the logging enabled, issue the following DB2 select statement:

SELECT CREATOR, NAME, TYPE
FROM SYSIBM.SYSTABLES WHERE NAME LIKE '%tablename%' AND TYPE IN( 'T','P');

iWay Software