Join tables from 2 different databases
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 24
- Joined: Sun Jan 23, 2005 3:36 pm
Join tables from 2 different databases
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
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
-
- Participant
- Posts: 82
- Joined: Thu Dec 02, 2004 10:27 pm
- Location: INDIA
-
- Participant
- Posts: 24
- Joined: Sun Jan 23, 2005 3:36 pm
-
- Participant
- Posts: 85
- Joined: Fri Jun 04, 2004 2:30 am
- Location: Melbourne, Australia
- Contact:
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.
Then perform your join in a DB2 stage and output your result set to an ORACLE stage.
First design your job to grab the ORACLE table and export it out into a temporary DB2 table.
Code: Select all
OCI --> X --> DB2
Code: Select all
DB2 --> X --> OCI
Cheers,
Dave Nemirovsky
Dave Nemirovsky
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
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
-
- Participant
- Posts: 24
- Joined: Sun Jan 23, 2005 3:36 pm
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
2. What kind of a join were you looking for? Just Inner join or ang Outer joins too?
- Sachin
madhonrougeuri wrote: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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 197
- Joined: Mon Feb 17, 2003 11:20 pm
- Location: India
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
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
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
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
-
- Participant
- Posts: 24
- Joined: Sun Jan 23, 2005 3:36 pm