CREATE FUNCTION (compiled SQL scalar)

The CREATE FUNCTION (compiled SQL scalar) statement defines a compiled SQL scalar function at the current server and specifies the source statements for the function. The body of the function is written in the SQL procedural language. The function returns a single value each time it is invoked.

A package is created for a compiled SQL scalar function.

For compiled SQL scalar functions, you can define multiple versions of the function. Use CREATE FUNCTION (compiled SQL scalar) to define the initial version, and ALTER FUNCTION to define subsequent versions. For information about the SQL statements that are supported in SQL functions, refer to SQL-procedure-statement.

Invocation

For a compiled SQL function, this statement can only be dynamically prepared but the DYNAMICRULES run behavior must be specified implicitly or explicitly.

Authorization

The privilege set defined below must include at least one of the following:

  • The CREATEIN privilege on the schema and the required authorization to add a new package or a new version of an existing package, depending on the value of the BIND NEW PACKAGE field on installation panel DSNTIPP
  • SYSADM or SYSCTRL authority
  • System DBADM
  • Start of changeInstallation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)End of change

The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

Start of changeIf the authorization ID that is used to create the function has the installation SYSADM authority or the installation SYSOPR authority and if the current SQLID is set to SYSINSTL, the function is identified as system-defined function.End of change

If a user-defined type is referenced (as the data type of a parameter or an SQL variable), the privilege set must also include at least one of the following:

  • Ownership of the user-defined type
  • The USAGE privilege on the user-defined type
  • SYSTEM DBADM authority
  • DATAACCESS AUTHORITY
  • SYSADM authority

If the function uses a table as a parameter, the privilege set must also include at least one of the following:

  • Ownership of the table
  • The SELECT privilege on the table
  • DATAACCESS authority
  • SYSADM authority

Additional authorization may be required on the SYSDUMMYx tables depending on the content of the function definition. See SYSDUMMYx tables.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the package owner is a role, the implicit schema match does not apply and this role needs to include one of the previously listed conditions.

If the statement is dynamically prepared and is not running in a trusted context for which the ROLE AS OBJECT OWNER clause is specified, the privilege set is the set of privileges that are held by the SQL authorization ID of the process. If the schema name is not the same as the SQL authorization ID of the process, one of the following conditions must be met:

  • The privilege set includes SYSADM or SYSCTRL authority.
  • The SQL authorization ID of the process has the CREATEIN privilege on the schema.
Start of changeWhen CREATE FUNCTION is issued in a trusted context that has the ROLE AS OBJECT OWNER clause, the package owner is determined as follows:
  • If the PACKAGE OWNER option is not specified, the role associated with the binder becomes the package owner.
  • If the PACKAGE OWNER option is specified, the role specified in the PACKAGE OWNER option becomes the package owner. In a trusted context, the PACKAGE OWNER specified must be a role.
End of change

If you specify the WLM ENVIRONMENT FOR DEBUG MODE clause, RACF® or an external security product is invoked to check the required authority for defining programs in the WLM environment. If the WLM environment access is protected in RACF, the privilege set must include the required authority.

At least one of the following additional privileges is required if the SECURED option is specified

  • SECADM authority
  • CREATE_SECURE_OBJECT privilege

Syntax

Read syntax diagramSkip visual syntax diagramCREATE FUNCTIONfunction-name( ,parameter-declaration )function-definitionWRAPPEDobfuscated-statement-text

parameter-declaration:

Read syntax diagramSkip visual syntax diagramparameter-nameparameter-type

parameter-type:

Read syntax diagramSkip visual syntax diagramdata-typeTABLE LIKEtable-nameview-nameAS LOCATOR

data-type:

Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-namearray-type-name

built-in-type:

built-in-type:

Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)( integer, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDBITDATACHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDDATAGRAPHIC(1)( integer)VARGRAPHIC(integer)DBCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEBINARY(1)( integer)BINARY VARYINGVARBINARY( integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONEROWIDXML

function-definition

Read syntax diagramSkip visual syntax diagram RETURNS data-type2 VERSION V1VERSIONroutine-version-idoption-list SQL-routine-body

SQL-routine-body:

Read syntax diagramSkip visual syntax diagramSQL-control-statement

option-list: (The options in the option-list can be specified in any order, but each one can only be specified one time)

Read syntax diagramSkip visual syntax diagram LANGUAGE SQL SPECIFICspecific-name NOT DETERMINISTICDETERMINISTIC EXTERNAL ACTIONNO EXTERNAL ACTION READS SQL DATACONTAINS SQLMODIFIES SQL DATA CALLED ON NULL INPUTRETURNS NULL ON NULL INPUT STATIC DISPATCH ALLOW PARALLELDISALLOW PARALLEL DISALLOW DEBUG MODEALLOW DEBUG MODEDISABLE DEBUG MODE PARAMETER CCSID ASCIIPARAMETER CCSID EBCDICPARAMETER CCSID UNICODE QUALIFIERschema-name PACKAGE OWNERauthorization-name ASUTIME NO LIMITASUTIME LIMITinteger INHERIT SPECIAL REGISTERSDEFAULT SPECIAL REGISTERS WLM ENVIRONMENT FOR DEBUG MODEname CURRENT DATA NOCURRENT DATA YES DEGREE 1DEGREE ANY CONCURRENT ACCESS RESOLUTIONUSE CURRENTLY COMMITTEDCONCURRENT ACCESS RESOLUTIONWAIT FOR OUTCOME DYNAMICRULES RUNDYNAMICRULES BINDDYNAMICRULES DEFINEBINDDYNAMICRULES DEFINERUNDYNAMICRULES INVOKEBINDDYNAMICRULES INVOKERUN APPLICATION ENCODING SCHEME ASCIIAPPLICATION ENCODING SCHEME EBCDICAPPLICATION ENCODING SCHEME UNICODE WITHOUT EXPLAINWITH EXPLAIN WITHOUT IMMEDIATE WRITEWITH IMMEDIATE WRITE ISOLATION LEVEL CSISOLATION LEVEL RSISOLATION LEVEL RRISOLATION LEVEL UR OPTHINT''OPTHINTstring-constant QUERY ACCELERATION NONEQUERY ACCELERATION ENABLEQUERY ACCELERATION ENABLE WITH FAILBACKQUERY ACCELERATION ELIGIBLEQUERY ACCELERATION ALLGET_ACCEL_ARCHIVE NOGET_ACCEL_ARCHIVE YESACCELERATION WAITFORDATAnnnn.mACCELERATORaccelerator-name SQL PATH,schema-nameSYSTEM PATHSESSION USERUSER REOPT NONEREOPT ALWAYSREOPT ONCE VALIDATE RUNVALIDATE BIND ROUNDING DEC_ROUND_CEILINGROUNDING DEC_ROUND_DOWNROUNDING DEC_ROUND_FLOORROUNDING DEC_ROUND_HALF_DOWNROUNDING DEC_ROUND_HALF_EVENROUNDING DEC_ROUND_HALF_UPROUNDING DEC_ROUND_UP DATE FORMAT ISODATE FORMAT EURDATE FORMAT USADATE FORMAT JISDATE FORMAT LOCAL DECIMAL(15)DECIMAL(31)DECIMAL(15, s)DECIMAL(31, s) FOR UPDATE CLAUSE REQUIREDFOR UPDATE CLAUSE OPTIONAL TIME FORMAT ISOTIME FORMAT EURTIME FORMAT USATIME FORMAT JISTIME FORMAT LOCAL NOT SECUREDSECURED BUSINESS_TIME SENSITIVEYESBUSINESS_TIME SENSITIVENOSYSTEM_TIME SENSITIVEYESSYSTEM_TIME SENSITIVENOARCHIVE SENSITIVEYESARCHIVE SENSITIVENOAPPLCOMPATapplcompat-levelCONCENTRATE STATEMENTS OFFCONCENTRATE STATEMENTS WITH LITERALS

Description

function-name
Names the function. If function-name already exists with the specified signature, an error is returned even if VERSION is specified with a routine-version-id that is different from any existing version identifier for the function that is specified in function-name. For more information, see Choosing the schema and function names and Determining the uniqueness of functions in a schema in CREATE FUNCTION.
(parameter-declaration,…)

Specifies the number of input parameters of the function and the name and data type of each parameter. Each parameter-declaration specifies an input parameter for the function. A function can have zero or more input parameters. There must be one entry in the list for each parameter that the function expects to receive. All of the parameters for a function are input parameters and are nullable. If the function has more than 30 parameters, only the first 30 parameters are used to determine if the function is unique.

parameter-name
Specifies the name of the input parameter. The name is an SQL identifier, and each name in the parameter list must not be the same as any other name.
data-type
Specifies the data type of the input parameter. The data type can be a built-in data type or a user-defined type.
built-in-type
The data type of the input parameter is a built-in data type.

For information on the data types, see built-in-type.

For parameters with a character or graphic data type, the PARAMETER CCSID clause or CCSID clause indicates the encoding scheme of the parameter. If you do not specify either of these clauses, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

distinct-type-name
The data type of the input parameter is a distinct type. Any length, precision, scale, subtype, or encoding scheme attributes for the parameter are those of the source type of the distinct type. The distinct type must not be based on a LOB data type.

If you specify the name of the distinct type without a schema name, Db2 resolves the distinct type by searching the schemas in the SQL path.

TABLE LIKE table-name AS LOCATOR
Specifies that the parameter is a transition table. However, when the function is invoked, the actual values in the transition table are not passed to the function. A single value is passed instead. This value is a locator for the table, which the function uses to access the columns of the transition table. The table that is identified can contain XML columns; however, the function cannot reference those XML columns.

A function with a table parameter can only be invoked from the triggered action of a trigger.

array-type-name
The data type of the input parameter is a user-defined array type.

If you specify array-type-name without a schema name, Db2 resolves the array type by searching the schemas in the SQL path.

The implicitly or explicitly specified encoding scheme of all of the parameters with a character or graphic string data type must be the same—either all ASCII, all EBCDIC, or all UNICODE.

Although parameters with a character data type have an implicitly or explicitly specified subtype (BIT, SBCS, or MIXED), the function program can receive character data of any subtype. Therefore, conversion of the input data to the subtype of the parameter might occur when the function is invoked. An error occurs if mixed data that actually contains DBCS characters is used as the value for an input parameter that is declared with an SBCS subtype.

Parameters with a datetime data type or a distinct type are passed to the function as a different data type:

  • A datetime type parameter is passed as a character data type, and the data is passed in ISO format.

    The encoding scheme for a datetime type parameter is the same as the implicitly or explicitly specified encoding scheme of any character or graphic string parameters. If no character or graphic string parameters are passed, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

  • A distinct type parameter is passed as the source type of the distinct type.
RETURNS
Identifies the output of the function.
data-type2
Specifies the data type of the output. The output is nullable.

The same considerations that apply to the data type of input parameter, as described under data-type, apply to the data type of the output of the function.

VERSION routine-version-id
Specifies the version identifier for the first version of the function that is to be generated. You can use an ALTER FUNCTION statement with the ADD VERSION clause or the BIND DEPLOY command to create additional versions of the function.
routine-version-id
An SQL identifier of up to 64 EBCDIC bytes that designates a version of a routine. The UTF-8 representation of the identifier must not exceed 122 bytes.

V1 is the default version identifier.

LANGUAGE SQL
Specifies that the function is written exclusively in SQL.
SPECIFIC specific-name
Specifies a unique name for the function. The name is implicitly or explicitly qualified with a schema name. The name, including the schema name, must not identify the specific name of another function that exists at the current server.

The unqualified form of specific-name is an SQL identifier. The qualified form is an SQL identifier (the schema name) followed by a period and an SQL identifier.

If you do not specify a schema name, it is the same as the explicit or implicit schema name of the function name (function-name). If you specify a schema name, it must be the same as the explicit or implicit schema name of the function name.

If you do not specify the SPECIFIC clause, the default specific name is the name of the function. However, if the function name does not provide a unique specific name or if the function name is a single asterisk, Db2 generates a specific name in the form of:
SQLxxxxxxxxxxxx
where 'xxxxxxxxxxxx' is a string of 12 characters that make the name unique.

The specific name is stored in the SPECIFIC column of the SYSROUTINES catalog table. The specific name can be used to uniquely identify the function in several SQL statements (such as ALTER FUNCTION, COMMENT, DROP, GRANT, and REVOKE) and must be used in Db2 commands (START FUNCTION, STOP FUNCTION, and DISPLAY FUNCTION). However, the function cannot be invoked by its specific name.

NOT DETERMINISTIC or DETERMINISTIC
Specifies whether the function returns the same results each time that the function is invoked with the same input arguments.
NOT DETERMINISTIC
The function might not return the same result each time that the function is invoked with the same input arguments. The function depends on some state values that affect the results. Db2 uses this information to disable the merging of views and table expressions when processing SELECT and SQL data change statements that refer to this function. An example of a function that is not deterministic is one that generates random numbers.

NOT DETERMINISTIC must be specified explicitly or implicitly if the function program accesses a special register or invokes another function that is not deterministic. NOT DETERMINISTIC is the default.

DETERMINISTIC
The function always returns the same result function each time that the function is invoked with the same input arguments. An example of a deterministic function is a function that calculates the square root of the input. Db2 uses this information to enable the merging of views and table expressions for SELECT and SQL data change statements that refer to this function. DETERMINISTIC is not the default. If applicable, specify DETERMINISTIC to prevent non-optimal access paths from being chosen for SQL statements that refer to this function.

Db2 does not verify that the function program is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.

EXTERNAL ACTION or NO EXTERNAL ACTION
Specifies whether the function takes an action that changes the state of an object that Db2 does not manage. An example of an external action is sending a message or writing a record to a file.
EXTERNAL ACTION
The function can take an action that changes the state of an object that Db2 does not manage.

Some SQL statements that invoke functions with external actions can result in incorrect results if parallel tasks execute the function. For example, if the function sends a note for each initial call to it, one note is sent for each parallel task instead of once for the function. Specify the DISALLOW PARALLEL clause for functions that do not work correctly with parallelism.

If you specify EXTERNAL ACTION, then Db2:

  • Materializes the views and table expressions in SELECT and SQL data change statements that refer to the function. This materialization can adversely affect the access paths that are chosen for the SQL statements that refer to this function. Do not specify EXTERNAL ACTION if the function does not have an external action.
  • Does not move the function from one task control block (TCB) to another between FETCH operations.
  • Does not allow another function or stored procedure to use the TCB until the cursor is closed. This is also applicable for cursors declared WITH HOLD.

The only changes to resources made outside of Db2 that are under the control of commit and rollback operations are those changes made under RRS control.

EXTERNAL ACTION must be specified implicitly or explicitly specified if the SQL routine body invokes a function that is defined with EXTERNAL ACTION. EXTERNAL ACTION is the default.

NO EXTERNAL ACTION
The function does not take any action that changes the state of an object that Db2 does not manage. Db2 uses this information to enable the merging of views and table expressions for SELECT and SQL data change statements that refer to this function. If applicable, specify NO EXTERNAL ACTION to prevent non-optimal access paths from being chosen for SQL statements that refer to this function.

Although the scope of global variables are beyond the scope of the routine, global variables can be set in the routine body when NO EXTERNAL ACTION is specified.

Db2 does not verify that the function program is consistent with the specification of EXTERNAL ACTION or NO EXTERNAL ACTION.

MODIFIES SQL DATA, READS SQL DATA, or CONTAINS SQL
Specifies the classification of SQL statements and nested routines that this routine can execute or invoke. The database manager verifies that the SQL statements issued by the function, and all routines locally invoked by the routine, are consistent with this specification; the verification is not performed when nested remote routines are invoked. For the classification of each statement, see SQL statement data access classification for routines.
MODIFIES SQL DATA
Specifies that the function can execute any SQL statement except the statements that are not supported in functions. Do not specify MODIFIES SQL DATA when ALLOW PARALLEL is in effect.
Start of changeIf a MODIFIES SQL DATA function is invoked in a subselect, the results are unpredictable since the number of times the function is invoked will vary depending on the access plan used.
Recommendation: If you invoke a function defined with the MODIFIES SQL DATA option in a subselect, make sure that the SQL statements inside the function do not modify any object that is referenced by that subselect.
End of change
READS SQL DATA
Specifies that the function can execute statements with a data access classification of READS SQL DATA, CONTAINS SQL, or NO SQL. The function cannot execute SQL statements that modify data.

READS SQL DATA is the default.

CONTAINS SQL
Specifies that the function can execute only SQL statements with a data access classification of CONTAINS SQL or NO SQL. The function cannot execute SQL statements that read or modify data.
CALLED ON NULL INPUT or RETURNS NULL ON NULL INPUT
Specifies whether the function is invoked if any of the input arguments is null at execution time.
CALLED ON NULL INPUT
Specifies that the function is to be invoked if any, or if all, of the argument values are null. Specifying CALLED ON NULL INPUT means that the body of the function must be coded to test for null argument values.

CALLED ON NULL INPUT is the default.

RETURNS NULL ON NULL INPUT
Specifies that the function is not invoked and returns the null value if any of the input argument values is null.
STATIC DISPATCH
At function resolution time, Db2 chooses a function based on the static (or declared) types of the function parameters. STATIC DISPATCH is the default.
ALLOW PARALLEL or DISALLOW PARALLEL
Specifies if the function can be run in parallel. The default is DISALLOW PARALLEL, if you specify one or more of the following clauses:
  • NOT DETERMINISTIC
  • EXTERNAL ACTION
  • MODIFIES SQL DATA

Otherwise, ALLOW PARALLEL is the default.

ALLOW PARALLEL
Specifies that the function can be run in parallel.
DISALLOW PARALLEL
Specifies that the function cannot be run in parallel.
ALLOW DEBUG MODE, DISALLOW DEBUG MODE, or DISABLE DEBUG MODE
Specifies whether this version of the routine can be run in debugging mode. The default is determined using the value of the CURRENT DEBUG MODE special register.
ALLOW DEBUG MODE
Specifies that this version of the routine can be run in debugging mode. When this version of the routine is invoked and debugging is attempted, a WLM environment must be available.
DISALLOW DEBUG MODE
Specifies that this version of the routine cannot be run in debugging mode.

You can use an ALTER statement to change this option to ALLOW DEBUG MODE for this initial version of the routine.

DISABLE DEBUG MODE
Specifies that this version of the routine can never be run in debugging mode.

This version of the routine cannot be changed to specify ALLOW DEBUG MODE or DISALLOW DEBUG MODE after this version of the routine has been created or altered to use DISABLE DEBUG MODE. To change this option, drop the routine and create it again using the option that you want. An alternative to dropping and recreating the routine is to create a version of the routine that uses the option that you want and making that version the active version.

When DISABLE DEBUG MODE is in effect, the WLM ENVIRONMENT FOR DEBUG MODE is ignored.

PARAMETER CCSID
Specifies that the encoding scheme for character or graphic string parameters is ASCII, EBCDIC, or UNICODE. The default encoding scheme is the value that is specified in the CCSID clauses of the parameter list or RETURNS clause, or in the DEF ENCODING SCHEME field on installation panel DSNTIPF.

This clause provides a convenient way to specify the encoding scheme for character or graphic string parameters. If individual CCSID clauses are specified for individual parameters in addition to this PARAMETER CCSID clause, the value that is specified in all of the CCSID clauses must be the same value that is specified in this clause. This clause also specifies the encoding scheme that is used for system-generated parameters of the routine, such as message tokens and DBINFO.

If the data type for a parameter is a user-defined distinct type that is defined as a character or graphic type string, the CCSID of the distinct type must be the same as the value that is specified in this clause.

If the data type for a parameter is a user-defined array type that is defined with character or graphic string array elements, or a character string array index, the CCSID of these array attributes must be the same as the value that is specified in this clause.

This clause also specifies the encoding scheme that will be used for system-generated parameters of the routine.

QUALIFIER schema-name
Specifies the implicit qualifier that is used for unqualified object names that are referenced in the procedure body. See Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names for information about how the default for this option is determined.
PACKAGE OWNER authorization-name
Specifies the owner of the package that is associated with the version of the routine. The SQL authorization ID of the process is the default value.
ASUTIME
Specifies the total amount of processor time, in CPU service units, that a single invocation of a routine can run. The value is unrelated to the ASUTIME column of the resource limit specification table.

When you are debugging a routine, setting a limit can be helpful in case the routine gets caught in a loop. For information on service units, see z/OS MVS Initialization and Tuning Guide.

NO LIMIT
Specifies that there is no limit on the service units.

NO LIMIT is the default.

LIMIT integer
The limit on the number of CPU service units is a positive integer in the range 1 - 2 147 483 647. If the procedure uses more service units than the specified value, Db2 cancels the procedure. The CPU cycles that are consumed by parallel tasks in a procedure do not contribute towards the specified ASUTIME LIMIT.
INHERIT SPECIAL REGISTERS or DEFAULT SPECIAL REGISTERS
Specifies how special registers are set on entry to the routine.
INHERIT SPECIAL REGISTERS
Specifies that the values of special registers are inherited, according to the rules that are listed in the table for characteristics of special registers in a routine in Table 1.

INHERIT SPECIAL REGISTERS is the default.

DEFAULT SPECIAL REGISTERS
Specifies that special registers are initialized to the default values, as indicated by the rules in the table for characteristics of special registers in a routine in Table 1.
WLM ENVIRONMENT FOR DEBUG MODE name
Specifies the WLM (workload manager) application environment that is used by Db2 when debugging the routine. The name of the WLM environment is an SQL identifier.

If you do not specify WLM ENVIRONMENT FOR DEBUG MODE, Db2 uses the default WLM-established stored procedure address space specified at installation time.

You must have the appropriate authority for the WLM application environment.

The WLM ENVIRONMENT FOR DEBUG MODE value is ignored when DISABLE DEBUG MODE is in effect.

CURRENT DATA YES or CURRENT DATA NO
Specifies whether to require data currency for read-only and ambiguous cursors when the isolation level of cursor stability is in effect. CURRENT DATA also determines whether block fetch can be used for distributed, ambiguous cursors.
CURRENT DATA YES
Specifies that data currency is required for read-only and ambiguous cursors. Db2 acquired page or row locks to ensure data currency. Block fetch is ignored for distributed, ambiguous cursors.
CURRENT DATA NO
Specifies that data currency is not required for read-only and ambiguous cursors. Block fetch is allowed for distributed, ambiguous cursors. Use of CURRENT DATA NO is not recommended if the routine attempts to dynamically prepare and execute a DELETE WHERE CURRENT OF statement against an ambiguous cursor after that cursor is opened. You receive a negative SQLCODE if your routine attempts to use a DELETE WHERE CURRENT OF statement for any of the following cursors:
  • A cursor that is using block fetch
  • A cursor that is using query parallelism
  • A cursor that is positioned on a row that is modified by this or another application process

CURRENT DATA NO is the default.

DEGREE
Specifies whether to attempt to run a query using parallel processing to maximize performance.
1
Specifies that parallel processing should not be used.

1 is the default.

ANY
Specifies that parallel processing can be used.
CONCURRENT ACCESS RESOLUTION
Specifies the whether processing uses only committed data or whether it will wait for commit or rollback of data that is in the process of being updated.
WAIT FOR OUTCOME
Specifies that processing will wait for the commit or rollback of data that is in the process of being updated.
USE CURRENTLY COMMITTED
Specifies that processing use the currently committed version of the data when data that is in the process of being updated is encountered. USE CURRENTLY COMMITTED is applicable on scans that access tables that are defined in universal table spaces with row or page level lock size.

When there is lock contention between a read transaction and an insert transaction, USE CURRENTLY COMMITTED is applicable to scans with isolation level CS or RS. Applicable scans include intent read scans for read-only and ambiguous queries and for updatable cursors. USE CURRENTLY COMMITTED is also applicable to scans initiated from WHERE predicates of UPDATE or DELETE statements and the subselect of INSERT statements.

When there is lock contention is between a read transaction and a delete transaction, USE CURRENTLY COMMITTED is applicable to scans with isolation level CS and when CURRENT DATA NO is specified.

DYNAMICRULES
Specifies the values that apply, at run time, for the following dynamic SQL attributes:
  • The authorization ID that is used to check authorization
  • The qualifier that is used for unqualified objects
  • The source for application programming options that Db2 uses to parse and semantically verify dynamic SQL statements

DYNAMICRULES also specifies whether dynamic SQL statements can include GRANT, REVOKE, ALTER, CREATE, DROP, and RENAME statements.

In addition to the value of the DYNAMICRULES clause, the run time environment of a routine controls how dynamic SQL statements behave at run time. The combination of the DYNAMICRULES value and the run time environment determines the value for the dynamic SQL attributes. That set of attribute values is called the dynamic SQL statement behavior. The following values can be specified:
RUN
Specifies that dynamic SQL statements are to be processed using run behavior.

RUN is the default.

BIND
Specifies that dynamic SQL statements are to be processed using bind behavior.
DEFINEBIND
Specifies that dynamic SQL statements are to be processed using either define behavior or bind behavior.
DEFINERUN
Specifies that dynamic SQL statements are to be processed using either define behavior or run behavior.
INVOKEBIND
Specifies that dynamic SQL statements are to be processed using either invoke behavior or bind behavior.
INVOKERUN
Specifies that dynamic SQL statements are to be processed using either invoke behavior or run behavior.
See For information on the effects of these options, see Authorization IDs and dynamic SQL.
APPLICATION ENCODING SCHEME
Specifies the default encoding scheme for SQL variables in static SQL statements in the routine body. The value is used for defining an SQL variable in a compound statement if the CCSID clause is not specified as part of the data type, and the PARAMETER CCSID routine option is not specified.
ASCII
Specifies that the data is encoded using the ASCII CCSIDs of the server.
EBCDIC
Specifies that the data is encoded using the EBCDIC CCSIDs of the server.
UNICODE
Specifies that the data is encoded using the Unicode CCSIDs of the server.

See the ENCODING bind option in ENCODING bind option for information about how the default for this option is determined.

WITH EXPLAIN or WITHOUT EXPLAIN
Specifies whether information will be provided about how SQL statements in the routine will execute.
WITHOUT EXPLAIN
Specifies that information will not be provided about how SQL statements in the routine will execute.

You can get EXPLAIN output for a statement that is embedded in a routine that is specified using WITHOUT EXPLAIN by embedding the SQL statement EXPLAIN in the routine body. Otherwise, the value of the EXPLAIN option applies to all explainable SQL statements in the routine body, and to the fullselect portion of any DECLARE CURSOR statements.

WITHOUT EXPLAIN is the default.

WITH EXPLAIN
Specifies that information will be provided about how SQL statements in the routine will execute. Information is inserted into the table owner.PLAN_TABLE. owner is the authorization ID of the owner of the routine. Alternatively, the authorization ID of the owner of the routine can have an alias as owner.PLAN_TABLE that points to the base table, PLAN_TABLE. owner must also have the appropriate SELECT and INSERT privileges on that table. WITH EXPLAIN does not obtain information for statements that access remote objects. PLAN_TABLE must have a base table and can have multiple aliases with the same table name, PLAN_TABLE, but have different schema qualifiers. Start of changeIt cannot be a view or a synonym and should exist before the CREATE statement is processed.End of change In all inserts to owner.PLAN_TABLE, the value of QUERYNO is the statement number that is assigned by Db2.

The WITH EXPLAIN option also populates two optional tables if they exist: DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE. DSN_STATEMNT_TABLE contains an estimate of the processing cost for an SQL statement. See Db2 Application Programming and SQL Guide for more information. DSN_FUNCTION_TABLE contains information about function resolution. See Db2 Application Programming and SQL Guide for more information.

For more information about the EXPLAIN statement, including a description of the tables that are populated by the WITH EXPLAIN option, see EXPLAIN.

WITH IMMEDIATE WRITE or WITHOUT IMMEDIATE WRITE
Specifies whether immediate writes are to be done for updates that are made to group buffer pool dependent page sets or partitions. This option is only applicable for data sharing environments. The IMMEDWRITE subsystem parameter has no affect of this option. IMMEDWRITE bind option shows the implied hierarchy of the IMMEDWRITE bind option (which is similar to this routine option) as it affects run time.
WITHOUT IMMEDIATE WRITE
Specifies that normal write activity is performed. Updated pages that are group buffer pool dependent are written at or before phase one of commit or at the end of abort for transactions that have been rolled back.

WITHOUT IMMEDIATE WRITE is the default.

WITH IMMEDIATE WRITE
Specifies that updated pages that are group buffer pool dependent are immediately written as soon as the buffer update completes. Updated pages are written immediately even if the buffer is updated during forward progress or during the rollback of a transaction. WITH IMMEDIATE WRITE might impact performance.
ISOLATION LEVEL RR, RS, CS, or UR
Specifies how far to isolate the routine from the effects of other running applications. For information about isolation levels, see Choosing an ISOLATION option.
RR
Specifies repeatable read.
RS
Specifies read stability.
CS
Specifies cursor stability. CS is the default.
UR
Specifies uncommitted read.
OPTHINT string-constant
Specifies whether query optimization hints are used for static SQL statements that are contained within the body of the routine.

string-constant is a character string of up to 128 bytes in length, which is used by the Db2 subsystem when searching the PLAN_TABLE for rows to use as input. The default value is an empty string, which indicates that the Db2 subsystem does not use optimization hints for static SQL statements.

Optimization hints are only used if optimization hints are enabled for your system. See Db2 Installation Guide for information about enabling optimization hints.

Start of changeSQL PATHEnd of change
Start of changeSpecifies the SQL path that the Db2 subsystem uses to resolve unqualified user-defined data types, functions, and procedure names (in CALL statements) in the body of the routine. The default value is "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", and the value of the QUALIFIER option, which is the qualifier for the trigger that is the target of the statement. The maximum length of the SQL path is 2048 bytes. Db2 calculates the length by taking each schema-name that is specified and removing any trailing blanks from it, adding a delimiter on the left and right sides, and adding one comma after each schema name except for the last name. The length of the resulting string cannot exceed 2048 bytes.
schema-name
Identifies a schema. Db2 does not verify that the schema exists when the CREATE statement is processed. The same schema name should not appear more than one time in the list of schema names.

SYSPUBLIC must not be specified for the SQL path.

SYSTEM PATH
Specifies the schema names "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM".
SESSION_USER or USER
Specifies the value of the SESSION_USER (or USER) special register. At the time the CREATE statement is processed, the actual length is included in the total length of the list of schema names that is specified for the SQL PATH option.
End of change
REOPT
Specifies if Db2 will determine the access path at run time by using the values of SQL variables or SQL parameters, parameter markers, and special registers.
NONE
Specifies that Db2 does not determine the access path at run time by using the values of SQL variables or SQL parameters, parameter markers, and special registers.

NONE is the default.

ALWAYS
Specifies that Db2 always determines the access path at run time each time an SQL statement is run. Do not specify REOPT ALWAYS with the WITH KEEP DYNAMIC or NODEFER PREPARE clauses.
ONCE
Specifies that Db2 determine the access path for any dynamic SQL statements only once, at the first time the statement is opened. This access path is used until the prepared statement is invalidated or removed from the dynamic statement cache and need to be prepared again.
QUERY ACCELERATION
Specifies whether a static SQL query is bound for acceleration, and if so, with what behavior.
NONE
Specifies that no static SQL query in the application is bound for acceleration or will be accelerated when the application is run.
ENABLE
Specifies that a static SQL query is bound for acceleration if it satisfies the acceleration criteria, including the cost and heuristics criteria. The query is routed to an accelerator when the application runs. Otherwise, if the static query does not satisfy the acceleration criteria, the query is bound for execution in Db2.

If an error condition, such as one of the following examples, occurs while executing the accelerated static query when the application is run, Db2 fails the static query and returns a negative SQL code to the application:

  • A failure occurs while running the static query on the accelerator.
  • The accelerator returns an error for the query.
  • The accelerator is not started and Db2 cannot route the static query to the accelerator for execution.
ENABLE WITH FAILBACK
Results in the same behavior as ENABLE, except if one of the error conditions occurs on the first OPEN of the accelerated static query when the application is run. In this case, instead of failing the static query and returning a negative SQL code to the application, Db2 performs a temporary statement-level incremental bind of the query and runs the query in Db2. The application does not see the acceleration failure. Failback to Db2 is not possible after the application does a successful OPEN for the query on the accelerator.
ELIGIBLE
Specifies that a static SQL query is bound for acceleration if the query meets the basic acceleration criteria, regardless of the cost or heuristics criteria. The query is routed to the accelerator when the application runs.

Like the behavior for ENABLE, if an error condition occurs while executing the accelerated static query when the application is run, Db2 fails the static query and returns a negative SQL code to the application.

ALL
Specifies that all of the static SQL queries in the application are to be bound for acceleration and routed to the accelerator when the application runs. If Db2 determines that a static query cannot be bound to run on the accelerator and the query references a user base table or view, the BIND or REBIND PACKAGE operation fails with an error message for that query. (A failure exception is made for declared global temporary tables (DGTTs) and created global temporary tables and (CGTTs) because these tables cannot be accelerated.)

Like the behavior for ENABLE, if an error condition occurs while executing the accelerated static query when the application is run, Db2 fails the static query and returns a negative SQL code to the application.

This bind option does not apply to a fullselect or WITH common-table-expression that is specified in a RETURN statement for the routine, or in a SET host-variable-assignment that is used in the routine. The queries that are specified in these cases cannot be accelerated.

GET_ACCEL_ARCHIVE
Specifies whether a static SQL query that is bound for acceleration retrieves archived data on the accelerator, instead of active data.
NO
Specifies that no static SQL query is bound to retrieve archived data from the accelerator. If the static query also is not bound for acceleration, the query is bound to run in Db2.

If the static query is bound for acceleration because the QUERYACCELERATION bind option was specified, the query is routed to the accelerator when the application runs; however, the query does not retrieve any archived data.

YES
Specifies that if all of the following criteria are met, the query is bound for acceleration and retrieves the archived data on the accelerator when the application runs:
  • The QUERYACCELERATION bind option is also specified.
  • The static SQL query references an accelerated table that has partitioned data archived on an accelerator.
  • The static query satisfies the acceleration criteria that is specified by the QUERYACCELERATION bind option.

If the static query does not satisfy the acceleration criteria that is specified by the QUERYACCELERATION bind option, the BIND or REBIND PACKAGE operation fails with an error message for that query.

This bind option does not apply to a fullselect or WITH common-table-expression that is specified in a RETURN statement for the routine, or in a SET host-variable-assignment that is used in the routine. The queries that are specified in these cases cannot be accelerated.

Start of changeACCELERATION WAITFORDATAEnd of change
Start of changeSpecifies the maximum amount of time, if any, that an accelerator will delay a query while the accelerator waits for the replication of committed Db2 data changes that occurred prior to Db2 running the query.

For static accelerated queries, you must also set the QUERYACCELERATION bind option for this function or procedure to a valid value other than NONE to request that static queries be accelerated. If the QUERYACCELERATION bind option value is set to NONE, the ACCELERATIONWAITFORDATA bind option is accepted and the package is bound with the option value; however, the option will not apply to static SQL queries because no static queries will be accelerated.

For dynamic accelerated queries, specifying the ACCELERATION WAITFORDATA bind option also initializes the CURRENT QUERY ACCELERATION WAITFORDATA special register, which is used for the dynamic queries in the Db2 function or procedure if the function or procedure option DEFAULT SPECIAL REGISTERS is also used. Initializing CURRENT QUERY ACCELERATION WAITFORDATA to a value greater than 0 specifies that Db2 and the accelerator will apply WAITFORDATA delay behavior and restrictions to all dynamic SQL queries to be accelerated from the Db2 function or procedure. The CURRENT QUERY ACCELERATION special register must also have a valid value other than NONE to request that dynamic queries be accelerated.

nnnn.m
Specifies a DECIMAL(5,1) numeric-constant value that specifies the maximum number of seconds that the accelerator will delay a query while the accelerator waits for the replication of committed Db2 data changes that occurred prior to Db2 running the query.

You can specify a value in the range of 0.0 - 3600.0 seconds. For example, a value of 20.0 represents 20.0 seconds (or 20000 milliseconds), and a value of 30.5 represents 30.5 seconds (or 30500 milliseconds). The maximum value of 3600.0 means they the query is delayed for 3600 seconds.

You can also specify the value as an INTEGER numeric-constant value ranging from 0 - 3600 seconds, which Db2 will convert to a DECIMAL(5,1) value.

Important: When a non-zero value is specified for the ACCELERATIONWAITFORDATA bind option, Db2 and the accelerator will apply other WAITFORDATA delay behaviors, restrictions, and requirements to all queries that will be accelerated from the application package. These behaviors, restrictions, and requirements can cause queries that were formerly accelerated successfully to no longer be accelerated or to fail. See SET CURRENT QUERY ACCELERATION WAITFORDATA for more information about WAITFORDATA behaviors, restrictions, and requirements.
End of change
Start of changeACCELERATOREnd of change
Start of changeSpecifies an accelerator server that, if enabled and available, Db2 will consider as the preferred accelerator for eligible SQL queries before sending the queries to other accelerator servers. If the specified accelerator server is not enabled or available, Db2 will send the queries to other available accelerator servers.End of change
VALIDATE RUN or VALIDATE BIND
Specifies whether to recheck, at run time, errors of the type "OBJECT NOT FOUND" and "NOT AUTHORIZED" that are found during bind or rebind. The option has no effect if all objects and needed privileges exist.
VALIDATE RUN
Specifies that if needed objects or privileges do not exist when the CREATE statement is processed, warning messages are returned, but the CREATE statement succeeds. The Db2 subsystem rechecks for the objects and privileges at run time for those SQL statements that failed the checks during processing of the CREATE statement. The authorization checks the use of the authorization ID of the owner of the routine.

VALIDATE RUN is the default.

VALIDATE BIND
Specifies that if needed objects or privileges do not exist at the time the CREATE statement is processed, an error is issued and the CREATE statement fails.
ROUNDING
Specifies the rounding mode for manipulation of DECFLOAT data. The default value is taken from the DEFAULT DECIMAL FLOATING POINT ROUNDING MODE in DECP.
DEC_ROUND_CEILING
Specifies numbers are rounded towards positive infinity.
DEC_ROUND_DOWN
Specifies numbers are rounded towards 0 (truncation).
DEC_ROUND_FLOOR
Specifies numbers are rounded towards negative infinity.
DEC_ROUND_HALF_DOWN
Specifies numbers are rounded to nearest; if equidistant, round down.
DEC_ROUND_HALF_EVEN
Specifies numbers are rounded to nearest; if equidistant, round so that the final digit is even.
DEC_ROUND_HALF_UP
Specifies numbers are rounded to nearest; if equidistant, round up.
DEC_ROUND_UP
Specifies numbers are rounded away from 0.
DATE FORMAT ISO, EUR, USA, JIS, or LOCAL
Specifies the date format for result values that are string representations of date or time values. For more information, see String representations of datetime values.

The default format is specified in the DATE FORMAT field of installation panel DSNTIP4 of the system where the routine is defined. You cannot use the LOCAL option unless you have a date exit routine.

DECIMAL(15), DECIMAL(31), DECIMAL(15,s), or DECIMAL(31,s)
Specifies the maximum precision that is to be used for decimal arithmetic operations. For more information see Arithmetic with two decimal operands. The default format is specified in the DECIMAL ARITHMETIC field of installation panel DSNTIPF of the system where the routine is defined. If the form pp.s is specified, s must be a number between 1 and 9. s represents the minimum scale that is to be used for division.
FOR UPDATE CLAUSE OPTIONAL or FOR UPDATE CLAUSE REQUIRED
Specifies whether the FOR UPDATE clause is required for a DECLARE CURSOR statement if the cursor is to be used to perform positioned updates.
FOR UPDATE CLAUSE REQUIRED
Specifies that a FOR UPDATE clause must be specified as part of the cursor definition if the cursor will be used to make positioned updates.

FOR UPDATE CLAUSE REQUIRED is the default.

FOR UPDATE CLAUSE OPTIONAL
Specifies that the FOR UPDATE clause does not need to be specified in order for a cursor to be used for positioned updates. The routine body can include positioned UPDATE statements that update columns that the user is authorized to update.
The FOR UPDATE clause with no column list applies to static or dynamic SQL statements. Even if you do not use this clause, you can specify FOR UPDATE OF with a column list to restrict updates to only the columns that are identified in the FOR UPDATE clause and to specify the acquisition of update locks.
TIME FORMAT ISO, EUR, USA, JIS, or LOCAL
Specifies the time format for result values that are string representations of date or time values. For more information, see String representations of datetime values.

The default format is specified in the TIME FORMAT field of installation panel DSNTIP4 of the system where the routine is defined. You cannot use the LOCAL option unless you have a date exit routine.

NOT SECURED or SECURED
Specifies if the function is considered secure for row access control and column access control. The SECURED or NOT SECURED option applies to all future versions of the function.
NOT SECURED
Specifies that the function is not considered secure for row access control and column access control.

NOT SECURED is the default.

When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled when the table is using active column access control.

SECURED
Specifies that the function is considered secure for row access control and column access control.

The function must be secure when it is referenced in a row permission or a column mask.

BUSINESS_TIME SENSITIVE
Determines whether references to application-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.
YES
References to application-period temporal tables are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register. YES is the default value.
NO
References to application-period temporal tables are not affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.
Related information:
SYSTEM_TIME SENSITIVE
Determines whether references to system-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
YES
References to system-period temporal tables are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. YES is the default value.
NO
References to system-period temporal tables are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
Related information:
ARCHIVE SENSITIVE
Determines whether references to archive-enabled tables in SQL statements are affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable.
YES
References to archive-enabled tables are affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable. YES is the default value.
NO
References to archive-enabled tables are not affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable.

For related information, see GET_ARCHIVE

Start of changeAPPLCOMPAT applcompat-levelEnd of change
Start of changeSpecifies the application compatibility level behavior for static SQL statements in the package. If this option is not specified, the behavior is determined by the APPLCOMPAT subsystem parameter. The following applcompat-level values can be specified:
Start of changeVvvRrMmmmEnd of change
Start of changeCompatibility with the behavior of the identified Db2 function level. For example, V12R1M510 specifies compatibility the highest available function level. The equivalent function level or higher must be activated. For a list of supported Db2 12 function levels, see Db2 12 function levels. End of change
Start of changeV12R1End of change
Start of changeCompatibility with the behavior of Db2 12 function level 500. This value this value has the same result as specifying V12R1M500.End of change
V11R1
Compatibility with the behavior of Db2 11 new-function mode. After migration to Db2 12, this value has the same result as specifying V12R1M100.
V10R1
Compatibility with the behavior of DB2® 10 new-function mode.
End of change
Start of changeCONCENTRATE STATEMENTS OFF or CONCENTRATE STATEMENTS WITH LITERALSEnd of change
Start of changeSpecifies whether each dynamic SQL statement in the routine that specifies literal constants will be cached as a separate unique statement entry in the dynamic statement cache, instead of sharing an existing statement in the cache. Dynamic SQL statements are eligible to share an existing statement in the cache if the new statement meets all of the conditions for sharing a cached version of the same dynamic statement, except that the new statement specifies one or more literal constants that are different than the cached statement.
CONCENTRATE STATEMENTS OFF

Specifies that each dynamic SQL statement that specifies literal constants will be cached as a unique statement entry if it specifies one or more constants that are different than the cached version of the same dynamic statement. CONCENTRATE STATEMENTS OFF is the default dynamic statement caching behavior.

CONCENTRATE STATEMENTS WITH LITERALS

Specifies that each dynamic SQL statement that specifies literal constants will share a cached version of the same dynamic statement that is also prepared using the CONCENTRATE STATEMENTS WITH LITERALS option, if the new dynamic statement meets all of the conditions for sharing the cached statement, and the constants that are specified can be reused in place of the constants in the cached statement.

End of change
SQL-routine-body
Specifies a single SQL control statement, including a compound-statement. See SQL procedural language (SQL PL) for more information about defining SQL functions.

An error is issued if an SQL function calls a procedure and the procedure issues a COMMIT, ROLLBACK, CONNECT, RELEASE, or SET CONNECTION statement.

If the SQL-routine-body is a compound statement, it must contain at least one RETURN statement and a RETURN statement must be executed when the function is invoked.

SQL-routine-body must not contain a period specification or period clause.

An ALTER FUNCTION (compiled SQL scalar) statement or an ALTER PROCEDURE (SQL native) statement with an ADD VERSION clause or a REPLACE clause is not allowed in an SQL-routine-body.

Start of changeWRAPPED obfuscated-statement-textEnd of change
Start of changeSpecifies the encoded definition of the function. A CREATE FUNCTION statement can be encoded using the WRAP scalar function.

WRAPPED must not be specified on a static CREATE statement.

End of change

Notes

Considerations for all types of user-defined functions:
For considerations that apply to all types of user-defined functions, see CREATE FUNCTION.
Start of changeTypes of SQL scalar functions:End of change
Start of changeIf the syntax of the CREATE FUNCTION statement conforms to the syntax diagrams and descriptions for CREATE FUNCTION (inlined SQL scalar), Db2 defines an inlined function, and a package is not created. When an inlined SQL scalar function is invoked, the expression in the RETURN statement of the function is copied (inlined) into the query itself; the function is not invoked. The attributes of an inlined SQL scalar function are described in CREATE FUNCTION (inlined SQL scalar).

Otherwise, Db2 attempts to define a compiled function with an associated package. For example, if the RETURN statement contains a scalar fullselect, Db2 attempts to define a compiled function. The attributes of a compiled SQL scalar function are described in CREATE FUNCTION (compiled SQL scalar).

To determine what type of SQL scalar function is created, refer to the INLINE column of the SYSIBM.SYSROUTINES catalog table. In the INLINE column, a value of Y indicates that the function is an inlined function, and a value of N indicates that the function is a compiled function.

End of change
Start of changeConsiderations for functions defined with MODIFIES SQL DATA:End of change
Start of changeIf a function is specified in a subselect, and the function is defined as MODIFIES SQL DATA, the number of times the function is invoked is invoked will vary depending on the access plan used.End of change
Self-referencing function:
The body of an SQL function (that is, the expression or NULL in the RETURN statement in the body of the CREATE FUNCTION statement) cannot contain a recursive invocation of itself or to another function that invokes it, because such a function would not exist to be referenced.
Dependent objects:
An SQL routine is dependent on objects that are referenced in the routine body.
Start of changeObfuscated statements:End of change
Start of changeA CREATE FUNCTION statement can be executed in obfuscated form. In an obfuscated statement, only the function name, parameters, and the WRAPPED keyword are readable. The rest of the statement is encoded in such a way that it is not readable but can be decoded by a database server that supports obfuscated statements. The WRAP scalar function produces obfuscated statements. Any debug options that are specified when the function is created from an obfuscated statement are ignored.End of change
Identifier resolution:
See SQL procedural language (SQL PL) for information on how names are resolved to columns, variables, or SQL parameters within an SQL routine.

If duplicate names are used for columns, variables, and parameters, qualify the duplicate names by using the table designator for columns, the routine name for parameters, the label name for SQL variables, and the schema name for global variables.

Error handling in SQL functions:
You should consider the possible exceptions that can occur for each SQL statement in the body of a compiled SQL function. Any exception SQLSTATE that is not handled within the function (using a handler), results in the exception SQLSTATE being returned for the SQL statement that caused the function to be invoked.
Lines within the SQL function definition:
When a compiled SQL function is created, information is retained on lines in the CREATE statement. Lines are determined by the presence of the new line control character.
Start of changeIn a compiled SQL scalar function, a new line control character is a special character that is used for a new line. The new line control characters for a compiled SQL scalar function include:
  • Line feed
  • New line
  • Carriage return
  • Carriage return, followed by a line feed
  • Carriage return, followed by a new line
For more information about control characters, see Tokens.End of change
Considerations for SQL processor programs:
SQL processor programs, such as SPUFI, the command line processor, and DSNTEP2, might not correctly parse SQL statements in the routine body that end with semicolons. These processor programs accept multiple SQL statements as input, with each statement separated with a terminator character. Processor programs that use a semicolon as the SQL statement terminator can truncate a CREATE FUNCTION statement with embedded semicolons and pass only a portion of it to Db2. Therefore, you might need to change the SQL terminator character for these processor programs. For information on changing the terminator character for SPUFI and DSNTEP2, see Setting the SQL terminator character in a SPUFI input data set.
Considerations for packages:
A package is generated for compiled SQL scalar functions. The package that is associated with the first version of a function is named as follows:
  • location is set to the value of the CURRENT SERVER special register.
  • collection-id (schema) for the package is the same as the schema qualifier of the function.
  • package-id is the same as the specific name of the function.
  • version-id is the same as the version identifier for the initial version of the function.

The package is generated using the bind options that correspond to the implicitly or explicitly specified function options. In addition to the corresponding bind options, the package is generated using the following bind options:

  • FLAG(I)
  • SQLERROR(NOPACKAGE)
  • ENABLE(*)
Correspondence of function options to bind command options:
The following table lists options for CREATE FUNCTION and ALTER FUNCTION and the corresponding bind command option. See BIND and REBIND options for packages, plans, and services for information about the BIND command options.
Table 1. Correspondence of function options to bind options
CREATE FUNCTION or ALTER FUNCTION option bind command option
Start of changeACCELERATION WAITFORDATA nnnn.mEnd of change Start of changeACCELERATIONWAITFORDATA(nnnn.m)End of change
Start of changeACCELERATOR accelerator-nameEnd of change Start of changeACCELERATOR(accelerator-name)End of change
APPLICATION ENCODING SCHEME ASCII ENCODING(ASCII)
APPLICATION ENCODING SCHEME EBCDIC ENCODING(EBCDIC)
APPLICATION ENCODING SCHEME UNICODE ENCODING(UNICODE)
ARCHIVE SENSITIVE NO ARCHIVESENSITIVE(NO)
ARCHIVE SENSITIVE YES ARCHIVESENSITIVE(YES)
BUSINESS_TIME SENSITIVE NO BUSTIMESENSITIVE(NO)
BUSINESS_TIME SENSITIVE YES BUSTIMESENSITIVE(YES)
CURRENT DATA NO CURRENTDATA(NO)
CURRENT DATA YES CURRENTDATA(YES)
DYNAMICRULES RUN DYNAMICRULES(RUN)
DYNAMICRULES BIND DYNAMICRULES(BIND)
DYNAMICRULES DEFINEBIND DYNAMICRULES(DEFINEBIND)
DYNAMICRULES DEFINERUN DYNAMICRULES(DEFINERUN)
DYNAMICRULES INVOKEBIND DYNAMICRULES(INVOKEBIND)
DYNAMICRULES INVOKERUN DYNAMICRULES(INVOKERUN)
Start of changeGET_ACCEL_ARCHIVE NOEnd of change Start of changeGETACCELARCHIVE(NO) End of change
GET_ACCEL_ARCHIVE YES GETACCELARCHIVE(YES)
ISOLATION LEVEL CS ISOLATION(CS)
ISOLATION LEVEL RS ISOLATION(RS)
ISOLATION LEVEL RR ISOLATION(RR)
ISOLATION LEVEL UR ISOLATION(UR)
OPTHINT string-constant OPTHINT(hint-id)
PACKAGE OWNER authorization-name OWNER(authorization-id)
QUALIFIER schema-name QUALIFIER(qualifier-name)
Start of changeQUERY ACCELERATION NONEEnd of change Start of change
QUERYACCELERATION(NONE)
End of change
QUERY ACCELERATION ENABLE QUERYACCELERATION(ENABLE)
QUERY ACCELERATION ENABLE WITH FAILBACK QUERYACCELERATION(ENABLE WITH FAILBACK)
QUERY ACCELERATION ELIGIBLE QUERYACCELERATION(ELIGIBLE)
QUERY ACCELERATION ALL QUERYACCELERATION(ALL)
REOPT ALWAYS REOPT(ALWAYS)
REOPT NONE REOPT(NONE)
REOPT ONCE REOPT(ONCE)
ROUNDING DEC_ROUND_CEILING ROUNDING(CEILING)
ROUNDING DEC_ROUND_DOWN ROUNDING(DOWN)
ROUNDING DEC_ROUND_FLOOR ROUNDING(FLOOR)
ROUNDING DEC_ROUND_HALF_DOWN ROUNDING(HALFDOWN)
ROUNDING DEC_ROUND_HALF_EVEN ROUNDING(HALFEVEN)
ROUNDING DEC_ROUND_HALF_UP ROUNDING(HALFUP)
ROUNDING DEC_ROUND_UP ROUNDING(UP)
SQL PATH path-specification PATH(path-specification)
SYSTEM_TIME SENSITIVE NO SYSTIMESENSITIVE(NO)
SYSTEM_TIME SENSITIVE YES SYSTIMESENSITIVE(YES)
VALIDATE BIND VALIDATE(BIND)
VALIDATE RUN VALIDATE(RUN)
WITH EXPLAIN EXPLAIN(YES)
WITHOUT EXPLAIN EXPLAIN(NO)
WITH IMMEDIATE WRITE IMMEDWRITE(YES)
WITHOUT IMMEDIATE WRITE IMMEDWRITE(NO)
Application compatibility level considerations for function objects
The application compatibility level controls the adoption and use of new capabilities and enhancements. When an object is created or altered, two separate application compatibility levels are used: one to process the definition of the object, and the other for processing the SQL statements in the object body:
Object definition

The CURRENT APPLICATION COMPATIBILITY special register value is used to process the object definition, except for statements in the object body

This application compatibility level is stored in the SYSENVIRONMENT.APPLCOMPAT column. You can use the environment ID value in the catalog definition of the object to locate the SYSENVIRONMENT row with the matching ENVID value.

This application compatibility level can be changed when the object is regenerated.

Statements in the object body

The application compatibility level that is implicitly or explicitly specified with the APPLCOMPAT option of the CREATE or ALTER statement is used to process statements in the object body.

This application compatibility level is stored in the SYSPACKAGE.APPLCOMPAT column for the package associated with the object definition.

Deploying a compiled SQL scalar function:
When a BIND DEPLOY command is issued to deploy a compiled SQL scalar function to a target location, the SECURED and NOT SECURED options are included in the deployment process.

When deploying a compiled SQL scalar function, if a function with the same target name does not exist at the target location, the deployed function is created as a new function at the target location with the same SECURED or NOT SECURED option that is specified (or the default of NOT SECURED is used) in the source of the deployment.

When deploying a compiled SQL scalar function, if a function with the same target name already exists at the target location, the deployed function is either added as a new version of the function, or the deployed function is used to replace an existing version of the function. The SECURED or NOT SECURED option of the deployed function must be the same as that of the existing function at the target location.

Versions of a function:
The CREATE FUNCTION statement for an SQL function defines the initial version of the function. You can define additional versions using the ADD VERSION clause of the ALTER FUNCTION statement. All versions of a function share the same function signature and the same specific name. However, the parameters names can differ between versions of a functions. Only one version of the function can be considered to be the active version of the function.
Alternative syntax and synonyms:
To provide compatibility with previously releases of Db2 or other products in the Db2 family, Db2 supports the following alternative syntax:
  • VARIANT as a synonym for NOT DETERMINISTIC
  • NOT VARIANT as a synonym for DETERMINISTIC
  • NOT NULL CALL as a synonym for RETURNS NULL ON NULL INPUT
  • NULL CALL as a synonym for CALLED ON NULL INPUT
  • TIMEZONE can be specified as an alternative to TIME ZONE.

Example

Define a scalar function that returns the text of an input string, in reverse order:
    CREATE FUNCTION REVERSE(INSTR VARCHAR(4000))
      RETURNS VARCHAR(4000)
      DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
      BEGIN
      DECLARE REVSTR, RESTSTR VARCHAR(4000) DEFAULT '';
      DECLARE LEN INT;
      IF INSTR IS NULL THEN
      RETURN NULL;
      END IF;
      SET (RESTSTR, LEN) = (INSTR, LENGTH(INSTR));
      WHILE LEN > 0 DO
      SET (REVSTR, RESTSTR, LEN) 
        = (SUBSTR(RESTSTR, 1, 1) CONCAT REVSTR, 
        SUBSTR(RESTSTR, 2, LEN - 1),
        LEN - 1);
     END WHILE;
     RETURN REVSTR;
   END#