Database DeadLock and still cannot open the txt file after..

ICE · Post by **ICE** » Sun Aug 06, 2006 9:41 pm

Hi,

I need your suggestions pls.
I have one job that output to the txt file and insert to the database.That job run on schedule as every morning.
Today I got a Database DeadLock error msg and abort the job.
1.May I know how to solve such kindda problem unless rebooting the server?I got such kindda problem before and I rebooted the server that time.Actually ,I don't wanna reboot the server and so want to know how to solve that kind of problem.
2.That job seems still running in back ground coz the output txt file that used by that job still cannot use.Cannot open.I got the err msg as "still use by one job" when I tried to open this.SO could u pls let me know how to solve such problem also.HOw to make that txt file to release?

Thanks,

kumar_s · Post by **kumar_s** » Sun Aug 06, 2006 9:59 pm

Can you post the exact deadlock error message. Most likely, if it is a database deadlock errors, its cause would be from an external system. May be some other job or the same job might to access the same table at same time. Or input from the table and update the same table.

loveojha2 · Post by **loveojha2** » Sun Aug 06, 2006 10:04 pm

Possible options available:

1. Stop the Job from director (if it is still running).
2. If it is not running then possibly it has locked the resources and aborted. Run DS.TOOLS from your Administrator and release the locks held by process of the job. (This should release the lock on the Seqential file).
3. If this also does not work then possibly you can kill the process from the task manager and then release the locks held by that process. (But instead I would advise you of a DS server restarts.)
4. Check if any locks are there on the database related objects (used by the job). Handle them accordingly (dependent on the database)

ICE · Post by **ICE** » Mon Aug 07, 2006 2:51 am

Hi Kumar,

Here is the error message.

SQLSTATE=40001, DBMS.CODE=-911
[DataStage][SQL Client][ODBC][IBM][CLI Driver][DB2/NT] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001

Thanks,

kumar_s wrote:Can you post the exact deadlock error message. Most likely, if it is a database deadlock errors, its cause would be from an external system. May be some other job or the same job might to access the same table at same time. Or input from the table and update the same table.

ICE · Post by **ICE** » Mon Aug 07, 2006 3:04 am

Hi lovojha2,

Now the job has been released the output txt file coz the job that I stopped has been done.
That job seems like running in the back ground.
From the DS director,job's status is compiled coz I stopped the job and recompiled it.But it seems keep running in the back ground.
1.May I know how to stop the job if the job seems not completely stopped?
In the above case,even though I stoped the DS services,the job cannot be stopped completely.This morning ,even I did rename the job and recompiled it,but it didn't stopped completely.

2.As per your suggestion I don't see the DS-TOOL in Datastage Administrator.Where can I find out it?Is there such kind of function in the window 5.x version?
3.If I have to stop from Window Task Mgr,could you pls let me know the exact service name?

Thanks,

loveojha2 wrote:Possible options available:

1. Stop the Job from director (if it is still running).
2. If it is not running then possibly it has locked the resources and aborted. Run DS.TOOLS from your Administrator and release the locks held by process of the job. (This should release the lock on the Seqential file).
3. If this also does not work then possibly you can kill the process from the task manager and then release the locks held by that process. (But instead I would advise you of a DS server restarts.)
4. Check if any locks are there on the database related objects (used by the job). Handle them accordingly (dependent on the database)

loveojha2 · Post by **loveojha2** » Mon Aug 07, 2006 3:21 am

1.May I know how to stop the job if the job seems not completely stopped?
In the above case,even though I stoped the DS services,the job cannot be stopped completely.This morning ,even I did rename the job and recompiled it,but it didn't stopped completely.

If you have successfully compiled it, you should get a status of compiled from the director and how did you know that it is still running.

2.As per your suggestion I don't see the DS-TOOL in Datastage Administrator.Where can I find out it?Is there such kind of function in the window 5.x version?

I have not worked with the 5.x version (although I have worked but for very short time), so I can't tell you whether you have DS.TOOLS on it or not.
Try giving command DS.TOOLS on the command window of the DS Administrator, if it works then you have it.

3.If I have to stop from Window Task Mgr,could you pls let me know the exact service name?

The uvsh.exe's are the one that are responsible for a program run.
But please don't do it this way

ICE · Post by **ICE** » Mon Aug 07, 2006 3:44 am

Dear LoveJoha2,

1.I did realized as the txt file that I couldn't open this morning which used by this job got the file size bigger than before when I can open it.
So I was sure the job was running in the back.I have been this kind of experience so many times.
So I really want to know how can I stop the job completely.
2.Hummm......I don't see the DS-TOOL at all .I think v 5.x may not have.

Thanks for your advise,

loveojha2 wrote:
1.May I know how to stop the job if the job seems not completely stopped?
In the above case,even though I stoped the DS services,the job cannot be stopped completely.This morning ,even I did rename the job and recompiled it,but it didn't stopped completely.
If you have successfully compiled it, you should get a status of compiled from the director and how did you know that it is still running.

2.As per your suggestion I don't see the DS-TOOL in Datastage Administrator.Where can I find out it?Is there such kind of function in the window 5.x version?
I have not worked with the 5.x version (although I have worked but for very short time), so I can't tell you whether you have DS.TOOLS on it or not.
Try giving command DS.TOOLS on the command window of the DS Administrator, if it works then you have it.

3.If I have to stop from Window Task Mgr,could you pls let me know the exact service name?
The uvsh.exe's are the one that are responsible for a program run.
But please don't do it this way

kumar_s · Post by **kumar_s** » Mon Aug 07, 2006 4:40 am

Not "DS-TOOL " but "DS.TOOLS". Issue this command in the "Command" option available in DS Administrator.
Or you can try TCL, its easy to use GUI.

ICE · Post by **ICE** » Mon Aug 07, 2006 11:38 pm

Now ,I got it.
Thank you so much Kumar.

BTW,have u guys experience with this error msg "Runtime overflow 6"?
When i select the job which got problem,I got the above err msg and shut down the datastage director or datastage mgr that I used to open that job.
Still I cannot solve that problem.
Help me pls.......

Thanks in advance,

kumar_s wrote:Not "DS-TOOL " but "DS.TOOLS". Issue this command in the "Command" option available in DS Administrator.
Or you can try TCL, its easy to use GUI.