• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1658
  • Last Modified:

DROP PROCEDURE fails with SQL0911N Reason code "68". SQLSTATE=40001

I'm trying to drop a very large, complex stored procedure, which calls other stored procedures. I keep getting a lock error and if fails

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0911N  The current transaction has been rolled back because of a deadlock
or timeout.  Reason code "68".  SQLSTATE=40001

We're running DB2 v9.7 on AIX.

Why can't I drop the procedure? We plan on recreating the procedure. Should I use "CREATE OR REPLACE PROCEDURE" instead of two discrete steps of DROP then CREATE?

Sorry, I don't deal much with stored procedures and I'm just trying to help out.

1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, if you want to "recreate" anyhow, don't drop it. with the drop, you are loosing all permissions assigned to the procedure, by using the create or replace you will retain any permissions granted to roles/users.
Tomas Helgi JohannssonCommented:

The reason why you cant drop a stored procedure and are getting -911 error is due to that another application/session is using the procedure.
Use db2top to find the session executing the SP and terminate it. Then you will be able to
drop the procedure.

    Tomas Helgi
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now