Partition in DB2 ref table Query Vs Data Stage partition

dlkkrao · Post by **dlkkrao** » Wed Mar 18, 2009 5:51 am

Hi All,

I wanted to perform some testing on one of existing jobs.In that job, source is a sequential file. They wanted to perform look up on DB2 table.

Reference table query:
SELECT LTRIM(RTRIM(VEH_IDENT_NBR)),
LTRIM(RTRIM(SRC_TXN_ID)),
SRC_VER_NBR ,
TRANSACTION_ID,
RANK() OVER (PARTITION BY SRC_TXN_ID ORDER BY SOURCE_DT DESC,SRC_VER_NBR DESC,TRANSACTION_ID ASC) AS RNK
FROM GALAXY.TRANSACTION WHERE SOURCE_SYSTEM_CD=45

They are generating RANK based on above columns. In the same way they are generating one more RANK on source records.
They are using SRC_TXN_ID,SRC_VER_NBR AND RANK columns for their lookup purpose.

Issue Summary: Even though I have some matches in both source as well in reference table, I am not getting proper results. It means
I have following values in source and reference table:
BQ01B200186942
BQ01B200189331
BD01B2001177981
CA01B2001199701

However In my output, I am getting only BD01B2001177981. Data Stage does not able to find the remaining values of reference table.

I am using AUTO partition on both sides. I think, the issue is with DB2 partition in Query vs Data Stage partition.They are using partition in DB2 Query.

Can Anyone tell me why Data Stage not able to get all the matches into result ?

I am getting following fatar errors.

Lkp,0: Failed a keylookup for record.
Lkp,0: the runLocally() of operator [APT_LUTProcessOp in Lkp], partition 0 of 1, processID 901,140 on node2 failed.

Kindly help me out in this issue.

Thanks a lot in advance.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Mar 18, 2009 6:12 am

My guess...
Enable 'returns multiple rows' in lookup and set the missing option as 'continue'.

Is it you who developed the job? Maybe you can shed more light on what you need to achieve.

dlkkrao · Post by **dlkkrao** » Wed Mar 18, 2009 6:35 am

Sainath.Srinivasan wrote:My guess...
Enable 'returns multiple rows' in lookup and set the missing option as 'continue'.

Is it you who developed the job? Maybe you can shed more light on what you need to achieve.

Hi All,

As you suggested, I enabled 'return multiple rows' and set the missing option 'continue'.It did not work. I am getting all the 4 records into output, 1 record got valid transaction id as earlier, 3 records got null transaction ids. It means it is not able find the match in reference table.

My intention is to get valid transaction id and veh ident nbr from reference table based on src_txn_id,src_ver_nbr,Rank. There is no issue in generating RANKS on both sides and matching the rank values. I think, the whole problems lies in the partition specified in DB2 query vs Data Stage partitioning.

I had put two peek stages in the job. one is on source link before lookup. One is on reference link before lookup. I observed there is collission between DB2 partition and Data Stage partition.

Kindly let me know if you want any other information.

Thanks a lot in advance.

Scope · Post by **Scope** » Wed Mar 18, 2009 7:08 am

Use a Copy Stage inbetween Lookup Stage & DB2 Stage. It may give solution.

dlkkrao · Post by **dlkkrao** » Mon Mar 23, 2009 2:53 pm

Hi All,

I had tried with copy stage between look up and DB2 stage. I think, the issue with partition clause in DB2 Ref Query and Data Stage partition.

Please let me know if you need any other information.

Can anyone throw some light on this.

Thanks in advance.