Datastage ODBC Problem - Help Needed

[appcon]

Hi,

I have a strange problem with ODBC stage. I using a job to load history data into Oracle table for individual years.

After running for couple of years, when I run the job to load a new year data it stops loading. It doesn't give any errors or stops processing...the count remains 0/0 for all stages. The point at which it stops varies from time to time. When I tested the job for the same year where it stopped using a seq file instead of ODBC, it works.

And when the ODBC is connected back, it stops. I don't know where the problem is whether its with DS or Oracle.

Did anyone come across this situation. Any suggestions would be appreciated.

Thanks in advance.

Sri.

[roy]

Hi,
having 0/0 on any output link (loading to a DB) can be one of several reasons:
1. the resource/table is locked and the job simply waits for it to be released.
2. it is waiting for rows to be fetched from the source table, which again can be waiting on an exclusive lock to be released.
3. you have an infinite loop in the transformation in between.
4. it is waiting on a before/after sql to finish prior to continue the data flow.

I hope I listed all posibilities.

now you simply need to get your DBAs to try see if any locks prevent your load.

Actually this morning I was amazed to find out that after 00:35 all jobs were hung in my production environment. After some investigation we found out that a session which had nothing to do with ETL developers had an exclusive lock on our production parameters table, this caused all jobs to stand and wait for the lock to be released.
I think the cause might be use of some oracel tool (can't remember the name).

IHTH,