Performance issue with ODBC stage
Posted: Sat Oct 04, 2008 3:25 am
Hi All,
I am using custom query as the input to ODBC Stage and transforming to target ODBC after adding 2 more columns, one having defaulted to zero and other having sequence no. using KeyMgtGetNextValue(1)..
I.FIELD2 and I.FIELD15 are the primary columns in target
the custom query appears like...
SELECT I.FIELD1 ,
I.FIELD2,
P.FIELD3,
PS.FIELD4,
I.FIELD15,
I.FIELD6,
PC.FIELD7,
P_CLI.FIELD8
FROM TBL1 I ,
TBL2 A,
TBL3 PC,
TBL4 AR,
TBL5 P_CLI,
TBL6 P,
TBL7 PS
WHERE I.FIELD3 =A.FIELD2
AND PC.FIELD3 =A.FIELD3
AND AR.FIELD4=I.FIELD5
AND AR.FIELD7=118
AND P_CLI.FIELD8=AR.FIELD5
AND P.FIELD8=P_CLI.FIELD8
AND PS.FIELD9 =I.FIELD9
AND I.FIELD15 > SYSDATE - 10
transaction handling: rows per transaction: 0
parameter array size = 1
if i change this the job gets aborted. indicating unique constraint violated..
update logic is "insert rows without clearing"
the issue is the job is taking long time to run say approximately 40 mins.
the same custom query executed in 25 secs. in oracle 10g but while using in job. the rows transferred at 20 row/sec
also I am getting warnings like
SQLSTATE=23000, DBMS.CODE=1
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00001: unique constraint (RELODS.SYS_C0060909) violated
since the record has duplicate entries..
records count is just below 100,000
how to improve the performance? I want to reduce the run time any suggestions please?
thanks in advance
I am using custom query as the input to ODBC Stage and transforming to target ODBC after adding 2 more columns, one having defaulted to zero and other having sequence no. using KeyMgtGetNextValue(1)..
I.FIELD2 and I.FIELD15 are the primary columns in target
the custom query appears like...
SELECT I.FIELD1 ,
I.FIELD2,
P.FIELD3,
PS.FIELD4,
I.FIELD15,
I.FIELD6,
PC.FIELD7,
P_CLI.FIELD8
FROM TBL1 I ,
TBL2 A,
TBL3 PC,
TBL4 AR,
TBL5 P_CLI,
TBL6 P,
TBL7 PS
WHERE I.FIELD3 =A.FIELD2
AND PC.FIELD3 =A.FIELD3
AND AR.FIELD4=I.FIELD5
AND AR.FIELD7=118
AND P_CLI.FIELD8=AR.FIELD5
AND P.FIELD8=P_CLI.FIELD8
AND PS.FIELD9 =I.FIELD9
AND I.FIELD15 > SYSDATE - 10
transaction handling: rows per transaction: 0
parameter array size = 1
if i change this the job gets aborted. indicating unique constraint violated..
update logic is "insert rows without clearing"
the issue is the job is taking long time to run say approximately 40 mins.
the same custom query executed in 25 secs. in oracle 10g but while using in job. the rows transferred at 20 row/sec
also I am getting warnings like
SQLSTATE=23000, DBMS.CODE=1
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-00001: unique constraint (RELODS.SYS_C0060909) violated
since the record has duplicate entries..
records count is just below 100,000
how to improve the performance? I want to reduce the run time any suggestions please?
thanks in advance