The following statement shows an example of creating a stored procedure. Many options are available when creating stored procedures, but some of the main ones include a parameter list, the language, and its external program name. The definition is stored in the SYSIBM.SYSROUTINES catalog table, and the parameter definitions are stored in SYSIBM.SYSPARMS. The CREATE PROCEDURE statement creates a stored procedure:
CREATE PROCEDURE SYSPROC.MYSP1 ( IN PARM1 SMALLINT, INOUT PARM2 CHAR(10), OUT CODE INTEGER ) EXTERNAL NAME 'CERTSP1' -- Load module name LANGUAGE COBOL -- language PARAMETER STYLE GENERAL -- type of parameter list COLLID DB2SPS -- collection ID ASUTIME LIMIT 2000 -- maximum amount of SUs allowed STAY RESIDENT YES -- make memory resident DYNAMIC RESULT SETS 3 -- 3 result sets returned NOT DETERMINISTIC -- different results with each call MODIFIES SQL DATA -- Issues SQL WLM ENVIRONMENT MYAPPS -- WLM environment PROGRAM TYPE MAIN -- main, not subprogram RUN OPTIONS('TRAP(ON)') -- specifies the LE run options STOP AFTER 2 FAILURES -- stops after 2 failures COMMIT ON RETURN NO -- do not commit on exit
The ALTER PROCEDURE statement can be used to modify the options of a stored procedure:
ALTER PROCEDURE SYSPROC.MYSP1 STAY RESIDENT NO
The ALTER PROCEDURE command will take effect immediately if the stored procedure is not already active in the system. If the stored procedure is active, it will have to be stopped and restarted. If the stored procedure is running in a WLM environment, that environment should also be refreshed.
Removing Stored Procedures
Procedures are removed from the system by using the DROP PROCEDURE command, as in
DROP PROCEDURE MYSPAP RESTRICT
This will drop the stored procedure unless the rules identified by the required keyword RESTRICT hold true. RESTRICT prevents the procedure from being dropped if a trigger definition contains a CALL statement with the name of the procedure.
When a procedure is directly or indirectly dropped, all privileges on the procedure are also dropped. In addition, any plans or packages that are dependent on the procedure are made inoperative.
Stored-procedure names are implicitly or explicitly qualified by a schema name. If no schema name is used to qualify the name, it is qualified by the following rules.
When calling a stored procedure, the CURRENT PATH special register is used to identify unqualified procedure names. If no path is specified, the default path is used:
SYSFUN is a schema used for additional functions shipped from other servers in the DB2 product family. Although DB2 for z/OS does not have the SYSFUN schema, it can be useful to have SYSFUN in the path when doing distributed processing that involves a server that uses the SYSFUN schema.