DB2 Replication – Near Real-time

In a world where everything has become real-time, mainframe is fast catching up to the term near real-time. It was not long ago, that customers were asking for real-time information. This did have an impact on mainframe community too, Db2 or Adabas, the word was out to see if a replication mechanism can be developed and if such a replication is a viable option. Software AG (developers of xml & Adabas/natural) was able to size up the opportunity quickly by creating the new tool called Adabas replicator driven on PLOG. Db2 wasn’t far behind either.

The only cliché was that Adabas Replicator was yet another tool (well let us say you have to get a licensing cost to it) to be marketed by the software giant, while db2 provided a solution called event publishing for converting committed source changes into messages in an XML format and publishing those messages to applications such as message brokers by leveraging the system files within db2. Before I proceed further, I just want to pause and say this – in both the instances the replication is only near real-time and it really depends on the target systems, on how fast they can process the messages from the queue.

A simple configuration of event publishing looks like this:

DB2 MQ setup table : SYSIBM.MQSERVICE_TABLE

DB2 ID used by db2 to access MQ = DB2MSTR

Sample DDL Set up as follows:

CREATE TRIGGER “REF_DATA_TRIGGER”

AFTER INSERT ON “REF_DATA”

REFERENCING NEW AS REF_NEW

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

SELECT DB2MQ.MQSEND

(

'MYSERVICE',

CAST(‘<REFDATA>’||XML2CLOB(

XMLCONCAT(

XMLELEMENT(NAME “FIELD1”,REF_NEW.FIELD_1),

XMLELEMENT(NAME “FIELD2”, REF_NEW.FIELD_2),

XMLELEMENT(NAME “FIELD3”, REF_NEW.FIELD_3),

XMLELEMENT(NAME “FIELD4”, REF_NEW.FIELD_4),

XMLELEMENT(NAME “FIELD5”, REF_NEW.FIELD_5),

XMLELEMENT(NAME “FIELD6”, REF_NEW.FIELD_6)

))||’<REFDATA>’ AS VARCHAR(2000)))

)  

FROM

SYSIBM.SYSDUMMY1

Special thanks to my colleague Jeegar Doshi who helped me compile the information on DB2 replication.