Building SQL Procedures in DB2 UDB for iSeries

DB2 UDB for iSeries supports two basic types of procedures:

• External procedures

• SQL procedures

External procedures are programs or service programs written in a language such as C or COBOL. The supported languages are C, C++, CL, COBOL, COBOLLE, FORTRAN, Java, PLI, REXX, RPG, and RPGLE. The CREATE PROCEDURE statement corresponding to an external procedure is used basically to name the procedures, identify the parameters used by the procedures and their corresponding data types, and the location of where to find the compiled program. The statement will not compile the program for you.

SQL procedures are written entirely in SQL. SQL procedures are the focus of this book, and hence this section focuses on building SQL procedures. SQL is not just used for writing SQL procedures; it is also used to write functions and triggers. Although these routines (SQL procedures, functions, and triggers) are written entirely in SQL, when the CREATE statement for a SQL routine is issued a temporary C source file with embedded SQL statements is created. The source file is then prepared and compiled. All of this is done automatically for you. The iSeries integrated operating environment is packaged with an internal C compiler; hence, automatic compilation via the CREATE statement is possible.

The SET OPTION clause of the CREATE PROCEDURE statement (Chapter 2, "Basic SQL Procedure Structure") is used to specify processing options used to create the SQL procedure. Figure C.2 shows a syntax diagram of all the valid SET OPTION processing options when creating SQL procedures.

Figure C.2. Simplified SET OPTION statement syntax diagram.

                .-,------------------------------------.                V                                      | >>-SET OPTION----+-ALWBLK = --alwblk-option---------+-+--------><                  +-ALWCPYDTA = --alwcpydta-option---+                  +-COMPILEOPT = --compile-option----+                  +-COMMIT = --commit-option---------+                  +-DATFMT = --datfmt-option---------+ *                  +-DATSEP = --datsep-option---------+ *                  +-DBGVIEW = --dbgview-option-------+                  +-DECMPT = --decmpt-option---------+                  +-DECRESULT = --decresult-option---+                  +-DLYPRP = --dlyprp-option---------+                  +-DYNUSRPRF = --dynusrprf-option---+                  +-EVENTF = --eventf-option---------+                  +-LANGID = --langid-option---------+                  +-OPTLOB = --optlob-option---------+                  +-OUTPUT = --output-option---------+                  +-RDBCNNMTH = --rdbcnnmth-option---+                  +-SQLCA = --sqlca-option-----------+                  +-SQLCURRULE = --sqlcurrule-option-+                  +-SQLPATH = --sqlpath-option-------+                  +-SRTSEQ = --srtseq-option---------+                  +-TGTRLS = --tgtrls-option---------+                  +-TIMFMT = --timfmt-option---------+ *                  +-TIMSEP = --timsep-option---------+ *                  '-USRPRF = --usrprf-option---------' 

Note

Refer to the CREATE PROCEDURE and SET OPTION statements in the DB2 UDB for iSeries SQL Reference for a detailed description on each of the options. Some of the more commonly used ones are described here.

The options shown in Table C.2 are also valid for SQL user-defined functions and triggers, although with triggers, the DATFMT, DATSEP, TIMFMT, and TIMSEP options cannot be specified.

Table C.2. Processing Option Descriptions

Option	Valid Choices	Description
ALWBLK	*READ, *NONE, *ALLREAD	Specify if row blocking can be used and the degree to which it is used.
ALWCPYDTA	*YES, *NO, *OPTIMIZE	Specifies if a copy of the data can be used for SELECT statements.
COMMIT	*CHG, *NONE, *CS, *ALL, *RR	Specifies the isolation level to use.
COMPILEOPT	*NONE, compile-string	Specifies additional parameters to use for the compiler command.
DATFMT	*JOB, *ISO, *EUR, *USA,*MDY, *DMY, *YMD, *JUL	Specifies format for date *JIS, columns.
DATSEP	*JOB, *SLASH, '/', *PERIOD, *COMMA, ',', *DASH, '-', *BLANK, ' '	Specifies separator for date columns.
DBGVIEW	*NONE, *SOURCE, *STMT, *LIST	Specifies to the compiler what type of debug information is required.
DECMPT	*PERIOD, *COMMA, *SYSVAL, *JOB	Specifies the format for the decimal point.
DECRESULT	(max precision, max scale, min divide scale)	Specifies the maximum precision to use for decimal operations.
DLYPRP	*YES, *NO	Specifies whether validation for dynamic statements is delayed.
DYNUSRPRF	*OWNER, *USER	Specifies which user profile to use for dynamic statements.
EVENTF	*YES, *NO	Specifies whether an event file is generated.
LANGID	*JOB, *JOBRUN, language-ID	Specifies the language identifier to use associated with SRTSEQ.
OPTLOB	*YES, *NO	Specifies whether or not to optimize LOB access through DRDA.
OUTPUT	*NONE, *PRINT	Specify whether or not to print precompiler and compiler listings.
RDBCNNMTH	*DUW, *RUW	Specifies whether to use distributed or remote unit of work for the connection.
SQLCA	*YES, *NO	Specifies whether or not to set fields in the SQLCA after each statement.
SQLCURRULE	*DB2, *STD	Specifies which SQL semantic option to use.
SLQPATH	*LIBL, path	Specifies function path for static SQL statements.
SRTSEQ	*JOB, *HEX, *JOBRUN, *LANGIDUNQ, *LANGIDSHR, libname/startseq-table-name	Specifies which sort sequence to use for string comparisons.
TGTRLS	VxRxMx	Specifies the release on which the routine will be deployed.
TIMFMT	*HMS, *ISO, *EUR, *USA, *JIS	Specifies format for time data.
TIMSEP	*JOB, *COLON, ':', *PERIOS, '.', *COMMA, ',', *BLANK, ' '	Specifies separator for time data.
USRPRF	*OWNER, *USER, *NAMING	Specifies the user profile to implement at execution time.

If you have the DB2 CLP installed and remote access to a DB2 UDB iSeries database through DB2 CONNECT, you can use the CLP to issue commands against the database as described in he previous section.

For other methods of issuing SQLfor example, the CREATE PROCEDURE statementssee Appendix A, "Getting Started with DB2."