Deadlock or timeout error[Reason code "68". SQLSTATE=40001]

[Videla]

Hi
I am new to IDM DB2. I am trying to create a procedure using IBM Development Center which
is available in my IBM DB2 Client. When I try to build the procedure for the first time, it
got created and when i try to rebuild the procedure after some changes in the procedure I am
getting the following error. This imapcts my development. I also checked that none of applications
are accessing the tables referred by the procedure. Any help would be appreciated.

easyinfodm.SP_PRODUCT_MIX_V2 - Build for debug started.
DROP SPECIFIC PROCEDURE easyinfodm.SQL060213064522632
easyinfodm.SP_PRODUCT_MIX_V2 - Failed to drop the stored procedure.
[IBM][CLI Driver][DB2/SUN64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001

--Build committed--
easyinfodm.SP_PRODUCT_MIX_V2 - Build for debug completed with warnings.

Other Details
-------------
IBM DB2 Version 8.2

 

[gregsimpson]

Videla,

it seems to me that one of the db2 agents, may still have a handle on this procedure. Are you able to force all users off ebfore you do the DROP SPECIFIC PROCEDURE

Cheers
Greg

 

[Videla]

Greg,
Thanks for helping me. I have one doubt, If you alter a table then do we need to execute "commit" after each alter statement. We tried this by opening two DB2 client sessions
and in one session we altered the table and tried to execute some select statement from another session. It gave dealock or time out error. Once we commit in the first session, our query fetched rows in second session.

My question, IS commit require after alter statement.

Thanks in advance for your help

 

[gregsimpson]

Videla,

I was under the impression that DDL statements such as alter etc auto committed and can't be rolled back.

However your findings appear to have contradicted my beliefs. It seems to me that your alter statement has not surprisingly locked the table and has stopped you issuing your select statement until the Unit OF Work controlling the alter has indeed succeeded.

So it looks like a commit (implied or otherwise) is neccessary to achieve your aim of getting a query to work, in a seperate session, to the alter command. Out of interest you could always do the same experiment, only this time whilst reading the table put on the end of your statement WITH UR and see if you get any results or not.

Cheers
Gerg