IBM DB2 and IBM IMS Change Data Capture Capabilities

Question

I'd like to understand if the CDC enabled IBM IMS segments and IBM DB2 table sources would be able to provide both the before and after snapshot change values (like the Oracle .OLD and .NEW values in trigger) so that both could be used for further processing.

Note:

1. We are supposed to retrieve these values through Informatica PowerExchange and process and push to targets.
2. As of now, we need to know would we be able to retrieve both before snapshot and after snapshot values from IBM DB2 and IBM IMS (.OLD and .NEW as in Oracle triggers - not an exact similar example, but mentioned just as an example to understand)

Any help is much appreciated, Thanks.

Answer 1

I don't believe CDC captures before data in its change messages that it compiles from the DBMS log data. It's main purpose is to issue the minimum number of commands needed to replicate the data from one database to another. You'll want to take a snapshot of your replica database prior to processing the change messages if you want to preserve the state of data such that you can query it.

Alternatively for Db2, it's probably easier to work with the temporal tables feature added in Db2 10 as that allows you to define what changes should drive a snapshot. You can then access the temporal data using a temporal SQL query.

SELECT … FROM…period specification

Answer 2

Example trigger with old and new referencing...

CREATE TRIGGER danny117                                      
        NO CASCADE BEFORE Update ON mylib.myfile  
      REFERENCING NEW AS N old as O                        

      FOR EACH ROW                                         
-- don't let the claim change and force upper case         
--just do something automatically on update blah... 
      BEGIN ATOMIC                                         
         SET N.claim = ucase(O.claim);                     
      END           

Answer 3

w.r.t PowerExchange 9.1.0 & 9.6:

Before snapshot data can't be processed via the powerexchange for DB2 database. Recently I worked on a migration project and I thought like the Oracle CDC which uses SCN numbers there should be something for db2 to start the logger from any desired point. But to my surprise Inforamtica global support confirmed that before snapshot data can't be captured by PowerExchange. They talk about materialize and de-materialize targets which was out of my knowledge at that time, later I found out they meant to export and import of history data.

Even if you have table with CDC enanbled, you can't capture the data before snapshot from PWX.

DB2 reads capture data from the DB2-logs which has a marking for the operation like U/I/D that's enough for PowerExchange to progress.