DB2 UDB for zSeries Considerations


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:

  • The LANGUAGE SQL clause is mandatory.

  • The SPECIFIC, SAVEPOINT LEVEL, and EXTERNAL ACTION clauses are not part of the syntax.

  • PARAMETER CCSID has an extra possible value of EBCDIC. In addition, the CCSID clause can be included with each stored procedure parameter.

  • The FENCED clause is the default value and an optional clause. There is no NOT FENCED option.

  • A COMMIT ON RETURN clause can be specified to commit a transaction when returning from an SQL procedure.

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

FENCED

This 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 DBINFO

NO 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-id

NO 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 ENVIRONMENT

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

ASUTIME

This 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 RESIDENT

This option indicates the SQL procedure will remain in memory when it ends (YES) or not (NO).

PROGRAM TYPE

This option specifies whether the SQL procedure will run as a subroutine (SUB) or as a main program (MAIN).

SECURITY

This option indicates how the SQL procedure interacts with an external security product to control access to non-SQL resources. Possible values include the following:

  • DB2. The SQL procedure does not require a special external security environment. If it accesses resources that an external security product protects, the access is performed using the authorization ID associated with the SQL procedure address space. DB2 is the default.

  • USER. An external security environment should be established for the SQL procedure. If the SQL procedure accesses resources that the external security product protects, the access is performed using the authorization ID of the user who invoked the SQL procedure.

  • DEFINER. An external security environment should be established for the SQL procedure. If the SQL procedure accesses resources that the external security product protects, the access is performed using the authorization ID of the owner of the SQL procedure.

RUN OPTIONS

This option indicates the Language Environment runtime options that are to be used for the SQL procedure.

COMMIT ON RETURN

This 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 FAILURE

This option indicates whether the SQL procedure is to be put in a stopped state after some number of failures. Possible values include the following:

  • STOP AFTER SYSTEM DEFAULT FAILURES. The value of field MAX ABEND COUNT on installation panel DSNTIPX is used to determine the number of failures allowed before placing the SQL procedure in stopped state. This is the default.

  • STOP AFTER nn FAILURES. Indicates that after nn failures, the SQL procedure should be placed in a stopped state. The value nn can be an integer from 1 to 32767.

  • CONTINUE AFTER FAILURE. Specifies that this SQL procedure should not be placed in a stopped state after any failure.



    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

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