Intelligent Enterprises of the 21st Century

2017-07-07 02:10:07

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

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

Table C.2. Processing Option Descriptions

Категории