Provide Complex filter condition in replication task

[rakeshraghav29]

Hello,

I am trying to migrate data via full load task from db2 z/os to PostgreSQL and want to know how to provide filter condition like below:

Table to be replicate: employee

Filter condition: select * from employee where deptid in (select deptid from department where region = 'US');

I don't see any option to build a complex filter like above directly in replication task, except I build a view in source with this condition.

Thanks,

Rakesh

[john_wang · Accepted solution]

Hello Rakesh @rakeshraghav29 ,

Welcome to Qlik Community forum and thanks for reaching out here!

Except the option of creation VIEW in DB2z, We might use the "Fullload Passthru Filter" for such complex filter, this is an example how to use it.

Hope this helps.

John.

[Heinvandenheuvel · Accepted solution]

Best option, for full-load only,  is really the view as you suggest.

Next best a push-thru filter as per @john_wang 

Now if this filtering also needs to be in place for CDC, then there is the 'data enrichment' SOURCE_LOOKUP function which can help you create a filter expression.

This is executed row by row, but with caching option which should alleviate overhead a lot in your case (regions in the US are likely very stable, perhaps occasionally updated overnight.

source_lookup(TTL,SCHM,TBL,EXP,COND,COND_PARAMS)

• source_lookup(3600,'your_schema','department','count(1)','deptid = :1 and region = 'US'',$regionid)
• TTL would be 3600 (an hour)
• Schema
• TBL = department
• EXP='count(1)'
• COND = deptid = :1 and region = 'US'
• COND_PARAMS = $regionid

Hein