Adding DB2 Summary tables

[mkfox]

Can DB2 Summary tables be added to Source tasks?  We don't see that option in UI.

[john_wang]

Hello @mkfox ,

Thanks for reaching out to Qlik Community!

Qlik Replicate is log-based CDC product (with a few exceptions), so far if the object's changes cannot be recorded in transaction logs (TLOG in SQL Server, Redo LOG in Oracle, Journal in DB2i, BinLog in MySQL, OPLOG in MongoDB ...) then it's impossible to get the relevant changes from transactions logs. 

Not very sure what's the DB2 platform, is it LUW, AS400, or Mainframe. If the "DB2 Summary tables" are base tables and its changes record into transaction logs, then it's available for CDC. If its type is VIEW (view's changes are not recorded in DB2 transaction logs) then it's supported by Full Load ONLY task.

Hope this helps.

John.

[john_wang]

Hello @mkfox ,

I did more rearching here. If I understand correctly,  the summary table is a special type of a materialized query table (MQT), by default it's invisible to Qlik Replicate. We need to create a VIEW for the MQT, then it's available for Qlik Replicate.

There are many object types in DB2 SYSCAT.TABLES catalog view, Qlik Replicate only interesting T & V:

• T = Table (untyped)
• V = View (untyped)

Qlik Replicate does not care about S = Materialized query table by default, so far we need create a VIEW for the MQT, for example:

CREATE VIEW johnw.V_umqt_testmqt AS SELECT * FROM johnw.umqt_testmqt;

then it can be included in a Full Load ONLY task, like:

Where "johnw" is the schema name, the simple (base) table name is "TESTMQT", the summary table name is "umqt_testmqt" and the VIEW name is "V_umqt_testmqt". In Qlik Repliate, we can now see the simple table, and the VIEW (although the summary table is invisible). The data can be replicated by a Full Load ONLY task as well.

Besides the above workaround, we may simply replicate the simple (base) tables in Full Load and/or CDC task then build up the 'summary table' view in target side database, if possible.

Hope this helps.

John.