DB2 UDB for iSeries Considerations

The CREATE PROCEDURE statement syntax for DB2 UDB for iSeries is very similar to the CREATE PROCEDURE statement syntax for DB2 UDB for distributed platforms shown in Figure 2.1. In iSeries, the following is true:

• The LANGUAGE SQL clause is mandatory and must be listed right after the parameter declaration in the procedure.

• The PARAMETER CCSID clause is not supported, but specification of the encoding scheme can be specified with the data type of the parameters.

• The EXTERNAL ACTION clause is not part of the syntax.

• An option to specify whether the procedure runs FENCED or NOT FENCED is supported for compatibility with other products in the DB2 family.

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

• An optional SET OPTION clause is supported to specify precompile options.

For completeness, Figure 2.11 shows the syntax diagram for the iSeries CREATE PROCEDURE statement, followed by a more detailed explanation of some of the clauses.

Figure 2.11. CREATE PROCEDURE statement syntax for DB2 UDB for iSeries.

 >>-CREATE PROCEDURE---procedure-name ---------------------------> >--+----------------------------------------------------------+->    '--(--+----------------------------------------------+--)--'->          |  .-,---------------------------------------. |          |  V .-IN----.                               | |          '----+-------+---parameter-name--data-type---+-'               +-OUT---+               '-INOUT-' >--LANGUAGE SQL-----------------------------------------------+->    .-NOT DETERMINISTIC--.     .-MODIFIES SQL DATA--. >--+--------------------+-----+--------------------+------------>    '-DETERMINISTIC------'     +-CONTAINS SQL-------+                               '-READS SQL DATA-----'    .-CALLED ON NULL INPUT-. >--+----------------------+-----+--------------------------+---->                                 '-SPECIFIC--specific-name--'    .-DYNAMIC RESULT SETS 0---------.     .-FENCED-----. >--+-------------------------------+-----+------------+--------->    '-DYNAMIC RESULT SETS--integer--'     '-NOT FENCED-'    .-COMMIT ON RETURN NO--.     .-OLD SAVEPOINT LEVEL-. >--+----------------------+-----+---------------------+--------->    '-COMMIT ON RETURN YES-'     '-NEW SAVEPOINT LEVEL-'    .-INHERIT SPECIAL RESGISTERS-. >--+----------------------------+-------------------------------> >--+---------------------+---SQL-routine-body------------------><    '-SET OPTION-statement-' 

FENCED and NOT FENCED

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.

COMMIT ON RETURN

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.

Note

If you specify YES for this clause, then any cursors that are opened in the SQL procedure for the purpose of returning a result set must be declared using the WITH HOLD option. For more details on cursors, refer to Chapter 5., "Understanding and Using Cursors and Result Sets."

SET OPTION Statement

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.