One DS job Creating Multiple oracle session

120267 · Post by **120267** » Fri Dec 09, 2005 5:44 am

Hi,
We are having a job to update the target table.
After updating some records..Its taking infinite time i..e the job is in running status for long time.

And we conclude that some session may be running,and due this TM type lock have been ocured,and this job is waiting for that session to commit.

And we found that three session are running in oracle for the user,we killed that session and again we started the job .But still we are facing the same issue.i.e again 3 session is running in oracle due this dead lock has been occured.

How a job will create 3 sessions in oracle?Please help to resolve this issue.

Thanks,
Siva.G

ArndW · Post by **ArndW** » Fri Dec 09, 2005 6:06 am

How many Oracle stages do you have in your job, and how many links going into the stage(s)? Each of these might be an Oracle connection. You should ask you DBA to monitor these sessions and see what lock or resource they are waiting on - a deadlock within connections for one DS job doesn't point to a DataStage error but to a potential design issue.

120267 · Post by **120267** » Fri Dec 09, 2005 6:14 am

Hi Arnd,

Its just one to one mapping with one lookup(Hash),2 oracle stages (source and target),one link to target.

Thanks,
Siva.G

ArndW · Post by **ArndW** » Fri Dec 09, 2005 6:27 am

Then you should reproduce the problem and have the DBA tell you which locks the processes hold and are waiting on (if it is a deadlock as you assume the locks/waits should match up amongst your 3 sessions). I think in this case you should only have two sessions, though. Could one of them be an old one that hasn't been cleared up?