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

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
paluindianAsked:
Who is Participating?
 
kedlunanuConnect With a Mentor Commented:
And last but not least...commit frequently, thats the way to avoid deadlocks.
0
 
CEHJCommented:
Make sure there's no lock on the table from another app. Maybe you're reading it in another app..?
0
 
paluindianAuthor Commented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
CEHJCommented:
Are you trying to drop the table programmatically? If so - how?
0
 
HemanthaKumarConnect With a Mentor Commented:
> 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
 
paluindianAuthor Commented:
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
 
HemanthaKumarCommented:
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
 
paluindianAuthor Commented:
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
 
KeithWatsonCommented:
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
 
KeithWatsonCommented:
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
 
kedlunanuCommented:
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
 
paluindianAuthor Commented:
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
 
CEHJCommented:
>>
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
 
paluindianAuthor Commented:
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
 
CEHJCommented:
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.