Hello - I was told to research using the ODBC stage for faster loading. I am updating a table that contains 600million records. I am processing about 300 million. When I added the ODBC stage and configured it for Write Method = Upsert, Upsert Mode = Update only, and Upsert Method = Auto generated SQL. I run the job and it just hangs. If I don't use this stage and connect my transformer stage directly to my target table, it runs just fine. What am I missing? Should I even use this stage? I am trying to update faster. I am currently not doing any inserts.
Thanks.
ODBC Stage
Moderators: chulett, rschirm, roy
Hello,
I resolved the issue with hanging. It was a deadlock issue.
But regarding the use of the ODBC stage. When I dont use the ODBC connector, I am connecting a remove duplicate stage to an Oracle connector. To process 700K records it takes 2mins44 seconds. When I add the ODBC stage and connect that to the Oracle connector, it took 4 mins 39 seconds.
I am just wondering if I should abandon the use of the ODBC stage since it doesn't seem to be making my job run any faster. Someone asked me to look into it because it has this UPSERT capability and they thought it might improve speed. Is that not the case from your experience?
I resolved the issue with hanging. It was a deadlock issue.
But regarding the use of the ODBC stage. When I dont use the ODBC connector, I am connecting a remove duplicate stage to an Oracle connector. To process 700K records it takes 2mins44 seconds. When I add the ODBC stage and connect that to the Oracle connector, it took 4 mins 39 seconds.
I am just wondering if I should abandon the use of the ODBC stage since it doesn't seem to be making my job run any faster. Someone asked me to look into it because it has this UPSERT capability and they thought it might improve speed. Is that not the case from your experience?
Are you putting the ODBC Connector stage in place of the Oracle Connector stage or connecting those two together?
What version of DataStage are you on?
In DS 8.5 the ODBC Connector stage has "Write mode" choices of Insert, Update, Delete, Insert then update, Update then insert, or Delete then insert. If I were to compare using ODBC, I would just choose plain "Update" Write mode.
How long is the 300M updates taking using the Oracle Connector?
What version of DataStage are you on?
In DS 8.5 the ODBC Connector stage has "Write mode" choices of Insert, Update, Delete, Insert then update, Update then insert, or Delete then insert. If I were to compare using ODBC, I would just choose plain "Update" Write mode.
How long is the 300M updates taking using the Oracle Connector?
Choose a job you love, and you will never have to work a day in your life. - Confucius
Hello,
I am using the ODBC connector stage in conjunction with the Oracle Connector. I am on version 8.5. I haven't tried making 300Million updates using the Oracle connector. I am going to do that soon though but wanted to make sure my job was as efficient as possible. I am starting to think that I dont need the ODBC connector and I should just use the Oracle Connector by itself.
I am using the ODBC connector stage in conjunction with the Oracle Connector. I am on version 8.5. I haven't tried making 300Million updates using the Oracle connector. I am going to do that soon though but wanted to make sure my job was as efficient as possible. I am starting to think that I dont need the ODBC connector and I should just use the Oracle Connector by itself.
Try updates only using the Oracle Connector first. I would not use both types of stages in the same job at the same time to do the same thing. You can play with the settings on either stage. You might want to make sure the key columns used on the update are indexed in Oracle, especially considering the volume.
Choose a job you love, and you will never have to work a day in your life. - Confucius