Job hanging while writing to odbc stage
Posted: 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
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