Job hanging while writing to odbc stage

dhletl · Post by **dhletl** » Mon Sep 12, 2005 8:51 pm

Hi -

Problem in DS Server job (connecting to a database table)-

I've a stream of data as input (consider a data file for simplicity).
No of records 10-500.

Based on conditional check in a transformer -
I hv 2 outputs each writing to a table X in Oracle database using odbc stage.
Further I hv another trasnformer following the above one- which does another level of conditional check - and has 1 output stream wiriting to table X in the database.

The table X is partitioned on country id. The data in the table looks as-
No of records for High Volume Country ~ 32000 to 37000
No of records for Medium Volume Country ~ 7000 to 13000
No of records for Low Volume Country ~ 300 to 5000

On running this job is hanging for data for some countries - mainly for those countries with high volumes and those with medium voilumes.

Investigation -
We find that on the database end - there are few processes waiting for resources.
Pls note there is ample dbspace/ resources available considering few hunderds of records in source stream.

Constraint-
We are hoping if there is a solution to re-solve this other than the re-design of the job. (Since we know there are potential design flaws which has been raised to the appropriate team)
Also since this is happening in Production enviroment we need a quick-fix first.

Appreciate if you can pls provide any pointers/ help to resolve this.

Thanks a bunch.

Nitin

chulett · Post by **chulett** » Mon Sep 12, 2005 9:38 pm

Sounds like your job is locking itself out - is that what you meant by "waiting for resources"? And are you saying you have three output links all writing to the same Oracle table using three different ODBC stages - or do all three links go into a single ODBC stage?

If you were using OCI stages (and why aren't you?) then you solve any locking problems by using a single stage over multiple stages when sending multiple streams to the same table. I imagine it would work the same with ODBC.

Your "quick fix" might be to set the commit level (Rows per transaction) to 1 so every row is commited as it is written, but...

dhletl · Post by **dhletl** » Tue Sep 13, 2005 12:52 am

Craig,

Thanks - We tried that option to reduce teh transaction size to 5 or 1.
However, still the problem persists.

>>Sounds like your job is locking itself out - is that what you meant by "waiting for resources"?
Yes

>>And are you saying you have three output links all writing to the same Oracle table using three different ODBC stages - or do all three links go into a single ODBC stage?
All the three links are writing to the same ORacle table (3 separate ODBC stages)

Besides can you explain about using the OCI stage for this?

Thanks ..

Nitin

chulett · Post by **chulett** » Tue Sep 13, 2005 7:02 am

The OCI stages are the native stages provided for accessing Oracle via DataStage. In my mind, ODBC is the 'stage of last resort' only to be used when you've got no other choice. All that's required to use OCI is an Oracle client installed on the DataStage server and some config tweaks in the dsenv file that are spelled out in the documentation.

That being said, using separate stages to write to the same table can cause problems. First thing to try would be to use one ODBC stage and direct all three links to it, see if that fixes things. This *is* the solution when using OCI, no clue about ODBC however.