How to use join in qlik replicate source for IBM iSeries tables

[dndiaye01]

I want to use join in qlik replicate full load task to get data from a table using join since the main table does not date column to get a limited amount of records

[john_wang]

Hello @dndiaye01 ,

There are options you can join multiple source tables:

1. Use SOURCE_LOOKUP data enrichment function.

    You can get more detailed usage and sample from User Guide.

2. Use VIEW (LF).

    You can create VIEW in DB400 to join the relational tables and get your desired layout (filter the rows by date column etc), then access the VIEW to replicate the records to target side.

    In this usage please take note that (1) VIEW support Full Load ONLY, no CDC function support as view's change will not record into Journal;  (2) Please use generic ODBC to access DB400 rather than Native DB400 driver in Replicate Endpoint setting; (3) It must be "Full Load ONLY" type (turn off CDC) in Replicate Task setting.

Personally I'd like suggest you use option 2 because it's easy to maintain (in both Task Design and Runtime managements), and the performance is better than option 1.

Hope this helps.

Regards,

John.

[dndiaye01]

Hi John,

I was able to join tables in Qlik Replicate Full Load putting a join condition on Fullload Passthru Filter

Here is my condition below:

first Column in the original table: Col_nm > value) AND EXISTS
(SELECT 1
FROM Lookup table  B
WHERE original_table.Join_Col_nm = B.Join_Col_nm
AND B.Second Colu_nm >= value
AND 1=1) WITH UR -- #

Example 

FRBIL# > 99999) AND EXISTS
(SELECT 1
FROM WELIB.TSPBILL B
WHERE WELIB.TSPTRPCM.FRBIL# = B.FRBIL#
AND B.BILYY >= 2021
AND 1=1) WITH UR -- #

The source Lookup did not work for me and the view. 

[john_wang]

Hello @dndiaye01 ,

A recent product hardening change to Replicate has the passthrough feature turned off by default. When disabled, using passthrough filters will cause the replication task to fail when trying to reload a table for which a passthrough filter is defined. While the passthrough filter feature can be re-enabled, it is not recommended if the user configured for the source endpoint has update permissions on the source database. An alternative approach is to use a view in the source database to implement the filter.

Regards,

John.

[dndiaye01]

Thanks John,

I will look into it further and let you know for more advices and suggestions.