RDBMS Change Detection Connector

The RDBMS Change Detection Connector enables IBM TDI to detect when changes have occurred in specific RDBMS tables. Currently, setup scenarios are provided for tables in Oracle, DB2, MS SQL, Informix and Sybase databases.

RDBMS's have no common mechanism to inform the outside world of the changes that have been taking place on any selected database table. To address this shortcoming, IBM TDI assumes that some RDBMS mechanism (such as a trigger, stored procedures or other) is able to maintain a separate change table containing one record per modified record in the target table. Sequence numbers are also maintained by the same mechanism.

Similar to an LDAP Change Detection Connector, the RDBMS Change Detection Connector communicates with the change table that is structured in a specific format that enables the connector to propagate changes to other systems. The format is the same that IBM DB2 Information Integrator (version 8) uses, providing IBM TDI users with the option to use DB2II to create such tables, or create the tables in some other manner. The RDBMS Change Detection Connector keeps track of a sequence number so that it only reports changes since the last iteration through the change table.

The RDBMS Change Detection Connector uses JDBC to connect to a specific RDBMS table. See the JDBC Connector for more information about JDBC driver issues.

The RDBMS Change Detection Connector only operates in Iterator mode.

This connector supports Delta Tagging at the Entry level only.

The RDBMS Change Detection Connector reads specific fields to determine new changes in the change table (see Change table format). The RDBMS Change Detection Connector reads the next change table record, or discovers the first change table record. If the RDBMS Change Detection Connector finds no data in the change table, the RDBMS Change Detection Connector checks whether it has exceeded the maximum wait time. If the RDBMS Change Detection Connector has exceeded the maximum wait time, it returns null to signal end of the iteration. If the RDBMS Change Detection Connector finds no data in the change table, and has not exceeded the maximum wait time, it waits for a specific number of seconds (Poll Interval), then reads the next change table record.

If the Connector returns data in the change table, the RDBMS Change Detection Connector increments and updates the nextchangelog number in the User Property Store (an area in the System Store tailored for this type of persistent information).

For each Entry returned, control information (counters, operation, time/date) is moved into Entry properties. All non-control information fields in the change table are copied as is to the Entry as attributes. The Entry objects operation (as returned by getOperation) is set to the corresponding changelog operation (Add, Delete or Modify).

This Connector in principle can handle secure connections using the SSL protocol; but it may require driver specific configuration steps in order to set up the SSL support. Refer to manufacturer's driver documentation for details.

Configuration

The Connector needs the following parameters:

JDBC URL

See documentation for your JDBC provider. This is the JDBC URL to the target database.

Username

This is the user ID with which the Connector signs on to the RDBMS. Only the tables available to this user are shown.

Password

The password for the user. It is used to authenticate to the RDBMS using the username/password authentication mechanism.

Schema

The schema (that is, the owner) of the table of the database that we want to monitor. If left blank, the value of the Username parameter is used.

JDBC Driver

The JDBC driver class name. The default value for this parameter is com.ibm.db2.jcc.DB2Driver.

Table Name

The table or view to monitor for changes.

Remove Processed Rows

Select to remove all previously processed table rows before the next poll attempt. This cleanup is done when Iterator State is persisted.

Iterator State Key

Specifies the name of the parameter that stores the current synchronization state in the User Property Store of the IBM TDI. This must be a unique name for all parameters stored in one instance of the IBM TDI User Property Store.

The Delete button will delete this state information from the User Property Store.

Start At

This parameter is only taken into consideration if the Iterator State Key is not found in the property store or is left blank. It indicates the position of the record in the "change table" from which the connector will start reading entries. The parameter accepts values between 1 and EOD (End Of Data - the number of the last record in the "change table"). If the provided input value for the parameter is not valid, an appropriate exception will be thrown at runtime.

State Key Persistence

Governs the method used for saving the Connector's state to the System Store. The default and recommended setting is End of Cycle, and choices are:

After Read

Updates the System Store when you read an entry from the RDBMS Server change log, before you continue with the rest of the AssemblyLine.

End of Cycle

Updates the System Store when all Connectors and other components in the AssemblyLine have been evaluated and executed.

Manual

Switches off the automatic updating of the System Store with this Connector's state information; instead, we will need to save the state by manually calling the RDBMS Change Detection Connector's saveStateKey() method, somewhere in your AssemblyLine.

Sleep Interval

Specifies the time (in seconds) that IBM TDI waits between polls of the change table.

Timeout

Specifies the time (in seconds) to wait for new changes. A value of 0 (zero) causes the Connector to wait indefinitely.

Commit

Controls when database transactions are committed. Options are:

• After every database operation

• On Connector close

• Manual

Manual means user must call commit().

Detailed Log

If this parameter is checked, more detailed log messages are generated.

Change table format

This example change table captures the changes from a table containing the fields NAME and EMAIL. Elements in bold are common for all Changelog table. The syntax for this example is for Oracle.

IBMSNAP_COMMITSEQ is used as our changelog-nr.
IBMSNAP_OPERATION takes on of the values I (Insert), U (Updated)  or D (Deleted).
CREATE TABLE "SYSTEM"."CCDCHANGELOG"
(
IBMSNAP_COMMITSEQ   RAW(10)   NOT NULL, IBMSNAP_INTENTSEQ   RAW(10)   NOT NULL, IBMSNAP_OPERATION   CHAR(1)   NOT NULL, IBMSNAP_LOGMARKER   DATE      NOT NULL, NAME   VARCHAR2 ( 80 )  NOT NULL, EMAIL   VARCHAR2 ( 80 ) 
)#

The RDBMS Change Detection Connector does not work if the ibmsnap_commitseq column name used internally in the connector does not match exactly with the actual column in the database. This is true only when case-sensitivity is turned on for data objects in the Database the RDBMS Change Detection Connector is iterating on.

To handle this the column name is externalized as a connector configuration parameter. This provides the DBA an easy way to set ibmsnap_commitseq with the same case as used in his Database table. However, this parameter is not visible in connector config tab. To configure this parameter, we will have to set this manually in the before initialize hooks of the RDBMS Change Detection Connector. This will enable multiple RDBMS Change Detection Connectors to have their own copy of the column name value set for the change table the connector iterates on. For example,

myConn.connector.setParam("rdbms.chlog.col","IBMSNAP_COMMITSEQ");

sets the name of the ibmsnap_commitseq column to literally, IBMSNAP_COMMITSEQ. The default is lowercase otherwise.

Creating change tables in DB2

The following example creates triggers in a DB2 database to maintain the change table as described previous:

connect to our_username

drop table email
drop table ccdemail

create table email ( \
  name varchar(80), \
  email varchar(80) \
)

create table ccdemail ( \
  ibmsnap_commitseq integer, \
  ibmsnap_intentseq integer, \
  ibmsnap_logmarker date, \
  ibmsnap_operation char, \
  name varchar(80), \
  email varchar(80) \
)

drop sequence ccdemail_seq
create sequence ccdemail_seq

create trigger t_email_ins after insert on email referencing new as n \
  for each row mode db2sql \
     INSERT INTO your_username.ccdemail VALUES (nextval for ccdemail_seq, 0, 
      CURRENT_DATE, 'I', n.name, n.email )

create trigger t_email_del after delete on email referencing old as n \
  for each row mode db2sql \
     INSERT INTO your_username.ccdemail VALUES (nextval for ccdemail_seq, 0, 
      CURRENT_DATE, 'D', n.name, n.email )

create trigger t_email_upd after update on email referencing new as n \
  for each row mode db2sql \
     INSERT INTO your_username.ccdemail VALUES (nextval for ccdemail_seq, 0, 
      CURRENT_DATE, 'U', n.name, n.email )

Creating change tables in Oracle

Given that your username is "ORAID", then this (example) change table will capture the changes from a table containing the fields NAME and EMAIL. Boldfaced elements are common for all change tables. Bold faced entries are extra control information that will end up as Entry properties.

-- create source email table in Oracle. 
---This will be the table that the RDBMS Change Detection Connector will detect changes on.
CREATE TABLE ORAID.EMAIL
(
 NAME VARCHAR2(80),  EMAIL VARCHAR2(80)
); 
-- Sequence generators used for Intentseq and commitseq
CREATE SEQUENCE ORAID.SGENERATOR001
MINVALUE 100 INCREMENT BY 1 ORDER;

CREATE SEQUENCE ORAID.SGENERATOR002
MINVALUE 100 INCREMENT BY 1 ORDER;

-- create change table and index for email table
CREATE TABLE ORAID.CCDEMAIL
(
 IBMSNAP_COMMITSEQ   RAW(10)   NULL,  IBMSNAP_INTENTSEQ   RAW(10)   NOT NULL,  IBMSNAP_OPERATION   CHAR(1)   NOT NULL,  IBMSNAP_LOGMARKER   DATE      NOT NULL,  NAME VARCHAR2( 80 ), EMAIL VARCHAR2( 80 )
);

CREATE UNIQUE INDEX ORAID.IXCCDEMAIL ON ORAID.CCDEMAIL
(
IBMSNAP_INTENTSEQ
);

-- create TRIGGER to capture INSERTs into email
CREATE TRIGGER  ORAID.EMAIL_INS_TRIG
AFTER INSERT ON ORAID.EMAIL
FOR EACH ROW BEGIN INSERT INTO ORAID.CCDEMAIL
( NAME,   EMAIL,   IBMSNAP_COMMITSEQ,   IBMSNAP_INTENTSEQ,   IBMSNAP_OPERATION,   IBMSNAP_LOGMARKER )
 VALUES (
  :NEW.NAME,   :NEW.EMAIL,   LPAD(TO_CHAR(ORAID.SGENERATOR001.NEXTVAL),20,'0'),   LPAD(TO_CHAR(ORAID.SGENERATOR002.NEXTVAL),20,'0'),   'I',   SYSDATE);END;


-- create TRIGGER to capture DELETE ops on email
CREATE TRIGGER  ORAID.EMAIL_DEL_TRIG
AFTER DELETE ON ORAID.EMAIL
FOR EACH ROW BEGIN INSERT INTO ORAID.CCDEMAIL
( NAME,   EMAIL,   IBMSNAP_COMMITSEQ,   IBMSNAP_INTENTSEQ,   IBMSNAP_OPERATION,   IBMSNAP_LOGMARKER)
 VALUES 
( :OLD.NAME,   :OLD.EMAIL,   LPAD(TO_CHAR(ORAID.SGENERATOR001.NEXTVAL),20,'0'),   LPAD(TO_CHAR(ORAID.SGENERATOR002.NEXTVAL),20,'0'),   'D',   SYSDATE);END;


-- create TRIGGER to capture UPDATEs on email
CREATE TRIGGER  ORAID.EMAIL_UPD_TRIG
AFTER UPDATE ON ORAID.EMAIL
FOR EACH ROW BEGIN INSERT INTO ORAID.CCDEMAIL
( NAME,   EMAIL,   IBMSNAP_COMMITSEQ,   IBMSNAP_INTENTSEQ,   IBMSNAP_OPERATION,   IBMSNAP_LOGMARKER )
 VALUES (
  :NEW.NAME,   :NEW.EMAIL,   LPAD(TO_CHAR(ORAID.SGENERATOR001.NEXTVAL),20,'0'),   LPAD(TO_CHAR(ORAID.SGENERATOR002.NEXTVAL),20,'0'),   'U',   SYSDATE);END;

Creating change table and triggers in MS SQL

-- Source table msid.email. 
-- This will be the table that the RDBMS Change Detection Connector will detect changes on.
CREATE TABLE msid.email
(
 NAME   VARCHAR (80),  EMAIL   VARCHAR (80)
 );

-- CCD table to capture changes. The RDBMS Change Detection Connector uses the CCD table to capture
-- all the changes in the source table. This table needs to be created in the following format.
CREATE TABLE msid.ccdemail
(
 IBMSNAP_MSTMSTMP timestamp, 
 IBMSNAP_COMMITSEQ   BINARY(10)   NOT NULL,  IBMSNAP_INTENTSEQ   BINARY(10)   NOT NULL,  IBMSNAP_OPERATION   CHAR(1)      NOT NULL,  IBMSNAP_LOGMARKER   DATETIME     NOT NULL,  NAME   VARCHAR (80),  EMAIL   VARCHAR (80) 
);

You also need to create triggers to capture the insert, update and delete operations performed on the email table.

CREATE TRIGGER  msid.email_ins_trig ON msid.email
FOR INSERT AS
BEGIN
 INSERT INTO msid.ccdemail
(NAME,  EMAIL,  IBMSNAP_COMMITSEQ,  IBMSNAP_INTENTSEQ,  IBMSNAP_OPERATION,  IBMSNAP_LOGMARKER )
 SELECT
 NAME,  EMAIL,   @@DBTS,  @@DBTS,  'I',  GETDATE() FROM inserted
END;

: @@DBTS returns the value of the current timestamp data type for the current database. This timestamp is guaranteed to be unique in the database.

-- creating DELETE trigger to capture delete operations on email table
CREATE TRIGGER  msid.email_del_trig ON msid.email
FOR DELETE AS 
BEGIN
 INSERT INTO msid.ccdemail
(
 NAME,  EMAIL,  IBMSNAP_COMMITSEQ,  IBMSNAP_INTENTSEQ,  IBMSNAP_OPERATION,  IBMSNAP_LOGMARKER
)
 SELECT
 NAME,  EMAIL,  @@DBTS,  @@DBTS,  'D',  GETDATE() FROM deleted
END;#

-- creating UPDATE trigger to capture update operations on email table
CREATE TRIGGER  msid.email_upd_trig ON msid.email
FOR UPDATE AS 
BEGIN
 INSERT INTO msid.ccdemail
(
 NAME,  EMAIL,  IBMSNAP_COMMITSEQ,  IBMSNAP_INTENTSEQ,  IBMSNAP_OPERATION,  IBMSNAP_LOGMARKER
)
 SELECT
 NAME,  EMAIL,  @@DBTS,  @@DBTS,  'U',  GETDATE() FROM updated
END;

Creating change table and triggers in Informix

-- Create Source table infxid.email. This will be the table that the RDBMS Change Detection Connector
-- will detect changes on.
CREATE TABLE infxid.email
(
NAME VARCHAR(80), EMAIL VARCHAR(80)
);

-- create ccdemail table to capture DML operations on email table
CREATE TABLE infxid.ccdemail
(
IBMSNAP_COMMITSEQ   CHAR(10)   NOT NULL, 
IBMSNAP_INTENTSEQ   CHAR(10) NOT NULL, 
IBMSNAP_OPERATION   CHAR(1)   NOT NULL, 
IBMSNAP_LOGMARKER   DATETIME YEAR TO FRACTION(5) NOT NULL, 
NAME   VARCHAR(80), 
EMAIL   VARCHAR(80)
);

--Create sequence generators
CREATE SEQUENCE infxid.SG1
MINVALUE 100 INCREMENT BY 1;
CREATE SEQUENCE infxid.SG2
MINVALUE 100 INCREMENT BY 1;

-- procedure to capture INSERTs into email table
CREATE PROCEDURE infxid.email_ins_proc
( 
NNAME  VARCHAR(80), 
  
NEMAIL  VARCHAR(80)
)
 
DEFINE VARHEX CHAR(256);
 
 INSERT INTO infxid.ccdemail
(NAME,  EMAIL,  IBMSNAP_COMMITSEQ,  IBMSNAP_INTENTSEQ,  IBMSNAP_OPERATION,  IBMSNAP_LOGMARKER )
 VALUES 
(NNAME,  NEMAIL,  infxid.SG1.NEXTVAL,  infxid.SG2.NEXTVAL,  'I',  CURRENT YEAR TO FRACTION(5));END PROCEDURE;

-- now create the trigger for INSERTs into ccdemail
CREATE TRIGGER  infxid.email_ins_trig
 INSERT ON infxid.email
 REFERENCING NEW AS NEW FOR EACH ROW( EXECUTE PROCEDURE 
 infxid.email_ins_proc
( NEW.NAME,   NEW.EMAIL
) );

-- create procedure to capture DELETEs on email table
CREATE PROCEDURE infxid.email_del_proc
( 
 ONAME  VARCHAR(80),  OEMAIL  VARCHAR(80)
 );

 INSERT INTO infxid.ccdemail
(NAME,  EMAIL,  IBMSNAP_COMMITSEQ,  IBMSNAP_INTENTSEQ,  IBMSNAP_OPERATION,  IBMSNAP_LOGMARKER )
 VALUES 
(ONAME,  OEMAIL,  infxid.SG1.NEXTVAL,  infxid.SG2.NEXTVAL,  'D',  CURRENT YEAR TO FRACTION(5));END PROCEDURE;

-- create DELETE trigger
CREATE TRIGGER  infxid.email_del_trig
 DELETE ON infxid.email
 REFERENCING OLD AS OLD FOR EACH ROW( EXECUTE PROCEDURE 
 infxid.email_del_proc
(OLD.NAME,  OLD.EMAIL
) );

-- create PROCEDURE to capture updates
CREATE PROCEDURE infxid.email_upd_proc
( 
 NNAME  VARCHAR(80),  NEMAIL  VARCHAR(80)
);
 INSERT INTO infxid.ccdemail
(NAME, 
 EMAIL, 
 IBMSNAP_COMMITSEQ,  IBMSNAP_INTENTSEQ,  IBMSNAP_OPERATION,  IBMSNAP_LOGMARKER)
 VALUES 
(NNAME,  NEMAIL,  infxid.SG1.NEXTVAL,  infxid.SG2.NEXTVAL,  'U',  CURRENT YEAR TO FRACTION(5));END PROCEDURE;

-- create TRIGGER to capture UPDATES
CREATE TRIGGER  infxid.email_upd_trig
 UPDATE ON infxid.email
 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW( EXECUTE PROCEDURE 
 infxid.email_upd_proc
(NEW.NAME,  NEW.EMAIL
 ) );

Creating change table and triggers for SYBASE

-- Create Source table sybid.email. 
-- This will be the table that the RDBMS Change Detection Connector will detect changes on.
CREATE TABLE sybid.EMAIL
(
 NAME   VARCHAR (80),  EMAIL   VARCHAR (80)
)

-- Create CCD table to captures changes on email table
CREATE TABLE sybid.CCDEMAIL
(
 IBMSNAP_TMSTMP TIMESTAMP,  IBMSNAP_COMMITSEQ  NUMERIC(10)   IDENTITY,  IBMSNAP_INTENTSEQ   BINARY(10)   NOT NULL,  IBMSNAP_OPERATION   CHAR(1)      NOT NULL,  IBMSNAP_LOGMARKER   DATETIME     NOT NULL,  NAME   VARCHAR(80),  EMAIL   VARCHAR(80) 
)

-- Create TRIGGER to capture INSERTs on email table
CREATE TRIGGER  sybid.EMAIL_INS_TRIG ON sybid.EMAIL
FOR INSERT AS
BEGIN
 INSERT INTO sybid.CCDEMAIL
(NAME,  EMAIL,  IBMSNAP_INTENTSEQ,  IBMSNAP_OPERATION,  IBMSNAP_LOGMARKER )
 SELECT
 NAME,  EMAIL, @@DBTS,  'I',  GETDATE() FROM inserted
END

NOTE: @@DBTS is a special database variable that yields the next database timestamp value 

-- create TRIGGER to captures DELETE ops on EMAIL table
CREATE TRIGGER  sybid.EMAIL_DEL_TRIG ON sybid.EMAIL
FOR DELETE AS 
BEGIN
 INSERT INTO sybid.CCDEMAIL
(
 NAME,  EMAIL,  IBMSNAP_INTENTSEQ,  IBMSNAP_OPERATION,  IBMSNAP_LOGMARKER
)
 SELECT
 NAME,  EMAIL,  @@DBTS,  'D',  GETDATE() FROM deleted
END

-- create TRIGGER to capture UPDATEs on email
CREATE TRIGGER  sybid.EMAIL_UPD_TRIG ON sybid.EMAIL
FOR UPDATE AS 
BEGIN
  DECLARE @COUNTER INT 
  SELECT @COUNTER=COUNT(*) FROM deleted 
  IF @COUNTER>1 
  BEGIN 
  DECLARE @NAME  VARCHAR ( 80 )
  DECLARE @EMAIL  VARCHAR ( 80 )
  DECLARE insertedrows CURSOR FOR SELECT * FROM inserted 
  OPEN insertedrows 
  WHILE 1=1 BEGIN 
  FETCH insertedrows INTO 
  @NAME, 
  @EMAIL 
   IF @@fetch_status<>0 BREAK 
  ELSE INSERT INTO sybid.CCDEMAIL
 (
  NAME,   EMAIL,   IBMSNAP_INTENTSEQ,   IBMSNAP_OPERATION,   IBMSNAP_LOGMARKER
 )
 VALUES
 (
  @NAME,   @EMAIL,   @@DBTS,  'U',  GETDATE()
 )
 END
  DEALLOCATE insertedrows 
 END ELSE INSERT INTO sybid.CCDEMAIL(
  NAME,   EMAIL,   IBMSNAP_INTENTSEQ,   IBMSNAP_OPERATION,   IBMSNAP_LOGMARKER
 )
 SELECT
  I.NAME,   I.EMAIL,   @@DBTS,  'U',  GETDATE() FROM inserted I
 END

Example

An example is provided under the directory TDI_install_dir/examples/RDBMS. The example demonstrates the abilities of the RDBMS Change Detection Connector to detect changes over a table in a remote DataBase. The current example is designed to work with IBM DB2 only.