Defining Stored Procedures

Team-Fly    

 
DB2 Universal Database for OS/390 v7.1 Application Certification Guide
By Susan Lawson
Table of Contents
Chapter 8.  Stored Procedures

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 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.

Schema Qualification

Stored 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:

  • 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:

  1. SYSIBM

  2. SYSFUN

  3. SYSPROC

  4. value of CURRENT SQLID

NOTE

graphics/note_icon.jpg

SYSFUN is a schema used for additional functions shipped from other servers in the DB2 product family. Although DB2 for OS/390 and 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.



Team-Fly    
Top


DB2 Universal Database for OS. 390 v7. 1 Application Certification Guide
DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)
ISBN: 0131007718
EAN: 2147483647
Year: 2002
Pages: 163
Authors: Susan Lawson

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net