When you change the definition of an SQL or an SPL procedure, the DDL statements in the script that the IBM® Procedure Designer generates to change the definition can vary. The data server on the connection, the version of the data server, and the information that you change in the procedure definition determine the DDL statements that the designer uses.
If the designer can use the ALTER PROCEDURE statement, all of the peripheral information in the procedure definition is preserved. If the designer must drop and re-create the procedure, all of the peripheral definitions, such as privilege grants and static references, are lost and you must specify them again.
Before you save the procedure definition, you can preview the DDL script in the Show Script view of the designer to see the statements that are being used.
When a procedure is dropped and re-created, information in the IBM Output Message Pane shows the changes that you made that are not supported for the ALTER PROCEDURE statement.
When you change the definition of an SQL procedure on a data connection to a server with DB2® for Linux®, UNIX®, and Windows® Version 9.5 or earlier, the procedure is dropped and re-created.
When you change the definition of an SQL procedure on a data connection to a server with DB2 for Linux, UNIX, and Windows, Version 9.7 or later, the designer uses the CREATE OR REPLACE PROCEDURE statement. Privileges that you grant when you create the procedure are preserved when the procedure definition is replaced. In other respects, the CREATE OR REPLACE PROCEDURE statement is semantically similar to the DROP and CREATE PROCEDURE statements.
If you change the signature of an external SQL procedure or a native SQL procedure, the procedure is dropped and re-created. The procedure signature includes the following information:
Note: Changing a parameter data type from TIMESTAMP to TIMESTAMP WITH TIMEZONE, or vice versa, changes the procedure signature.
The designer uses the ALTER PROCEDURE statement if you change only the value of one or more of the following procedure identification attributes:
External SQL procedures
If you change only the body of an SQL procedure and do not change the signature, the definition script contains an ALTER REBUILD or an ALTER REBUILD DEBUG statement, depending on whether the procedure has debugging support.
If you change only z/OS options of the SQL procedure and do not change the signature or the body of the procedure, an ALTER PROCEDURE DDL statement is used. You can change the following z/OS options:
If you change procedure identification attributes or z/OS options and you also you change the procedure body or debugging support, the designer uses two separate ALTER PROCEDURE statements. The first ALTER PROCEDURE statement changes procedure identification attributes and z/OS options. The second ALTER PROCEDURE statement changes the procedure body and debugging support.
Native SQL procedures
When you change the procedure body or debugging support, the designer uses an ALTER PROCEDURE statement.
When you change the definition of a native SQL procedure, the appropriate clause is added to the ALTER PROCEDURE statement, depending on the changes that you make. For example, if you add a version to the procedure, the ALTER PROCEDURE statement includes the ADD VERSION clause. Or if you change the definition of a procedure version, the ALTER PROCEDURE statement includes the REPLACE VERSION clause.
The designer uses a single ALTER PROCEDURE statement for all changes that the statement supports, including changes to the procedure identification attributes, body, debugging support, and version.
If you change the signature or the body of an SQL procedure, the procedure is dropped and re-created. The procedure signature includes the following information:
The designer uses an ALTER PROCEDURE statement if you change only the following information in an SQL procedure and you do not change the procedure signature or body:
If you change the signature or the body of an SPL procedure, the procedure is dropped and re-created. The procedure signature consists of the following information:
If you change only the routine modifiers of an SPL procedure, the designer uses an ALTER PROCEDURE statement to modify the procedure definition. You can make the following changes to the routine modifiers:
Developing IBM Database SQL and SPL Procedures | Changing SQL and SPL Procedure Definitions | DB2 SQL Procedures | Informix SPL Procedures | Managing Procedures, Functions, and Objects