Sequential File to Oracle Stage slow load issue

jatayl · Post by **jatayl** » Wed Feb 01, 2006 11:57 am

Hi, I'm a newbie to this forum, but I've been working with DataStage for over 3 years. Now, to my issue. We've been running in production for 2.5 years, with very few "issues" that we couldn't solve. Well, I've finally come across one. I have a process flow that reads in a sequential file to an oracle stage. It's just a straight Oracle table load. DataStage is installed on one server, and Oracle in on the other server.

Over the last month, we've had very slow loading times. I've decided to take this on by running a few tests. I've headed off 5 records in my test file to load. Unfortunately, the load takes about 5 to 7 minutes to run. I initially thought it was an Oracle issue, but I've worked with my fellow DBA trying to debug the problem. We wrote a sqlloader script that runs on the same server as DataStage is on, with results that were like milliseconds to load the records. How come DataStage is taking so long to load? Is it doing some kind of testing on the table before it actually initializes sqlloader? It just seems to be hanging in the log at the OSH Dump step. Help would be appreciated.

Thanks,
Jason

ArndW · Post by **ArndW** » Wed Feb 01, 2006 12:09 pm

Hello Jason,

have you tried using the "upsert" method in your Oracle as a test to see if that is also running slower than expected?

I'm not at a PC where I have the documentation available, but I would look at the environment settings that might let you defer the bulk loading to a manual start (as server jobs let you do); then you could see if the PX jobs runs quickly and if the manual start of the loaders goes quickly or is taking time - letting the problem be narrowed down a bit. Perhaps there are bulk load settings that cause a conflict and are slowing you down.

kwwilliams · Post by **kwwilliams** » Wed Feb 01, 2006 12:14 pm

What are the settings for diabling constraints and rebuilding your indexes? Does you sqlloader test use the same options? I have found many times that the load is fast but the constraints and index rebuilds take longer.

jatayl · Post by **jatayl** » Wed Feb 01, 2006 1:36 pm

This is the last message I receive on my log before it hangs:

SUPRS_DTL_LOAD_TBL: Calling APT_DBExportOperator in SUPRS_DTL_LOAD_TBL::reportInputAttachment()

The next entry in the log is 5 or 7 min later, when sqlloader starts the load.

SUPRS_DTL_LOAD_TBL: Returning from APT_DBExportOperator in SUPRS_DTL_LOAD_TBL::reportInputAttachment()

I'm using the default settings in the Oracle stage. I have Disable Constraints set to False, and the index mode options is not selected.

I'll talk with one of our DBAs on upsert and how that works.

Thanks,
Jason