Error: db_execute_ddlsql SQL Stmt: DROP PROCEDURE SDE.sde_release_proc ERROR in creating system stored procedures
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.
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."
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.
Be sure there are no other connections to the database.
- Open a DB2 command window on the client machine on which the Upgrade Geodatabase geoprocessing tool or Python script is to be run.
- Connect to the geodatabase as the sde user.
connect to mydatabase user sde using mysde_password
- Drop the SDE_RELEASE_PROC stored procedure.
drop procedure sde_release_proc
- Once the stored procedure has been dropped, run the Upgrade Geodatabase geoprocessing tool or Python script to upgrade the geodatabase to 10 SP1.