How to do Row Convention in DataStage?

nvkravi · Post by **nvkravi** » Tue Jul 15, 2003 11:38 pm

Hey All,

Im new to DataStage tool. Right now Im using DataStage 5.2 Client in Windows 2000. And DataStage Server also in Windows 2000 and Version 5.2. And Im using ORAOCI8 to both Source and Target sides. Backend is Oracle 9i.

I have a table in oracle src_one (Source table) and the availble columns are,

Name Type
-----------------
E_ID NOT NULL NUMBER(3)
E_ONE NOT NULL CHAR(1)
E_TWO NOT NULL CHAR(1)
E_THREE CHAR(1)

And my lookup table is lkp_one (Source side)
Name Null? Type
--------- -------- ----------------------------
L_CD NOT NULL CHAR(1)
L_ID NOT NULL NUMBER

trgt_three (Target table) contains,
Name Null? Type
-------- -------- ----------------------------
E_ID NOT NULL NUMBER(3)
E_S_ID NOT NULL CHAR(1)

The Lookup data is

L_CD L_ID
A 10
B 20
C 30
D 40

Here I have table One Lookup ORAOCI8 and then I have to check each value of E_ONE is available in L_CD (Lookup column),If it there retrieve a Lookup column L_ID, then Insert with a record in Target table.Similarly, E_TWO also have to check with same LOOKUP, If value is there in Lookup table, again retrieve Lookup column and then insert with a new record in Target table, and so on. So the Target table should come like the following way.....

E_ID E_VAL
100 10
100 20
100 30
200 20
200 10
200 30
.
.
.

So E_ONE (Source table) value is there in L_CD (in Lookup) only then Insert a new record with L_ID in Target table side. Similar way to E_TWO and E_THREE columns also, to insert a new record. (Here I have taken only 3 columns to check, But I have 20 columns in Source side to check with same Lookup)
Can anybody help me out the above Convention? Whether I have to any Buil-in Routines here or to write any User diefined Routines? How to use this concept in DataStage? Pls give your suggestions or openions?
Thanx in advance.

Ravi

ray.wurlod · Post by **ray.wurlod** » Wed Jul 16, 2003 1:13 am

While you could do this in DataStage performing twenty separate lookups the fact that all the columns are in one table and there is only one lookup table and that both these are in the same database, my suggestion would be to build a query to extract the desired result directly from Oracle, ensuring that LKUP_ONE.L_CD is indexed.

Even if each column has to be compared against a different Oracle table, the only difference will be a larger FROM clause.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

knvenkat · Post by **knvenkat** » Wed Jul 16, 2003 1:32 am

Hey Ray,
In my source table 20 columns there and these columns are NOT NULL. I have only One Lookup. So each column go to Lookup and check the following way...
src.id1 = lkp.id
src.id2 = lkp.id
src.id3 = lkp.id
.... (Like I have 20 Columns)

If, src.id1 = lkp.id is successful then retrieve E_V (this is Lookup column) and insert as new row with this E_V (Insert this value to E_VAL target table column) to target table.
Similarly, if src.id2 = lkp.id is successful then retrieve E_V and insert as new row with this E_V to target table.

The output should be come like the following way....

E_ID E_VAL
100 A
100 B
100 C

So each column will goto Lookup and If it successful then Retrieve Lookup column and then interest as new row.....

Thanx in Advance.

Ravi

ray.wurlod · Post by **ray.wurlod** » Wed Jul 16, 2003 5:46 pm

If you really want to do this within DataStage, you need twenty input streams each carrying E_ID and one of the other columns. Each of these has its own Transformer stage which performs a lookup and outputs a row if the lookup succeeds. The result can be inserted into the same target table from each output.

If you want to do it in Oracle, you can do the same thing with a fairly standard UNION query.

SELECT T1.E_ID, T2.L_ID
FROM SRC_ONE T1, LKP_ONE T2
WHERE T1.E_ONE = T2.L_ID
UNION
SELECT T1.E_ID, T2.L_ID
FROM SRC_ONE T1, LKP_TWO T2
WHERE T1.E_TWO = T2.L_ID
UNION
...

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

nvkravi · Post by **nvkravi** » Mon Jul 21, 2003 4:56 am

Hey,

Thanx to this replies. Still Im in R n D to Transpose my Source Columns into Target Records. Is there any otherway to do without take 20 Input Streams and with more Lookups. I have 4 columns in my Target table and each of this Target columns having 11 columns to transpose each. So If i write Stored Procedure also It may take time to handle lakhs of records.

Waiting your openion.

Thanx and Regards
Ravi

ray.wurlod · Post by **ray.wurlod** » Mon Jul 21, 2003 5:28 am

I think you'll be surprised how fast the UNION (or UNION ALL) approach works, provided you don't include an ORDER BY clause in it.