Join tables from 2 different databases

[madhonrougeuri]

Hi All,
I need to join 2 tables from DB2 and Oracle.Join should be performed on th DB2 side(ODBC) and put in the Oracle database.
Could somebody help me out.
Thanks,
Madhu

[dsxuserrio]

Put the data from DB2 and Oracle into two datasets. Then do a join.

[madhonrougeuri]

by datasets,do you mean to use PX?We dont have Enterprise edition.

[davidnemirovsky]

This may not be very efficient but it will work:

First design your job to grab the ORACLE table and export it out into a temporary DB2 table.

Code: Select all

OCI --> X --> DB2

Then perform your join in a DB2 stage and output your result set to an ORACLE stage.

Code: Select all

DB2 --> X --> OCI

[clshore]

Another option if the data set is not huge, is to use the Merge stage.
This stage reads flat files, and will perform various Left, Right, and Inner joins to create output datasets.
Query each DB, creating a flat file, then use the merge stage to join them.
Some people find ithe Merge difficult to set up, but I've had good results using it. One bug is that sometimes changes you make inside the stage are not preserved when you exit.
Performance isn't too bad, because under the covers, it sets up temporary hash files to do the dirty work.
I wish it was set up to use input streams from other transformations, etc.

Carter

[madhonrougeuri]

I have loaded oracle into a hash file and joined both databases in the Transformer stage.
I have left the account name to deafult.but when i try to run the job,I see an error"DSD.UVOpen Unable to open file xxxx"
Could somebdy help me out?
Thanks,
madhu

[sachinkc]

1. Did you specify the correct names in teh stages (Foolish, but obvious question)?
2. What kind of a join were you looking for? Just Inner join or ang Outer joins too?

- Sachin

I have loaded oracle into a hash file and joined both databases in the Transformer stage.
I have left the account name to deafult.but when i try to run the job,I see an error"DSD.UVOpen Unable to open file xxxx"
Could somebdy help me out?
Thanks,
madhu

[Sainath.Srinivasan]

You are trying to open a file which does not exist.

Check the path and name of the files in the job.

[sumitgulati]

If you load Oracle data into hash file and do a join in transformer stage then in case of multiple matches found the hash file will return the last match only.

In case you want all the rows to be returned use the ODBC stage. In the transformer properties > Input tab select the "Reference link with multi row result set" for the ODBC input link.

Regards,
-Sumit

[rajiivnb]

Hi sumit,
I use the Hash file for lookup but finding many rows with match values ,the same way when i use a odbc also i see many rows with matching values but it takes only last value what is the solution for this??.You gave some option In the transformer properties > Input tab select the "Reference link with multi row result set" for the ODBC input link. .....i am unable to follow this ..would you please explain me more on this.

Regards,
rajiivnb

[rajiivnb]

Thanks sumit i found them

[madhonrougeuri]

I dont see the reference link with multi row result set in my Trnsf properties??Any idea?

[manteena]

I dont see the reference link with multi row result set in my Trnsf properties??Any idea?

It is only avaliable if you use odbc stage to pull the data from the database

[rajiivnb]

Hi madhonrougeuri ,
ODBC stage pulling should be used as reference link .Get into transformer properties and input tab of the reference link at the bottom you can see the option.

Regards,
Rajiivnb

[chulett]

Actually, both the ODBC and UV stages can return 'multi row result sets'. FYI.