Solved

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

Posted on 2004-09-15
15
4,002 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
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…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

730 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