ODBC Stage

DCSD · Post by **DCSD** » Mon Sep 12, 2011 9:04 am

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.

qt_ky · Post by **qt_ky** » Mon Sep 12, 2011 9:10 am

When you connect directly to the table and it runs fine, what stage and settings are you connecting directly with? How long does it take as is?

chulett · Post by **chulett** » Mon Sep 12, 2011 9:26 am

In addition to that, what database are we talking about here and what kind of perceived issues are you seeing? Generally speaking, one does not "research the ODBC stage" looking for speed improvements, especially over a native stage.

DCSD · Post by **DCSD** » Mon Sep 12, 2011 10:08 am

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?

qt_ky · Post by **qt_ky** » Mon Sep 12, 2011 10:45 am

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?

DCSD · Post by **DCSD** » Mon Sep 12, 2011 11:41 am

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.

qt_ky · Post by **qt_ky** » Mon Sep 12, 2011 1:31 pm

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.

qt_ky · Post by **qt_ky** » Mon Sep 12, 2011 1:37 pm

Just to be clear (because it's not yet), what is your job design?

Is it like this? ORA --> XFM --> ODBC or ORA --> XFM --> ORA

or FILE --> XFM --> ODBC and ORA, etc.?