process db2 tables many-to-many relationship

DStageNewbie · Post by **DStageNewbie** » Wed Jan 20, 2010 1:13 pm

We have a driver table (db2) containing an old part nbr and a new part nbr (a cross-reference). On this table, the old part nbr is not unique, so we could have two rows on the table with the same old part nbr, but two different new part nbrs. On a second table, we want to match on the old part nbr and build a new row transforming the old part nbr with the new part nbr. So when we are done, we will have rows with the old nbr and new rows with the new part nbr. How do we process this many-to-many relationship? Is there any good documentation out there that would help us understand how to process this situation? I am a rookie, so please be specific. thanks!

ray.wurlod · Post by **ray.wurlod** » Wed Jan 20, 2010 3:08 pm

Start by thinking about how you'd do it in SQL. A UNION, maybe? Then, essentially, replicate that methodology in DataStage. Probably the easiest is to have DataStage execute the SQL you got in the first step to perform the extraction.

DStageNewbie · Post by **DStageNewbie** » Thu Jan 21, 2010 7:07 am

ray.wurlod wrote:Start by thinking about how you'd do it in SQL. A UNION, maybe? Then, essentially, replicate that methodology in DataStage. Probably the easiest is to have DataStage execute the SQL you got in the fi ...

I started out that route, but then realized that the two tables are on different platforms/servers and I could not figure out away to join them in one stage. Is that possible?

chulett · Post by **chulett** » Thu Jan 21, 2010 9:10 am

One of the tables will be your input stream, the other a reference lookup. If you need multiple rows from the lookup, you'll need to use ODBC or UV rather than the Hashed File stage for the reference lookup.