|
In DB2 UDB for zSeries, the CREATE PROCEDURE statement defines a procedure in the DB2 subsystem. Figure 2.12 shows the syntax diagram of such a statement. It is very similar to the syntax shown in Figure 2.1 for DB2 for LUW. In zSeries, the following is true:
Figure 2.12. CREATE PROCEDURE statement syntax in DB2 UDB for zSeries.>>-CREATE PROCEDURE---------------------------------------------> >----procedure-name--(--+----------------------------------------------+---)-> | .-,----------------------------------------. | | V .-IN-----. | | '----+-------+---parameter-name--data-type---+-' +-OUT---+ '-INOUT-' .-DYNAMIC RESULT SETS 0--------. .-MODIFIES SQL DATA-. >--+------------------------------+--*--+-------------------+---> '-DYNAMIC RESULT SETS--integer-' +-CONTAINS SQL------+ '-READS SQL DATA----' .-NOT DETERMINISTIC-. .-CALLED ON NULL INPUT-. >--*--+-------------------+--*--+----------------------+--*-----> '-DETERMINISTIC-----' .-INHERIT SPECIAL REGISTERS-. >--+---------------------------+--*-------LANGUAGE SQL----------> '-DEFAULT SPECIAL REGISTERS-' .-FENCED-. .-NO DBINFO-. >--+------------------------------+---+--------+---+-----------+-> '-PARAMETER CCSID--+-ASCII---+-' '-EBCDIC -' '-UNICODE-' .-NO COLLID ------------. >--+-----------------------+---+-------------------------------+-> '-COLLID Collection-Id -' '-WLM ENVIRONMENT--+--name----+-' '-(name,*)-' .-ASUTIME NO LIMIT ------. .-STAY RESIDENT NO--. >--+------------------------+----+-------------------+-----------> '-ASUTIME LIMIT integer--' +-STAY RESIDENT YES-' .-PROGRAM TYPE MAIN--. .-SECURITY DB2-----. >--+--------------------+----+------------------+----------------> '-PROGRAM TYPE SUB---' +-SECURITY USER ---+ '-SECURITY DEFINER-' .-COMMIT ON RETURN NO---. >--+-------------------------------+----+-----------------------+-> '-RUN OPTIONS run time options--' '-COMMIT ON RETURN YES--' .-STOP AFTER SYSTEM DEFAULT FAILURES --. >--+--------------------------------------+----------------------> +-STOP AFTER integer FAILURES ---------+ '-CONTINUE AFTER FAILURE---------------' >-----| SQL-procedure-body |------------------------------------>< SQL-procedure-body: |---SQL-procedure-statement-------------------------------------| FENCEDThis option specifies that the SQL procedure runs in an external address space to prevent user programs from corrupting the DB2 engine storage. This clause is optional and is the default value in DB2 UDB for zSeries. There is no NOT FENCED option. Note that SQL procedures created in DB2 UDB for LUW are all UNFENCED; this is the default and the only behavior. NO DBINFONO DBINFO indicates that specific internal information known to DB2 will not be passed to the SQL procedure. NO DBINFO is the default for SQL procedures. NO COLLID or Collid collection-idNO COLLID indicates that the package collection for the SQL procedure is the same as the package collection of the calling program. COLLID collection-id provides the collection ID to be used for the SQL procedure. WLM ENVIRONMENTThis option identifies the Workload Manager (WLM) environment where the SQL procedure is to run if defined as a WLM-established SQL procedure. If WLM ENVIRONMENT is not specified, the default WLM environment specified at installation time is used. ASUTIMEThis option indicates whether the SQL procedure will use unlimited processor time for a single invocation of an SQL procedure (NOLIMIT) or a specific amount of processor time (LIMIT integer). STAY RESIDENTThis option indicates the SQL procedure will remain in memory when it ends (YES) or not (NO). PROGRAM TYPEThis option specifies whether the SQL procedure will run as a subroutine (SUB) or as a main program (MAIN). SECURITYThis option indicates how the SQL procedure interacts with an external security product to control access to non-SQL resources. Possible values include the following:
RUN OPTIONSThis option indicates the Language Environment runtime options that are to be used for the SQL procedure. COMMIT ON RETURNThis option indicates whether a COMMIT is automatically issued upon return from the SQL procedure, regardless of whether a COMMIT or ROLLBACK is explicitly coded in the SQL procedure. NO is the default. STOP AFTER SYSTEM DEFAULT FAILURES or CONTINUE AFTER FAILUREThis option indicates whether the SQL procedure is to be put in a stopped state after some number of failures. Possible values include the following:
|
|