Defining Stored Procedures The following statement shows an example of creating a stored procedure. There are many options when creating stored procedures, but some of the main ones include a parameter list, the language, and its external program name . The actual 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 -- requires a WLM environment PROGRAM TYPE MAIN -- main, not subprogram RUN OPTIONS('TRAP(ON)') -- specifies the LE run options 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 ProceduresProcedures 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. Schema QualificationStored procedure names are implicitly or explicitly qualified by a schema name. If no schema name is used to qualify the name, then 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:
NOTE
|
Team-Fly |
Top |