Performance Issue (DB2/400 to Oracle 9i) Extraction
Posted: Tue Oct 10, 2006 2:52 am
Hi,
I facing performance problem while extracting the data from DB2/400 using datastage ODBC connection.
----- Enviorment -- -------------
Source System
JD Edwards (ERP APPLICATION)
OS: AS/400
DB: DB2/400
----------------------------------------------
Target Database:
Oracle 9i
------------------------------------------------
The requirement is that we need to do extract the data from JD Edwards (DB2/400) database on a daily basis.
All the dates in Source system are in Julian Date formate
To extract the delta records from source system we are doing a lookup with a Current_status_table from oracle 9i (which keep track of load) with the DB2/400 table (eg.F0005 ) compare with the respective julian date field by doing a lookup. This is design is having poor performance issues.
Eg. some of the source system table has more than 40Lakhs of records out of which we may need to extract only 1000+ rows per day as per the date filter conditions. and in some case out of 40Lakhs record there no transaction on that particular day. In such case this lookup filter is checking each and every records and then after 2:30 hours its populating zero records.
To avoid this situation if we directly apply the date filter condition in the UserDefine SQL at the ODBC Stage Level its very very effective and fast.
But in need to assign this Job paramter at run time bcos this contains dynamic values keep changing on daily basis.
Please provide some effective solution how to assgin the run the parameter in datastage.
------------------------------------------------
SELECT FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD5 FROM F0005
WHERE DRUPMJ > P_ST_DT AND DRUPMJ <= P_END_DT
-------------------------------------------------------------
DRUPMJ is the julian format date in (DB2/400)
P_ST_DT = 106001 julian format date ('01-JAN-2006')
P_END_DT = 106005 Julian format date ('05-JAN-2006')
The above two parameter values needs to be assign at run time.
Please help...
Regards,
Srikanth
--------------------------------------------------
I facing performance problem while extracting the data from DB2/400 using datastage ODBC connection.
----- Enviorment -- -------------
Source System
JD Edwards (ERP APPLICATION)
OS: AS/400
DB: DB2/400
----------------------------------------------
Target Database:
Oracle 9i
------------------------------------------------
The requirement is that we need to do extract the data from JD Edwards (DB2/400) database on a daily basis.
All the dates in Source system are in Julian Date formate
To extract the delta records from source system we are doing a lookup with a Current_status_table from oracle 9i (which keep track of load) with the DB2/400 table (eg.F0005 ) compare with the respective julian date field by doing a lookup. This is design is having poor performance issues.
Eg. some of the source system table has more than 40Lakhs of records out of which we may need to extract only 1000+ rows per day as per the date filter conditions. and in some case out of 40Lakhs record there no transaction on that particular day. In such case this lookup filter is checking each and every records and then after 2:30 hours its populating zero records.
To avoid this situation if we directly apply the date filter condition in the UserDefine SQL at the ODBC Stage Level its very very effective and fast.
But in need to assign this Job paramter at run time bcos this contains dynamic values keep changing on daily basis.
Please provide some effective solution how to assgin the run the parameter in datastage.
------------------------------------------------
SELECT FIELD1,FIELD2,FIELD3,FIELD4,FIELD5,FIELD5 FROM F0005
WHERE DRUPMJ > P_ST_DT AND DRUPMJ <= P_END_DT
-------------------------------------------------------------
DRUPMJ is the julian format date in (DB2/400)
P_ST_DT = 106001 julian format date ('01-JAN-2006')
P_END_DT = 106005 Julian format date ('05-JAN-2006')
The above two parameter values needs to be assign at run time.
Please help...
Regards,
Srikanth
--------------------------------------------------