Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1624
  • 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.

Thanks!
0
data_bits
Asked:
data_bits
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.
0
 
Tomas Helgi JohannssonCommented:
Hi!

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.

Regards,
    Tomas Helgi
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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