Solved

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0913N

Posted on 2004-09-15
15
3,984 Views
Last Modified: 2011-10-03
Hello Everybody,

I'm getting this error when i'm trying to drop table from Z/OS.

"COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0913N  Unsuccessful execution caused by deadlock or timeout.  Reason code "00C9008E".  SQLSTATE=57033"

I can run " select * from testtable" successfully but only when I'm trying to drop table I'm getting this error. Does anybody know what could be the reason?

I'm using JDBC Universal driver for UDB.

Thanks,
P
0
Comment
Question by:paluindian
  • 5
  • 4
  • 2
  • +2
15 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 12068420
Make sure there's no lock on the table from another app. Maybe you're reading it in another app..?
0
 

Author Comment

by:paluindian
ID: 12068485
I can manually go and drop the table.
I can delete a single row from the table...I did not understand " Maybe you're reading it in another app..? " 

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12068675
Are you trying to drop the table programmatically? If so - how?
0
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 24

Assisted Solution

by:HemanthaKumar
HemanthaKumar earned 30 total points
ID: 12068893
> I can manually go and drop the table.
Did you use the same user id as that of jdbc call ?

The error might be related to timeout and not be of deadlock issue ??

So check the user has access to drop tables. Secondly, check if you have opened any result set in the script. This might keep the table locked...

~Hemanth
0
 

Author Comment

by:paluindian
ID: 12068992
Hi Hemanth,

I used same userid/password as I used for jdbc.
While searching on this topic I guess I have somehow locked the table. How can i unlock it?
I was using following commands:

db2 update monitor switches using LOCK ON
db2 get snapshot for LOCKS on dbName > 
The directory_name\lock_snapshot.log now has the DB2 lock information.
Turn off the lock monitor by executing: db2 update monitor switches using LOCK OFF


but while executing 2nd command : db2 get snapshot for LOCKS on <database_alias>

I get following error:

SQL1428N  The application is already attached to "DB2" while the command
issued requires an attachment to "NDEA5001" for successful execution.

what is the cause?
I think deadlocking could be the problem.
I'm on windows machine having ZOS database cataloged. I do not have ISPF client to directly work on zos.

Thank
P
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 12069164
Either commit or rollback are the two options which release lock pending on the table.. For now just force the connection and it should drop the lock automatically.

Follow this sample on how to drop/create tables in db2
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/ad/samples/jdbc/s-TbCreate-java.htm
0
 

Author Comment

by:paluindian
ID: 12070009
Hi Hemanth,

I tried commit, rollback and re-enabling the cataloged connection with zos system. I'm still not able to drop the table.

I searched and found out regarding reason code : "00C9008E" and it points me towards
http://publib.boulder.ibm.com/infocenter/dzichelp/index.jsp?topic=/com.ibm.db2.doc.apsg_8.1.0/bjnqmstr282.htm

But I could not understand much with my little experience with zos and db2.

Please help me if you have any suggestions.
thanks,
P
0
 
LVL 3

Expert Comment

by:KeithWatson
ID: 12076795
Hi P, I don't know about dropping the locks from the table; can you ask a DBA to do this? However, we get this happening quite often when the SQL accessing the mainframe database is not appeneded with FOR FETCH ONLY, since in DB2 by default, X-locks are placed on rows that are read; you need to tell it not to do this. If you place X-locks on rows, subsequent transactions will timeout.

So for example:

SELECT * FROM DBNAME.TABLENAME FOR FETCH ONLY

00C9008E according to quickref is a timeout rather than a deadlock by the way.

Another approach to dropping client-server locks would be to restart DB2Connect on the mainframe, assuming it's DB2Connect you're using to talk to z/os DB2.
0
 
LVL 3

Expert Comment

by:KeithWatson
ID: 12076841
Incidentally, the IBM page you reference does explain roughly what happens in this circumstance; however, it's related to how IMS transactions behave, rather than either dynamic SQL or DB2 stored procedures.
0
 

Expert Comment

by:kedlunanu
ID: 12077039
I would suggest, there is problem with your program.
Your program is locking the table before you may drop it. Check your select statements. If you haven't closed rs statements then the possiblity is that it is locking the table.
0
 

Accepted Solution

by:
kedlunanu earned 20 total points
ID: 12077065
And last but not least...commit frequently, thats the way to avoid deadlocks.
0
 

Author Comment

by:paluindian
ID: 12077134
Hello everybody,

It worked now!!!
The problem was as you mentioned Kedlu and Hemanth. One of my colleague was also running this automation and somehow...by mistake he commented out the rs.close part. And thats what caused the whole problem. Also now as kedly suggested I added commit in that too and it started working.

So the earlier select statement from my collegues machine was causing deadlock.

Thanks ya all.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12078786
>>
One of my colleague was also running this automation and somehow...by mistake he commented out the rs.close part. And thats what caused the whole problem.
>>

And what did i say in my first comment..?  ;-)
0
 

Author Comment

by:paluindian
ID: 12079337
Yes CEHJ,
That time I did not understand your comments. Also, I was not aware that my colleague is also accessing the ZOS machine. I sure can not forget your contribution to my problems...!! I see you commenting first to any of my problems...thanks so much.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 12079386
:-)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question