Solved

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

Posted on 2004-09-15
15
3,957 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now