Partition in DB2 ref table Query Vs Data Stage partition
Posted: 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.
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.