DB2 Procedure

zyllf2009

1. Specific Name

SPECIFIC is an optional clause that defines a unique name for a procedure. Specific names are particularly useful when there are multiple procedures defined with the same name but have a different number of parameters (also known asoverloaded procedures, as discussed in the previous section). In this case, each procedure would be given a different specific name which would be used to drop or comment on the stored procedure. Attempting to drop an overloaded procedure using only the procedure name would result in ambiguity and error.

2. Dynamic result sets

3. Data access classification

CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA

The SQL data access indication clause restricts the type of SQL statements that can be executed by the procedure. The default, MODIFIES SQL DATA, is the least restrictive and indicates that any supported SQL statements can be executed.

When CONTAINS SQL is specified, then only statements that do not read or modify data are allowed in the procedure. Examples of such statements are PREPARE, the SET special register, and SQL control statements.

READS SQL DATA can be specified if the procedure contains only statements that do not modify SQL data. Refer to the SQL Reference of the corresponding platform for statements allowed in the SQL procedure for each access indicator.

4. Parameter CCSID

CCSID stands for Coded Character Set ID. This clause specifies the encoding scheme used for all string data passed into and out of the stored procedure for LUW and zSeries. Possible values are ASCII, UNICODE, andEBCDIC (for zSeries only).

5. DETERMINISTIC or NOT DETERMINISTIC

This clause allows you to specify the procedure as DETERMINISTIC if it returns the same results for each invocation of identical input parameters. You can also specify NOT DETERMINISTIC, the default, if the results depend on the input values and/or other values which may change, such as the current date or time. Identifying a procedure as DETERMINISTIC allows DB2 to perform additional optimizations to improve performance because DB2 can just call it once, cache the result, and reuse it.

6. New savepoint level

　　This clause is available only in LUW and iSeries. A save point level refers to the scope of reference for any save point related statements. All save point names in the same save point level must be unique.
　　The OLD SAVEPOINT LEVEL means that any SAVEPOINT statements issued within the procedure are created in the same save point level as the caller of the procedure. Thus, any save point created inside the stored procedure must not have the same name as those defined at the caller. This is the default behavior.
　　The NEW SAVEPOINT LEVEL, on the other hand, creates a new save point level when the stored procedure is called. Any save points set within the procedure are created at a level that is nested deeper than the level at which this procedure was invoked. Therefore, names of any new save point set within the procedure will not conflict with any existing save points.

7. External action

This clause is only available on LUW. If the SQL procedure takes some action that changes the state of an object not managed by DB2, specify the EXTERNAL ACTION. Otherwise, use NO EXTERNAL ACTION so that DB2 can use certain optimizations that assume the procedure has no external impact.

8. Inherit special registers

Special registers are memory registers that allow DB2 to provide information to an application about its environment.

INHERIT SPECIAL REGISTERS is an optional clause and indicates that updateable special registers in the procedure will inherit their initial values from the environment of the invoking statement. Special register inheritance is the default behavior on all platforms. such asCURRENT DATE.

9. Called on null input

This clause indicates that the procedure will always be called even if its input parameters are null. This behavior is the default, and is the only value that can be specified. This clause is optional and is usually left out.

10. LANGUAGE SQL
　　LANGUAGE SQL identifies this procedure as an SQL procedure, and indicates that the body of the procedure will be specified in theCREATE PROCEDURE statement body. LANGUAGE SQL is an optional clause for LUW. For iSeries and zSeries, LANGUAGE SQLmust be specified. Furthermore, on iSeries, it must be specified as the first clause.
　　The LANGUAGE keyword is required when creating procedures in other languages such as Java or C.
　　Note: To increase portability, always use the LANGUAGE SQL clause and ensure it is the first clause.