Solved

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

Posted on 2004-09-15
15
3,993 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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
nextBoolean(double p) for Random class 3 41
Android development question 2 54
jboss 7.1 start up error 1 40
running on tomcat not jboss eap 7.0 3 18
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…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

828 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