Can we use DB2 materialised Views as a source with Qlik Replicate

[adbdkb]

In the documentation, I found some information about Oracle materialised views and what can or cannot be replicated, but did not find any information about DB2 materialised views.  Is there any documentation on limitations on DB2 materialised views?

Also, a different question - not sure if it is related to to above part or not so asking here.  Let me know, if  I should open another question.

Can we use either -  a SQL query with multi-table joins  or  a  DB2 stored procedure as source for replication?

Thank you

[john_wang · Accepted solution]

Hello @adbdkb ,

Not very sure what's the DB2 platform, DB2i, DB2z, or DB2 LUW. However, you know Replicate is a log-based CDC product (only few exceptions), if the source object (eg VIEW ) changes cannot be recorded in the transaction log (certainly it's DB itself design), then it's not supported by CDC in Replicate of course.

For the VIEW replication, only Full Load mode is supported.

Regarding the multi-tables joins, the most easy way is using VIEW. Replicate does not support stored procedure as source.

Hope this helps.

Regards,

John.

[adbdkb]

It is DB2z.  Can you help me understand how this - if the source object (eg VIEW ) changes cannot be recorded in the transaction log (certainly it's DB itself design),  -  is defined for DB2z ?

Thanks

[john_wang]

Hello @adbdkb ,

In general most of the relational DBs view's data changes are not recorded in transaction log (instead, the data changes of a view is based on the backend real table(s)). It's same in DB2z.

In other words, you cannot UPDATE a VIEW records, you can do that by UPDATE the backend tables records then the VIEW data looks like being updated. Not sure if this is what your concern.

Regards,

John.

[Michael_Litz · Accepted solution]

Hi,

I wanted to add to John's post that if you only needed a couple of fields from a different table in the task target table replicate does have the capability to a source lookup function which could be added to the target table in a new field.

This lookup will bring back the value of a single field from the specified table into the new field in the target table.
Please refer to the user guide under transformations. Here is an example of how it looks.

source_lookup(1, 'DBO', C988888042,'C988888042txt','C1=?', $C1)
(Duration, Schema, Table, Field, TableIndex(Where), SQLite field reference

Thanks,
Michael

[Michael_Litz]

Hi,

Adding a link the Qlik community where I found this article on source lookup:

https://community.qlik.com/t5/Knowledge/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170

Thanks,
Michael

[adbdkb]

@john_wang  and @Michael_Litz - Thank you both for helping me with the details.   Since I can accept only one answer, I will accept the first answer from John

[john_wang]

Hello @adbdkb 

Thank you. We are glad for help always. Thank you @Michael_Litz  for your insight.

Regards,

John.