English

Error: db_execute_ddlsql SQL Stmt: DROP PROCEDURE SDE.sde_release_proc ERROR in creating system stored procedures

Error Message

If the instructions to manually drop the SDE_RELEASE_PROC stored procedure are not followed when upgrading an ArcSDE 10 geodatabase for DB2 to 10 service pack (SP) 1 or subsequent hot fixes, the following error message is written to the upgrade log:

"db_execute_ddlsql SQL Stmt: <DROP PROCEDURE SDE.sde_release_proc>
ERROR in creating system stored procedures.
Error: -1
DBMS error code: -454
SQL0454N The signature provided in the definition for routine
"SDE.SDE_RELEASE_PROC" matches the signature of some other routine
that already exists in the schema or for the type."

Cause

The SDE_RELEASE_PROC stored procedure must be dropped and re-created at the end of the upgrade process. However, a lock exists on the stored procedure that prevents it from being dropped automatically. As a workaround, instructions are provided with 10 SP1 to drop the SDE_RELEASE_PROC manually before upgrading the geodatabase. If this is not done, the upgrade fails with the error message above.

Solution or Workaround

Drop the SDE_RELEASE_PROC stored procedure before upgrading the geodatabase.

Note:
Be sure there are no other connections to the database.

  1. Open a DB2 command window on the client machine on which the Upgrade Geodatabase geoprocessing tool or Python script is to be run.
  2. Connect to the geodatabase as the sde user.

    Code:
    connect to mydatabase user sde using mysde_password

  3. Drop the SDE_RELEASE_PROC stored procedure.

    Code:
    drop procedure sde_release_proc

  4. Once the stored procedure has been dropped, run the Upgrade Geodatabase geoprocessing tool or Python script to upgrade the geodatabase to 10 SP1.