Defining Stored Procedures

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:


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


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 Qualification

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.

  • If the CREATE PROCEDURE statement is embedded in a program, the schema name is the authorization ID specified in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not specified, the schema name is the owner of the plan or package.

  • If the CREATE PROCEDURE statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

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:




  4. Value of CURRENT SQLID


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.

DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson © 2008-2017.
If you may any questions please contact us: