1

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.

JoshMc
  • 10,239
  • 2
  • 19
  • 38
Lakshmanan Chidambaram
  • 133
  • 11
  • I came to know about the following content: Database Management Systems log changes. Depending on the operation (Insert/Update/Delete), they log a "before image" and/or "after image" of the record or row. For an insert, there is only an "after image" of the record or row, because there is nothing "before" the insert. For a delete, there is only a "before image" of the record or row, because there is nothing "after" the delete. For an update, there is a "before image" and an "after image". Does this applies to both IBM IMS Hierarchal DB and IBM DB2 RDBMS is my question now? – Lakshmanan Chidambaram Apr 02 '18 at 15:22
  • yep the sql looks something like referencing old as O new as N. – danny117 Apr 02 '18 at 16:15
  • Could you please be a bit elaborative? in terms of how to get those values? May be one sample Query each to IMS and DB2 would help. Thanks. – Lakshmanan Chidambaram Apr 02 '18 at 16:28
  • Done. See below trigger referencing old and new. – danny117 Apr 03 '18 at 14:08

3 Answers3

0

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
Richard Tran
  • 438
  • 3
  • 10
  • "that it compiles from the DBMS log data" - The following URL specifies that a Before and After image of data is possible: https://network.informatica.com/thread/10271, however I'm yet to confirm with our DBAs. – Lakshmanan Chidambaram Apr 06 '18 at 10:15
0

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
danny117
  • 5,581
  • 1
  • 26
  • 35
  • Thanks for helping with this information. However, my specific doubt is to understand if a before and an after change data is available at the Database level on a CDC enabled IBM DB2 tables and IBM IMS segments. Not to work with triggers actually, but at a table / IMS segment level data capture for any change of data in those tables / segments. Seems like this URL suggests some idea (URL: https://network.informatica.com/thread/10271), however, I'm yet to confirm with our DBAs on the same. Thanks. – Lakshmanan Chidambaram Apr 06 '18 at 10:19
0

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.

Chandan Kumar
  • 2,617
  • 2
  • 19
  • 20