How can we join the tables from diperate source databases

irajasekharhexa · Post by **irajasekharhexa** » Mon Jun 05, 2006 8:17 am

Hi all,

Till now we have developed server jobs in which we used DRS Stages as the source stage, Is there any method to join the tables which are taking from diffrent source databases. if so what is the method.

Can any body tell this pls.

DSguru2B · Post by **DSguru2B** » Mon Jun 05, 2006 8:19 am

Depends upon the how many rows we are looking at. If not much then unload the tables into hashed files and proceed from there. If the count is large then you might want to look into using staging/temp tables in the same database schema and join the tables by passing a sql join through DataStage.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 05, 2006 4:08 pm

Provided you only want inner or left outer joins, then you can use the conventional lookup mechanism in DataStage, using a Transformer stage. The stream link can come from one data source, the reference input link from the other. Obviously you need to generate a suitable reference key expression, one which will match.

If going to the second data source on the reference input link is too slow you can, as others suggested, load rows from there into a hashed file prior to running the "join". Only load those rows and columns that you will actually need, to conserve memory (and disk) resources.

irajasekharhexa · Post by **irajasekharhexa** » Mon Jun 05, 2006 11:41 pm

ray.wurlod wrote:Provided you only want inner or left outer joins, then you can use the conventional lookup mechanism in DataStage, using a Transformer stage. The stream link can come from one data source, the reference input link from the other. Obviously you need to generate a suitable reference key expression, one which will match.

If going to the second data source on the reference input link is too slow you can, as others suggested, load rows from there into a hashed file prior to running the "join". Only load those rows and columns that you will actually need, to conserve memory (and disk) resources.

Thanks lot Ray and DSguru