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.
Table getting locked on stopping a job
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Cheers
Wasim
--If necessity is the mother of invention, then the need to simplify is the father of it.
Wasim
--If necessity is the mother of invention, then the need to simplify is the father of it.
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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.![Wink :wink:](./images/smilies/icon_wink.gif)
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
Cheers
Wasim
--If necessity is the mother of invention, then the need to simplify is the father of it.
Wasim
--If necessity is the mother of invention, then the need to simplify is the father of it.