Sample - Moving DB2 Changed Data from Q Replication to Files

A lesser known feature of the IBM InfoSphere Data Replication (IIDR) product is a Q Replication function called event publishing (EP). EP publishes DB2 changed data in a well-known format such as comma separated values. This gives applications an alternative way to consume changed data. This post provides an example of how changed data can be moved from queues to files.

The Event Publishing Format

EP captures data from the DB2 transaction log and writes it to a Websphere MQ queue. It supports 2 formats:

• A delimited format where transaction information - table row, column values, etc. - is separated by delimiters, most often commas.
• An XML format where transaction information is enclosed in self-described tags in an XML document.

The picture below shows a EP comma-delimited transaction message that includes a SQL insert ('ISRT') and 2 SQL updates ('REPL'):

[Picture no longer present]

Sample Event Consumer

A sample event consumer is now available here on developerWorks. It is a Java app and consumes EP messages that are published in a delimited format. The downloads for this are:

• An overview (pdf): Moving DB2 Changed Data from MQ to Files with Q Replication
• The code (zip): asnmsg2file.zip

Final Note on Event Consumers

Queues are not the only way of publishing data. It can also be published to tables.

For example, CCD, or Consistent Change Data, tables hold changed data in a format easily consumed by applications through SQL. A popular use-case is the multi-tier distribution scenario where tier-1 is the production system, tier-2 is a mid-tier consolidation server with CCD tables populated with tier-1 changed data (no Capture process is running on tier-2) and tier-3 servers are regional servers where Apply processes pull the appropriate changed data from tier-2 CCD tables.

Event publishing combined with specialized event consumers delivers simple solutions for integration scenarios.