Table getting locked on stopping a job

memrinal · Post by **memrinal** » Tue Jul 31, 2007 4:20 pm

We have a job updating an Oracle DB Table, through ODBC Stage.
Whenever this job is stopped (Manually through Director), the target table gets locked.

What can be done to ensure that when the job is aborted, the table does not get locked. -- i.e. lock is released when the DS job aborts.

Thanks in advance.

ray.wurlod · Post by **ray.wurlod** » Tue Jul 31, 2007 5:00 pm

What can be done? Get IBM to fix the stage.

wasimraja · Post by **wasimraja** » Tue Jul 31, 2007 5:55 pm

This is one of the problems I also faced quite a lot of times recently.

In Informatica, I have always aborted jobs and restarted them and the chances of creating locks seemed to be pretty less compared to what I am facing now.

In Datastage, whenever I abort jobs, 7 out of 10 times, it creates a lock for me. I use Oracle database by the way.

DSguru2B · Post by **DSguru2B** » Tue Jul 31, 2007 6:59 pm

A simple and stright fix; Dont stop or kill the job. There is more than often an alive thread to the database that goes bonkers.
Best way I deal with such jobs is, pick up the phone and ask the dba to kill the thread orginated by my id. Once the connection is lost, the job aborts, rather gracefully.

chulett · Post by **chulett** » Tue Jul 31, 2007 7:34 pm

There are plenty of times when Oracle is not in a position to honor anything like a 'stop request'. Typically, if the query is actively returning rows it can be gracefully stopped. Other times, as when a dataset is being 'readied' for one example, you can't - or at least not until it gets to an interrupt. This isn't a DataStage thing, it's an Oracle thing. And if you force the DS job to abort during one of those times, you stand a very good chance of leaving your Oracle query / DML running.

Now, when I've had that happen, it doesn't leave the table 'locked' just 'busy'. And as DSGuru notes, I've found the best way to ensure I really get something killed that might be problematical - for example it's been gathering records together for some time now and nothing is going to start flowing into my job any time soon - then I too get a DBA on the line and have them kill the session / SID from their side. Oracle will notice and then DataStage will too. I gar-ron-tee.

memrinal · Post by **memrinal** » Wed Aug 01, 2007 4:14 pm

Thanks for pitcing in with your experience. hink getting the session killed is the best way to deal with this.

wasimraja · Post by **wasimraja** » Wed Aug 01, 2007 5:44 pm

Found something interesting. This maynot be 100% correct but it happened with me.

I had this job which is running fine and loading records. Now, when I say abort the job, the job STOPS and there is no lock on the database.
Also, I clicked on the stop button just once. I tried this thrice and it behaved in the same way.

I had another job whose source sql query is taking forever to run and start fetching records. When I abort this job once, it wont stop. So, I aborted it like 4 or 5 times continuously (by clicking the stop button in datastage) and the job says 'ABORTED'. Now, it creates a lock in the target table. I tried this like 5 times and all the time, there was a lock.

But, as we know already that this is indeed a problem with Oracle, we need to kill the session in database level using DBA's help.

Just wanted to let you guys know this information.