Deleting Stored Procedure Program Objects

Resolving The Problem

It is often confusing as to when deleting the program object of a stored procedure will also delete the registration information (in other words, information is SYSPROCS).

If the procedure is created as an SQL procedure (versus an EXTERNAL procedure), the registration information is deleted when the program object is deleted. This is true even if the program object is moved to a different library and then the CREATE statement is used to register it in that library.

If it is an EXTERNAL stored procedure, the registration information is not deleted when the program object is deleted. It will only be deleted with a DROP statement.

The recommended steps for changing a program that also has an associated stored procedure are as follows:

1. DROP the procedure.
2. DELETE the program (or RECOMPILE with REPLACE *YES).
3. CREATE the procedure.

If you have an SQL stored procedure and do a DROP or DELETE of the program object, the same results occur. The program object and registration information are both gone.

Also note that if you copy an SQL procedure program object into a library and compile over it, it becomes an EXTERNAL function so the registration information is treated separately from the program object.