The procedure name specifies the procedure being defined. The name is an SQL identifier that can be a maximum of 128 characters. It must begin with a letter and may be followed by additional letters, digits, or the underscore character “_”.

These are examples of valid procedure names:

UPDATE_EMPLOYEE_SALARY
READEMP

These examples are of unqualified procedure names. When a procedure name is unqualified, the procedure schema is determined by the CURRENT SCHEMA special register. The DB2 special register will be discussed later in this chapter. By default, the CURRENT SCHEMA is the authorization ID of the current user. Procedure names may also be qualified by a schema name explicitly. These are examples of valid qualified procedure names:

HRAPP.UPDATE_EMPLOYEE_SALARY
DB2ADMIN.READEMP

Similar to the procedure name, the schema name is an SQL identifier with a maximum of 128 characters or 10 characters on iSeries. The schema name cannot begin with SYS.

The qualified procedure name does not necessarily need to be unique in the database. The combination of qualified procedure name and number of parameters (procedure signature), however, must uniquely identify a procedure. This topic will be discussed in more detail in the following section.

The SPECIFIC clause is important when there is a requirement to define procedures with the same name but with a different number of parameters. To fully understand the purpose of this clause, we must discuss parameters.

The parameters for a procedure are defined in the CREATE PROCEDURE statement. The definition of each parameter consists of three parts:

The list of parameters is enclosed in parentheses “( )”, and each parameter definition is delimited by a comma “,”.

Figure 2.2 is an example of a partial CREATE PROCEDURE statement that defines the procedure and its parameters.

CREATE PROCEDURE update_employee_salary ( IN  P_EMPID INTEGER
                                        , IN  P_PERCENTINCR DECIMAL(4,2)
                                        , INOUT P_EMPCOUNT INTEGER
                                        , OUT P_UPDATED_SALARY DECIMAL(5,2))
. . .

A procedure may be defined without any parameters, and the parentheses can be omitted, as in this example:

CREATE PROCEDURE increase_salary LANGUAGE SQL BEGIN . . .

To allow back-level DB2 support, parentheses may be coded in the CREATE PROCEDURE statement.

CREATE PROCEDURE increase_salary() LANGUAGE SQL BEGIN . . .

On LUW and iSeries, it is possible to define multiple procedures with the same qualified name but different numbers of parameters. This is called overloading, and the procedures are referred to as overloaded procedures. Figures 2.3 and 2.4 show examples of overloaded procedures. zSeries does not support overloaded procedures. Therefore, examples run in zSeries have to be created with different procedure names.

CREATE PROCEDURE sum( IN  p_a INTEGER
                    , IN  p_b INTEGER
                    , OUT p_s INTEGER)
    LANGUAGE SQL
    SPECIFIC sum_ab                             -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                       -- applies to zSeries
BEGIN
    SET p_s = p_a + p_b;
END

CREATE PROCEDURE sum( IN  p_a INTEGER
                    , IN  p_b INTEGER
                    , IN  p_c INTEGER
                    , OUT p_s INTEGER)
    LANGUAGE SQL
    SPECIFIC sum_abc                            -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                       -- applies to zSeries
BEGIN
    SET p_s = p_a + p_b + p_c;
END

In the examples, two procedures have the same name, sum. The first procedure has three parameters, and the second has four parameters. When sum is called, DB2 determines which version of the procedure to execute based on the number of parameters. Note that each procedure is defined with a unique specific name. Specific names will be discussed in the next section.

The following statement can be used to invoke the SQL procedure:

CALL sum(100,200,?)

This call results in the sum procedure in Figure 2.3 to be executed because there are three parameters. Note that because the third parameter is an output parameter, a “?” must be specified in its place.

Executing the following statement invokes the sum procedure in Figure 2.4 because there are four parameters.

CALL sum(100,200,300,?)

If you attempt to call a procedure where there is no procedure defined in the database with the same number of parameters, an error occurs, as in this example:

CALL sum(100,200,300,400,?)

This call fails because a procedure named sum with five parameters does not exist.

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

The following example illustrates the use of SPECIFIC name when two procedures with the same name are defined. Consider the two sum procedures defined in Figures 2.3 and 2.4.

To drop the procedure sum, issue the following DROP PROCEDURE statement:

DROP PROCEDURE sum

This statement fails with SQLCODE -476 (SQLSTATE 42725) because the procedure is ambiguous. DB2 cannot determine which of the two procedures called sum should be dropped. To drop a particular version of the sum procedure, you must either specify the procedure parameters with the DROP PROCEDURE statement or use the DROP SPECIFIC PROCEDURE statement. These valid statements drop the procedure:

DROP PROCEDURE sum(INTEGER,INTEGER,INTEGER)
DROP SPECIFIC PROCEDURE sum_ab

By using DROP SPECIFIC PROCEDURE, DB2 knows that it should drop the procedure with the specific name sum_ab. The specific name can also be used with the COMMENT ON statement, as in this example:

COMMENT ON SPECIFIC PROCEDURE sum_abc IS 'THIS IS THE 3 PARM VERSION OF THE
PROCEDURE'

The specific name is an SQL identifier with a maximum length of 18 characters on LUW and 128 characters on iSeries. The name can be unqualified or qualified by a schema name. If it is qualified, it must use the same schema name as the procedure name. The specific name can be the same name as its procedure name. The qualified specific name must be unique among specific procedure names.

In LUW, if the specific name is not explicitly specified when creating a procedure, DB2 generates a unique name for the procedure. The generated unique name consists of SQL and a character timestamp:

SQLyymmddhhmmsshhn

On iSeries, if the specific name is not specified, DB2 uses the procedure name as the specific name. If a procedure already exists with that specific name, a unique name is generated using a portion of the procedure name and a sequence number.

The DYNAMIC RESULT SETS clause specifies the maximum number of result sets you are returning. Handling result sets is explained in detail in Chapter 5, “Understanding and Using Cursors and Result Sets,” and Chapter 8, “Nested SQL Procedures.”

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.

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.

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.

Special registers are memory registers that allow DB2 to provide information to an application about its environment. Refer to the section “DB2 Special Registers” for more information.

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.

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.

You can also make stored procedure calls from inline SQL such as SQL functions, triggers, and stand-alone code. Such a procedure will be executed as if it were created in the NEW SAVEPOINT LEVEL mode even if the OLD SAVEPOINT LEVEL was specified.

Save points are discussed in more detail in Chapter 10, “Leveraging DB2 Application Development Features.”

LANGUAGE SQL identifies this procedure as an SQL procedure, and indicates that the body of the procedure will be specified in the CREATE PROCEDURE statement body. LANGUAGE SQL is an optional clause for LUW. For iSeries and zSeries, LANGUAGE SQL must 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.

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.

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, and EBCDIC (for zSeries only).

On iSeries, the specification of the CCSID is at a more granular level. You specify the CCSID for each string parameter of the procedure (CHAR, VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, and DBCLOB). There are numerous options for specifying CCSID on iSeries. Refer to the iSeries SQL Reference for a listing of valid CCSIDs and the allowed conversions between these CCSIDs.

On zSeries, the CCSID can be specified at either the parameter level, the subsystem level in the field DEF ENCODING SCHEME on installation panel DSNTIPF, or at the SQL procedure level. The default is the value specified at the parameter level or the subsystem level. If specified at both the parameter level and the SQL procedure, they must be the same CCSID value for all parameters.

For SQL procedures, the logic of the procedure is contained in the SQL procedure body of the CREATE PROCEDURE statement. The SQL procedure body can consist of a single SQL statement or several SQL statements in the form of a compound SQL statement. The next section explains the details of writing the SQL procedure body.

It is always good practice to include comments in programs. DB2 allows two styles of comments:

Figure 2.6 are examples of comments used in an SQL procedure.

CREATE PROCEDURE proc_with_comments ( IN aaa INTEGER
                                    , OUT bbb INTEGER )
    LANGUAGE SQL
    SPECIFIC proc_with_comments                   -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
BEGIN
    /*
      Variables
    */
    DECLARE v_total INTEGER DEFAULT 0;
    SELECT 1 INTO v_total FROM SYSIBM.SYSDUMMY1;
END

The DECLARE statement is used to define variables within a compound SQL statement (see Figure 2.7). Each variable declaration consists of a name for the variable, a DB2 data type, and optionally a default value.

CREATE PROCEDURE proc_with_variables (IN p_empno VARCHAR(6))
    LANGUAGE SQL
    SPECIFIC proc_with_vars                     -- applies to LUW and iSeries
    -- WLM ENVIRONMENT <env>                    -- applies to zSeries
BEGIN
    DECLARE v_empno VARCHAR(6);
    DECLARE v_total, v_count INTEGER DEFAULT 0;
    SELECT empno INTO v_empno FROM employee WHERE empno = p_empno ;
END

Variable declarations must be specified at the beginning of a BEGIN/END block before any SQL procedural statements are defined. The CREATE PROCEDURE statement will fail if a DECLARE is found anywhere else.

You should ensure that the variables defined in your procedure are different from column names of tables that will be referenced in the procedure. Such a declaration is allowed but only causes confusion. It is recommended that you define a naming convention for your procedures that clearly differentiates variables and columns. In this book, the convention is to precede variables with a prefix of v_ and precede procedures parameters with a prefix of p_ to differentiate them from possible column names.

When variables are declared, they are initialized as NULL unless the DEFAULT clause is used to initialize the variable to a value. In Figure 2.7 the value of v_empno is null until it is set within the procedure body. The integer variables v_total and v_count are initialized to zero in the declaration.

As in any programming language, you can initialize or set variables. There are several ways to set variables in SQL procedures, and they are demonstrated in Figure 2.8.

CREATE PROCEDURE set_variables ()
    LANGUAGE SQL
    SPECIFIC set_variables                       -- applies to LUW and iSeries
    -- WLM ENVIRONMENT <env>                     -- applies to zSeries


BEGIN
    DECLARE v_rcount INTEGER;
    DECLARE v_max DECIMAL(9,2);
    DECLARE v_adate,v_another DATE;
    DECLARE v_total INTEGER DEFAULT 0;                -- (1)
    SET v_total = v_total + 1;                        -- (2)
    SELECT MAX(salary) INTO v_max FROM employee;      -- (3)
    VALUES CURRENT DATE INTO v_adate;                 -- (4)
    SELECT CURRENT DATE, CURRENT DATE
      INTO v_adate, v_another
      FROM SYSIBM.SYSDUMMY1;                          -- (5)
END

When declaring a variable, you can specify a default value using the DEFAULT clause as in Line (1).

Line (2) shows that a SET can be used to set a single variable.

Variables can also be set by executing a SELECT or FETCH statement in combination with INTO as shown in (3). Details on using FETCH can be found in Chapter 4, “Understanding and Using Cursors and Result Sets.”

Lines (4) and (5) show how the VALUES INTO statement can be used to evaluate a function or special register and assign the value to a variable. Special registers are discussed in more detail in the next section.

Recall that special registers are memory registers that allow DB2 to provide information to an application about its environment. They can be referenced in SQL statements. The most commonly used special registers are

Special registers can be categorized as updateable and non-updateable. For example, CURRENT SCHEMA and CURRENT PATH are both updateable registers. CURRENT TIMESTAMP is an example of a non-updateable register. To obtain the value of the register, use the SELECT statement as shown:

SELECT CURRENT SCHEMA FROM SYSIBM.SYSDUMMY1;

Use the SET command to update the updateable registers such as the current schema:

SET CURRENT SCHEMA = DB2ADMIN

When invoking SQL procedures with unqualified names, the CURRENT PATH special register is used to resolve the requested SQL procedure. The path is a list of schemas which DB2 searches to locate a procedure. To obtain the CURRENT PATH special register, connect to the database and simply issue this command

SELECT CURRENT PATH FROM SYSIBM.SYSDUMMY1;

The default setting for the PATH register in LUW looks like

"SYSIBM", "SYSFUN", "SYSPROC", "DB2ADMIN"

The default PATH is made up of system schemas followed by the current user connected to the database. On LUW, SYSIBM, SYSFUN, and SYSPROC are the system schemas. With the previously shown PATH, DB2 first searches for the procedure in schema SYSIBM, then SYSFUN, then SYSPROC, and lastly DB2ADMIN. If there are overloaded procedures in both SYSPROC and DB2ADMIN, the SYSPROC one will be used. Sometimes you might want to use the DB2ADMIN procedure instead. In that case, set the CURRENT PATH to customize the search order of the schemas:

SET CURRENT PATH = DB2ADMIN,SYSIBM,SYSFUN,SYSPROC

Figure 2.9 shows the use of several special registers.

CREATE PROCEDURE registersample ( OUT p_start TIMESTAMP
                                , OUT p_end   TIMESTAMP
                                , OUT p_c1    TIMESTAMP
                                , OUT p_c2    TIME
                                , OUT p_user  CHAR(20))
    LANGUAGE SQL
    SPECIFIC registersample                       -- applies to LUW and iSeries
 -- WLM ENVIRONMENT <env>                         -- applies to zSeries
BEGIN
    CREATE TABLE datetab (c1 TIMESTAMP,c2 TIME,c3 DATE);


    VALUES CURRENT TIMESTAMP INTO p_start;               -- (1)
    INSERT INTO datetab VALUES( CURRENT TIMESTAMP
                               , CURRENT TIME
                               , CURRENT DATE + 3 DAYS); -- (2)
    SELECT c1,c2 INTO p_c1,p_c2 FROM datetab;
    VALUES CURRENT TIMESTAMP INTO p_end;
    SET p_user = USER;                                   -- (3)
    DROP TABLE datetab;
END

The procedure registersample creates a table, datetab, to demonstrate how special registers are used with SQL statements. This table is dropped at the end so that the procedure can be run repeatedly.

On Line (1), the values statement is used to set the variable p_start to the current timestamp.

Line (2) shows several special registers being used within a single SQL statement to retrieve the date or time. Here, the time portion of the CURRENT TIMESTAMP special register will be the same as the value of the CURRENT TIME special register, and date portion of the CURRENT TIMESTAMP special register will be the same as the value of CURRENT DATE. This statement also demonstrates the use of built-in functions. Column c3 will receive a date that is three days from the current date. You can find more details on using built-in functions such as DATE, TIME, and TIMESTAMP in the DB2 SQL Reference.

The p_user variable is set to the authorization ID of the currently connected user on Line (3).

The following list includes other special registers that are available. More information about them can be found in the DB2 SQL Reference for the corresponding platform.

This option is provided only for compatibility with other products in the DB2 family and is not used by SQL procedures in DB2 UDB for iSeries.

This clause can be specified to tell the database manager to commit the transaction when returning from the SQL procedure call. The default value is NO. If a value of YES is specified, the transaction is committed only upon successful completion of the procedure. If an error is encountered, a COMMIT is not issued.

The SET OPTION statement is used to specify processing options that will be used to create the procedure. For example, a procedure can be created for debug by specifying the following:

SET OPTION DBGVIEW = *SOURCE

DBGVIEW identifies that debug information is to be provided by the compiler. The *SOURCE specifies that the compiled object is to be debugged using the program source code.

Chapter 12, “Performance Tuning,” discusses the SET OPTION statement in more detail. For a complete list of options, refer to the SET OPTION statement information found in the Statements chapter of the DB2 UDB for iSeries SQL Reference.

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

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.

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

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

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

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:

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

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.

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: