source_lookup not working as expected for IBM DB2 for LUW

[Sudharma]

Hi I am facing issue while using the source_lookup functionality, after configuring the follwing syntax(plus the syntax of specifing columns using :1 and :2) it is not getting replaced at the runtime.

Source : IBM DB2 for LUW
Target : Google AlloyDB for Postgres

Tried options

Option -1

source_lookup(10000,'SCHEMANAME','TABLENAME','COLUMN1','COLUMN_X=? and COLUMN_Y=?','$COLUMN_X, $COLUMN_Y')

Output-1

SELECT COLUMN1 FROM "SCHEMANAME"."TABLENAME" WHERE COLUMN_X=? and COLUMN_Y=?

Option -2

source_lookup(10000,'SCHEMANAME','TABLENAME','COLUMN1','COLUMN_X=:1 and COLUMN_Y=:2','$COLUMN_X, $COLUMN_Y')

Output-2

SELECT COLUMN1 FROM "SCHEMANAME"."TABLENAME" WHERE COLUMN_X=:1 and COLUMN_Y=:2

[john_wang]

Hello @Sudharma ,

I'm not sure what issue you're encountering, but multiple input parameters in source_lookup() are working fine in my DB2 LUW lab environment. Here's a sample for reference:

The expression is: source_lookup(100000,'JOHNW','TESTLOOKUP2','NOTES','ID1=? and ID2=?', $ID1, $ID2)

Could you please share the error message you're seeing? We'd be happy to help investigate further.

Regards,

John.

[Sudharma]

Table 'SCHEMA'.'TABLE' (subtask 1 thread 1) is suspended. Failed to init column calculation expression

This is the error which I am currently facing

[john_wang]

Hello @Sudharma ,

Thanks for the update.

Please set SOURCE_CAPTURE/SOURCE_UNLOAD to Verbose, re-run the task, then check the task log file to see what's the expression delivered to DB2 LUW Server to understand what's the issue.

If you require assistance, please open a support ticket and attach the diagnostic packages. Our support team will be more than happy to assist you.

Regards,

John.

[Sudharma]

Thank you for the previous assistance. I have a follow-up question regarding Qlik's 'source lookup' functionality and its performance implications during data loading.

Could you please explain how Qlik handles these lookups? Specifically:

• Does Qlik perform a separate database call for each individual row that requires a lookup?
• When multiple columns use source lookups, referencing either the same or different source tables, how does Qlik manage these lookups for performance? Does it optimize or batch them?
• For instance, if we have 5 derived columns using lookups, and 2 of them pull from the same source table, would Qlik fetch the entire necessary data from that source table into memory/cache once and use that for both lookups, or how does that process typically work?

Understanding this from a performance standpoint is crucial for us to plan our data loading activities effectively.

If you have any relevant documentation on Qlik's lookup, mapping, or join performance behavior, could you please share it. That would be really helpful.

Thanks in Advance

[john_wang]

Hello @Sudharma ,

>> Does Qlik perform a separate database call for each individual row that requires a lookup?

Yes, the call is for each individual row. However Qlik Replicate cache the return values to improve performance. The TTL can be configured.

>> When multiple columns use source lookups, referencing either the same or different source tables, how does Qlik manage these lookups for performance? Does it optimize or batch them?

Qlik Replicate try to optimize them. You may enable the logging to see the source lookup queries.

>> For instance, if we have 5 derived columns using lookups, and 2 of them pull from the same source table, would Qlik fetch the entire necessary data from that source table into memory/cache once and use that for both lookups, or how does that process typically work?

Personally I do not think Qlik Replicate fetch the entire data, but row by row.

BTW, the source lookup and target lookup functionalities impact the replication performance as Replicate has to deviery queries to source/target database to fetch specific rows again.

Hope this helps.

John.

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[Dana_Baldwin]

Hi @Sudharma 

The logging to increase to see the lookup queries is Source_Capture. This component is involved with reading from the source endpoint transaction log during the change processing phase of the task.

Thanks,

Dana

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download