Join 2 db2 tables from different database

[KadetG]

Hello, All

I have 2 identical tables stored in different db2 database servers. I should be replicate data from first table to another. I need copy records only added after last replication. So.. In second table I have field with date and I need copy records from first table where date is greater.

In datastage I use two DB2/UDB API stages to connect to first and second db2 servers. How I can use SQL query from second table in Where clauses first table?

Thank you.

[ray.wurlod]

Welcome aboard.

Ideally you would perform this task entirely within DB2, and not involve DataStage at all. Table2 should be able to be made visible on the server for Table1, and thus could be used in the WHERE clause either as a join or (shudder!) in a correlated subquery.

Otherwise, in DataStage, you're moving data from Table1 to Table2 only if the row does not already exist in Table2. You could achieve this via a lookup failing against Table2. If Table2 is remote, consider pre-loading a hashed file with the keys from Table2.

[KadetG]

Thank you. But DB2 can`t join tables from different databases as I know. So. Can I use variables or parameters in Where clauses of the SQL request?

[sb_akarmarkar]

Hi,

After loading data from tablename1(Source) to tablename2(Target) job select maximum date from target and store it in hash file. Paramterize date variable in select clause like "Select columnname1... from tablename where date > Date Parameter" ... initial default date like 1/1/1900

Create sequence use routine UtilityHashLookup() in routine activity pass coming Date value from routine activity to Job activity - Data Parameter as job created for above....

I think this will help to slove your problem.

_____________________________________________________________
Anupam

[KadetG]

Thank you

But can I don`t use HashFile to store my date?

So.. I created parameter with list dates. This is dates which don`t exist in secont table. I receive this dates from my stage. Can I copy data from dataset to parameter directly? And then use it in LoopActivity and as parameter to other job.

[ray.wurlod]

With DB2-Connect you can expose tables from one database on another. You can then effect your joins. IBM Information Integrator is another way that you might access these data.

[KadetG]

Thenk you for replay. But I need use datastage for that task!

[baglasumit21]

Hello, All

I have 2 identical tables stored in different db2 database servers. I should be replicate data from first table to another. I need copy records only added after last replication. So.. In second table I have field with date and I need copy records from first table where date is greater.

In datastage I use two DB2/UDB API stages to connect to first and second db2 servers. How I can use SQL query from second table in Where clauses first table?

Thank you.

Try this
Just load a hash file with maximum date and a dummy column with '1' as derivation from second table and add a constraint in the transformer 'firsttable. date > secondtable.date'

[ray.wurlod]

Thenk you for replay. But I need use datastage for that task!

Why? Who said?

[vmcburney]

You can use the change capture stage to compare the source table to the target table and deliver just the changes (inserts, updates, deletes). You can truncate and reload the target table, if you use bulk load this should be quite fast.

If you are doing this for a lot of tables then the Replication Server on the IBM Information Server would do this most efficiently and it uses the same metadata repository and services layer as DataStage 8.