CREATE FUNCTION (external scalar)

Invocation

This statement can be embedded in an application program, or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization id of the statement must include at least one of the following:

• For the SYSFUNCS catalog view and SYSPARMS catalog table¹:
  • The INSERT privilege on the table, and
  • The system authority *EXECUTE on library QSYS2
• Database administrator authority

If the external program or service program exists, the privileges held by the authorization ID of the statement must include at least one of the following:

• For the external program or service program that is referenced in the SQL statement:
  • The system authority *EXECUTE on the library that contains the external program or service program.
  • The system authority *EXECUTE on the external program or service program, and
  • The system authority *CHANGE on the program or service program. The system needs this authority to update the program object to contain the information necessary to save/restore the function to another system. If user does not have this authority, the function is still created, but the program object is not updated.
• Database administrator authority

If SQL names are specified and a user profile exists that has the same name as the library into which the function is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:

• The system authority *ADD to the user profile with that name
• Database administrator authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

• For each distinct type identified in the statement:
  • The USAGE privilege on the distinct type, and
  • The system authority *EXECUTE on the library containing the distinct type
• Database administrator authority

To replace an existing function, the privileges held by the authorization ID of the statement must include at least one of the following:

• The following system authorities:
  • The system authority of *OBJMGT on the service program object associated with the function
  • All authorities needed to DROP the function
  • The system authority *READ to the SYSFUNCS catalog view and SYSPARMS catalog table
• Database administrator authority

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View and Corresponding System Authorities When Checking Privileges to a Distinct Type.

Description

OR REPLACE

Specifies to replace the definition for the function if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog with the exception that privileges that were granted on the function are not affected. This option is ignored if a definition for the function does not exist at the current server. To replace an existing function, the specific-name and function-name of the new definition must be the same as the specific-name and function-name of the old definition, or the signature of the new definition must match the signature of the old definition. Otherwise, a new function is created.

function-name

Names the user-defined function. The combination of name, schema name, the number of parameters, and the data type of each parameter (without regard for any length, precision, scale, or CCSID attributes of the data type) must not identify a user-defined function that exists at the current server.

For SQL naming, the function will be created in the schema specified by the implicit or explicit qualifier.

For system naming, the function will be created in the schema that is specified by the qualifier. If no qualifier is specified:

• If the value of the CURRENT SCHEMA special register is *LIBL, the function will be created in the current library (*CURLIB).
• Otherwise, the function will be created in the current schema.

In general, more than one function can have the same name if the function signature of each function is unique.

Certain function names are reserved for system use. For more information see Choosing the schema and function name in CREATE FUNCTION.

(parameter-declaration,...)

Specifies the number of input parameters of the function and the data type of each parameter. Each parameter-declaration specifies an input parameter for the function. A maximum of 90 parameters can be specified. 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 the parameters for a function are input parameters and are nullable. In the case of JAVA, numeric parameters other than the DECIMAL and NUMERIC types are not nullable. A runtime error will occur if a null value is input to such a parameter for a CALLED ON NULL INPUT function. For more information, see Defining the parameters in CREATE FUNCTION.

parameter-name

Names the parameter. Although not required, a parameter name can be specified for each parameter. The name cannot be the same as any other parameter-name in the parameter list.

data-type1

Specifies the data type of the input parameter. The data type can be a built-in data type or a distinct type. It cannot be an array type.

built-in-type

Specifies a built-in data type. For a more complete description of each built-in data type, see CREATE TABLE. Some data types are not supported in all languages. For details on the mapping between the SQL data types and host language data types, see Embedded SQL Programming topic collection. Built-in data type specifications can be specified if they correspond to the language that is used to write the user-defined function.

distinct-type-name

Specifies a user-defined distinct type. The length, precision, or scale attributes for the parameter are those of the source type of the distinct type (those specified on CREATE TYPE). For more information on creating a distinct type, see CREATE TYPE (distinct).

If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.

If a CCSID is specified, the parameter will be converted to that CCSID prior to passing it to the function. If a CCSID is not specified, the CCSID is determined by the default CCSID at the current server at the time the function is invoked.

Any parameter that has an XML type must specify either the XML-cast-type clause or the AS LOCATOR clause.

AS LOCATOR

Specifies that a locator to the value of the parameter is passed to the function instead of the actual value. Specify AS LOCATOR only for parameters with a LOB or XML data type or a distinct type based on a LOB or XML data type. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.

For more information on the AS LOCATOR clause, see Specifying AS LOCATOR for a parameter in CREATE FUNCTION.

AS XML-cast-type

Specifies the data type passed to the function for a parameter that is XML type or a distinct type based on XML type. If LOCATOR is specified, the parameter is a locator to the value rather than the actual value.

If a CCSID value is specified, only Unicode CCSID values can be specified for graphic data types. If a CCSID value is not specified, the CCSID is established at the time the function is created according to the SQL_XML_DATA_CCSID QAQQINI option setting. The default CCSID is 1208. See XML Values for a description of this option.

default-clause

Specifies a default value for the parameter. The default can be a constant, a special register, a global variable, an expression, or the keyword NULL. The expression is any expression defined in Expressions, that does not include an aggregate function or column name. If a default value is not specified, the parameter has no default and cannot be omitted on invocation. The maximum length of the expression string is 64K.

The default expression must be assignment compatible to the parameter data type.

Any comma in the default expression that is intended as a separator of numeric constants in a list must be followed by a space.

All objects referenced in a default expression must exist when the function is created. When the function is invoked, the default will be evaluated using the authority of the invoker.

A default cannot be specified for a parameter of type array.

RETURNS

Specifies the data type for the result of the function. Consider this clause in conjunction with the optional CAST FROM clause.

data-type2

Specifies the data type and attributes of the output.

You can specify any built-in data type (except LONG VARCHAR, LONG VARGRAPHIC, or DataLink) or a distinct type (that is not based on a DataLink). You cannot specify an array type.

If a CCSID is specified,

• If AS LOCATOR is not specified, the result returned is assumed to be encoded in that CCSID.
• If AS LOCATOR is specified and the CCSID of the data the locator points to is encoded in a different CCSID, the data is converted to the specified CCSID.

If a CCSID is not specified and the function is not referenced in the outermost select list of a view,

• If AS LOCATOR is not specified, the result returned is assumed to be encoded in the CCSID of the job (or associated graphic CCSID of the job for graphic string return values).
• If AS LOCATOR is specified, the data the locator points to is converted to the CCSID of the job, if the CCSID of the data the locator points to is encoded in a different CCSID. To avoid any potential loss of characters during the conversion, consider explicitly specifying a CCSID that can represent any characters that will be returned from the function. This is especially important if the data type is graphic string data. In this case, consider using CCSID 1200 or 13488 (Unicode graphic string data).

If a CCSID is not specified and the function is referenced in the outermost select list of a view,

• If AS LOCATOR is not specified, the result returned is assumed to be encoded in the CCSID of the associated view column.
• If AS LOCATOR is specified, the data the locator points to is converted to the CCSID of the associated view column, if the CCSID of the data the locator points to is encoded in a different CCSID. To avoid any potential loss of characters during the conversion, consider explicitly specifying a CCSID that can represent any characters that will be returned from the function. This is especially important if the data type is graphic string data. In this case, consider using CCSID 1200 or 13488 (Unicode graphic string data).

AS LOCATOR

Specifies that the function returns a locator to the value rather than the actual value. Specify AS LOCATOR only if the result of the function has a LOB or XML data type or a distinct type based on a LOB or XML data type. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.

For more information on the AS LOCATOR clause, see Specifying AS LOCATOR for a parameter in CREATE FUNCTION.

data-type3 CAST FROM data-type4

Specifies the data type and attributes of the function (data-type4) and the data type in which that result is returned to the invoking statement (data-type3). The two data types can be different. For example, for the following definition, the function returns a DOUBLE value, which the database manager converts to a DECIMAL value and then passes to the statement that invoked the function:

CREATE FUNCTION SQRT (DECIMAL15,0))
  RETURNS DECIMAL(15,0)
  CAST FROM DOUBLE
  ... 

The value of data-type4 must not be XML or a distinct type and must be castable to data-type3. The value for data-type3 can be any built-in data type or distinct type. (For information on casting data types, see Casting between data types).

For CCSID information, see the preceding description of data-type2.

AS LOCATOR

Specifies that the function returns a locator to the value rather than the actual value. Specify AS LOCATOR only if the result of the function has a LOB data type or a distinct type based on a LOB data type. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.

For more information on the AS LOCATOR clause, see Specifying AS LOCATOR for a parameter in CREATE FUNCTION.

LANGUAGE

Specifies the language interface convention to which the function body is written. All programs must be designed to run in the server's environment.

If LANGUAGE is not specified, the LANGUAGE is determined from the program attribute information associated with the external program at the time the function is created. The language of the program is assumed to be C if:

• The program attribute information associated with the program does not identify a recognizable language
• The program cannot be found

C   

The external program is written in C.

C++

The external program is written in C++.

CL   

The external program is written in CL or ILE CL.

COBOL

The external program is written in COBOL.

COBOLLE

The external program is written in ILE COBOL.

JAVA

The external program is written in JAVA. The database manager will call the user-defined function, which must be a public static method of the specified Java™ class

When LANGUAGE JAVA is specified, specify the EXTERNAL NAME clause with a valid external-java-routine-name. Do not specify LANGUAGE JAVA when SCRATCHPAD, FINAL CALL, or DBINFO is specified.

PLI

The external program is written in PL/I.

RPG

The external program is written in RPG.

RPGLE

The external program is written in ILE RPG.

PARAMETER STYLE

Specifies the conventions used for passing parameters to and returning the values from functions:

SQL

All applicable parameters are passed. The parameters are defined to be in the following order:

• n parameters for the input parameters that are specified for the function.
• A parameter for the result of the function.
• n parameters for indicator variables for the input parameters.
• A parameter for the indicator variable for the result.
• A CHAR(5) output parameter for SQLSTATE. The SQLSTATE returned indicates the success or failure of the function. The SQLSTATE returned can either be:
  • the SQLSTATE from the last SQL statement executed in the external program,
  • an SQLSTATE that is assigned by the external program.

    The user may set the SQLSTATE to any valid value in the external program to return an error or warning from the function.

• A VARCHAR(517) input parameter for the fully qualified function name.
• A VARCHAR(128) input parameter for the specific name.
• A VARCHAR(1000) output parameter for the message text.

  When control is returned to the invoking program, the message text can be found in the 6th token of the SQLERRMC field of the SQLCA. Only a portion of the message text is available. For information on the layout of the message data in the SQLERRMC, see the replacement data descriptions for message SQL0443 in message file QSQLMSG. The complete message text can be retrieved using the GET DIAGNOSTICS statement. For more information, see GET DIAGNOSTICS.

• Zero to three optional parameters:
  • A structure (consisting of an INTEGER followed by a CHAR(n)) input and output parameter for the scratchpad, if SCRATCHPAD was specified on the CREATE FUNCTION statement.
  • An INTEGER input parameter for the call type, if FINAL CALL was specified on the CREATE FUNCTION statement.
  • A structure for the dbinfo structure, if DBINFO was specified on the CREATE FUNCTION statement.

These parameters are passed according to the specified LANGUAGE. For example, if the language is C or C++, the VARCHAR parameters are passed as NUL-terminated strings. For more information about the parameters passed, see the include sqludf in the appropriate source file in library QSYSINC. For example, for C, sqludf can be found in QSYSINC/H.

DB2GENERAL

This parameter style is used to specify the conventions for passing parameters to and returning the value from external functions that are defined as a method in a Java class. All applicable parameters are passed. The parameters are defined to be in the following order:

• The first N parameters are the input parameters that are specified on the CREATE FUNCTION statement.
• A parameter for the result of the function.

DB2GENERAL is only allowed when the LANGUAGE is JAVA.

GENERAL

All applicable parameters are passed. The parameters are defined to be in the following order:

• The first N parameters are the input parameters that are specified on the CREATE FUNCTION statement.

Note that the result is returned as a value of a C value returning function. For example:

return_val func(parameter-1, parameter-2, ...)

GENERAL is only allowed when EXTERNAL NAME identifies a service program.

GENERAL WITH NULLS

All applicable parameters are passed. The parameters are defined to be in the following order:

• The first N parameters are the input parameters that are specified on the CREATE FUNCTION statement.
• An additional argument is passed for an indicator variable array.
• A parameter for the indicator variable for the result.

Note that the result is returned as a value of a C value returning function. For example:

return_val func(parameter-1, parameter-2, ...)

GENERAL WITH NULLS is only allowed when EXTERNAL NAME identifies a service program.

JAVA

Specifies that the function will use a parameter passing convention that conforms to the Java language and ISO/IEC FCD 9075-13:2003, Information technology - Database languages - SQL - Part 13: Java Routines and Types (SQL/JRT) specification. All applicable parameters are passed. The parameters are defined to be in the following order:

• The first N parameters are the input parameters that are specified on the CREATE FUNCTION statement.

Note that the result is returned as a value of a C value returning function. For example:

return_val func(parameter-1, parameter-2, ...)

JAVA is only allowed when the LANGUAGE is JAVA.

Note that the language of the external function determines how the parameters are passed. For example, in C, any VARCHAR or CHAR parameters are passed as NUL-terminated strings. For more information, see the SQL Programming topic collection. For Java routines, see the IBM® Developer Kit for Java topic collection.

SPECIFIC specific-name

Specifies a unique name for the function. For more information on specific names, see Specifying a specific name for a function in CREATE FUNCTION.

GLOBAL DETERMINISTIC or STATEMENT DETERMINISTIC or NOT DETERMINISTIC

Specifies whether the function returns the same results each time that the function is invoked with the same input arguments. The default is NOT DETERMINISTIC.

NOT DETERMINISTIC

Specifies that 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. The database manager uses this information during optimization of SQL statements. An example of a function that is not deterministic is one that generates random numbers.

A function that is not deterministic might return incorrect results if the function is executed by parallel tasks. Specify the DISALLOW PARALLEL clause for these functions.

NOT DETERMINISTIC should be specified if the function contains a reference to a special register, a non-deterministic function, or a sequence.

GLOBAL DETERMINISTIC

Specifies that the function always returns the same result each time that the function is invoked with the same input arguments. The database manager uses this information during optimization of SQL statements. The query optimizer may choose to cache global deterministic scalar function results.² An example of a global deterministic function is a function that calculates the square root of the input argument.

STATEMENT DETERMINISTIC

Specifies that the function might not return the same result each time that the function is invoked with the same input arguments, but multiple invocations of the function within a single SQL statement are considered deterministic. The query optimizer will not cache statement deterministic scalar function results.³ An example of a statement deterministic function is a function that performs currency conversion.

CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA, or NO SQL

Specifies the classification of SQL statements and nested routines that the function can execute. The database manager verifies that the SQL statements issued by the function, and all routines locally invoked by the function, are consistent with this specification. The verification is not performed when nested remote routines are invoked. For the classification of each statement, see Characteristics of SQL statements. The default is READS SQL DATA. This option is ignored for parameter default expressions.

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.

NO SQL

Specifies that the function can execute only SQL statements with a data access classification of NO SQL.

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 any SQL statements that read or modify data.

MODIFIES SQL DATA

The function can execute any SQL statement except those statements that are not supported in any function.

RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT

Specifies whether the function is called if any of the input arguments is null at execution time. CALLED ON NULL INPUT is the default.

RETURNS NULL ON INPUT

Specifies that the function is not invoked if any of the input arguments is null. The result is the null value.

CALLED ON NULL INPUT

Specifies that the function is to be invoked, if any, or all, argument values are null. This specification means that the function must be coded to test for null argument values. The function can return a null or nonnull value.

INHERIT SPECIAL REGISTERS

Specifies that existing values of special registers are inherited upon entry to the function.

STATIC DISPATCH

Specifies that the function is dispatched statically. All functions are statically dispatched.

NO DBINFO or DBINFO

Specifies whether additional status information is passed when the function is invoked. The default is NO DBINFO.

NO DBINFO

Specifies that no additional information is passed.

DBINFO

Specifies that the database manager should pass a structure containing status information to the function. Table 1 contains a description of the DBINFO structure. Detailed information about the DBINFO structure can be found in include sqludf in the appropriate source file in library QSYSINC. For example, for C, sqludf can be found in QSYSINC/H.

DBINFO is only allowed with PARAMETER STYLE SQL or PARAMETER STYLE DB2GENERAL.

Table 1. DBINFO fields

Field	Data Type	Description
Relational database	VARCHAR(128)	The name of the current server.
Authorization ID	VARCHAR(128)	The run-time authorization ID.
CCSID Information	INTEGER INTEGER INTEGER   INTEGER INTEGER INTEGER   INTEGER INTEGER INTEGER   INTEGER   CHAR(8)	The CCSID information of the job. Three sets of three CCSIDs are returned. The following information identifies the three CCSIDs in each set: SBCS CCSID DBCS CCSID Mixed CCSID Following the three sets of CCSIDs is an integer that indicates which set of three sets of CCSIDs is applicable and eight bytes of reserved space. Each set of CCSIDs is for a different encoding scheme (EBCDIC, ASCII, and Unicode). If a CCSID is not explicitly specified for a parameter on the CREATE FUNCTION statement, the input string is assumed to be encoded in the CCSID of the job at the time the function is executed. If the CCSID of the input string is not the same as the CCSID of the parameter, the input string passed to the external function will be converted before calling the external program.
Target column	VARCHAR(128) VARCHAR(128) VARCHAR(128)	If a user-defined function is specified on the right-hand side of a SET clause in an UPDATE statement, the following information identifies the target column: Schema name Base table name Column name If the user-defined function is not on the right-hand side of a SET clause in an UPDATE statement, these fields are blank.
Version and release	CHAR(8)	The version, release, and modification level of the database manager.
Platform	INTEGER	The server's platform type.

EXTERNAL ACTION or NO EXTERNAL ACTION

Specifies whether the function takes an action that changes the state of an object that the database manager does not manage. An example of an external action is sending a message or writing a record to a stream file. The default is EXTERNAL ACTION.

EXTERNAL ACTION

Specifies that the function can take an action that changes the state of an object that the database manager does not manage. Thus, the function must be invoked with each successive function invocation. EXTERNAL ACTION should be specified if the function contains a reference to another function that has an external action.

NO EXTERNAL ACTION

The function does not perform an external action. It need not be called with each successive function invocation.

NO EXTERNAL ACTION functions might perform better than EXTERNAL ACTION functions because they might not be invoked for each successive function invocation.

FENCED or NOT FENCED

Specifies whether the external function runs in an environment that is isolated from the database manager environment. FENCED is the default.

FENCED

The function will run in a separate thread.

FENCED functions cannot keep SQL cursors open across individual calls to the function. However, the cursors in one thread are independent of the cursors in any other threads which reduces the possibility of cursor name conflicts.

NOT FENCED

The function may run in the same thread as the invoking SQL statement.

NOT FENCED functions can keep SQL cursors open across individual calls to the function. Since cursors can be kept open, the cursor position will also be preserved between calls to the function. However, cursor names may conflict since the UDF is now running in the same thread as the invoking SQL statement and other NOT FENCED UDFs.

NOT FENCED functions usually perform better than FENCED functions.

PROGRAM TYPE MAIN or PROGRAM TYPE SUB

This parameter is allowed for compatibility with other products. It indicates whether the routine's external program is a program (*PGM) or a procedure in a service program (*SRVPGM).

PROGRAM TYPE MAIN

Specifies that the routine executes as the main entry point in a program. The external program must be a *PGM object.

PROGRAM TYPE SUB

Specifies that the routine executes as a procedure in a service program. The external program must be a *SRVPGM object.

NO FINAL CALL or FINAL CALL

Specifies whether a final call is made to the function. A final call enables the function to free any system resources that it has acquired. A final call is useful when the function has been defined with the SCRATCHPAD keyword and the function acquires system resources and stores them in the scratchpad. The default is NO FINAL CALL.

NO FINAL CALL

Specifies that a final call is not made to the function. The function does not receive an additional argument that specifies the type of call.

FINAL CALL

Specifies that a final call is made to the function. To differentiate between final calls and other calls, the function receives an additional argument that specifies the type of call.

FINAL CALL is only allowed with PARAMETER STYLE SQL or PARAMETER STYLE DB2GENERAL.

The types of calls are:

First Call

Specifies the first call to the function for this reference to the function in this SQL statement. A first call is a normal call. SQL arguments are passed and the function is expected to return a result.

Normal Call

Specifies that SQL arguments are passed and the function is expected to return a result.

Final Call

Specifies the last call to the function to enable the function to free resources. A final call is not a normal call. If an error occurs, the database manager attempts to make the final call.

A final call occurs at these times:

• End of statement: When the cursor is closed for cursor-oriented statements, or the execution of the statement has completed.
• End of a parallel task: When the function is executed by parallel tasks.
• End of transaction: When normal end of statement processing does not occur. For example, the logic of an application, for some reason, bypasses closing the cursor.

Some functions that use a final call can receive incorrect results if parallel tasks execute the function. For example, if a function sends a note for each final 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 have inappropriate actions when executed in parallel.

If a commit operation occurs while a cursor defined as WITH HOLD is open, a final call is made when the cursor is closed or the application ends. If a commit occurs at the end of a parallel task, a final call is made regardless of whether a cursor defined as WITH HOLD is open.

Committable operations should not be performed during a FINAL CALL, because the FINAL CALL may occur during a close that is invoked as part of a COMMIT operation.

ALLOW PARALLEL or DISALLOW PARALLEL

Specifies whether the function can be run in parallel.

The default is DISALLOW PARALLEL if one or more of the following clauses are specified: NOT DETERMINISTIC, EXTERNAL ACTION, FINAL CALL, MODIFIES SQL DATA, or SCRATCHPAD. Otherwise, ALLOW PARALLEL is the default.

ALLOW PARALLEL

Specifies that the database manager can consider parallelism for the function. The database manager is not required to use parallelism on the SQL statement that invokes the function or on any SQL statement issued from within the function.

See the descriptions of NOT DETERMINISTIC, EXTERNAL ACTION, MODIFIES SQL DATA, SCRATCHPAD, and FINAL CALL for considerations that apply to specification of ALLOW PARALLEL.

DISALLOW PARALLEL

Specifies that the database manager must not use parallelism for the function.

NO SCRATCHPAD or SCRATCHPAD

Specifies whether the function requires a static memory area.

NO SCRATCHPAD

Specifies that the function does not require a persistent memory area.

SCRATCHPAD integer

Specifies that the function requires a persistent memory area of length integer. The integer can range from 1 to 16,000,000. If the memory area is not specified, the size of the area is 100 bytes. If parameter style SQL is specified, a pointer is passed following the required parameters that points to a static storage area. If ALLOW PARALLEL is specified, a memory area is allocated for each user-defined function reference in the statement. If DISALLOW PARALLEL is specified, only 1 memory area will be allocated for the function.

The scope of a scratchpad is the SQL statement. For each reference to the function in an SQL statement, there is one scratchpad. For example, assuming that function UDFX was defined with the SCRATCHPAD keyword, three scratchpads are allocated for the three references to UDFX in the following SQL statement:

SELECT A, UDFX(A) 
  FROM TABLEB
  WHERE UDFX(A) > 103 OR UDFX(A) < 19

If the function is run under parallel tasks, one scratchpad is allocated for each parallel task of each reference to the function in the SQL statement. This can lead to unpredictable results. For example, if a function uses the scratchpad to count the number of times that it is invoked, the count reflects the number of invocations done by the parallel task and not the SQL statement. Specify the DISALLOW PARALLEL clause for functions that will not work correctly with parallelism.

SCRATCHPAD is only allowed with PARAMETER STYLE SQL or PARAMETER STYLE DB2GENERAL.

EXTERNAL

Specifies that the CREATE FUNCTION statement is being used to define a new function that is based on code that is written in an external programming language.

If external-program-name is not specified, the external program name is assumed to be the same as the function name.

NAME external-program-name

Specifies the program, service program, or Java class that will be executed when the function is invoked in an SQL statement. The name must identify a program, service program, or Java class that exists at the application server at the time the function is invoked. If the naming option is *SYS and the name is not qualified:

• The current path will be used to search for the program at the time the function is invoked.
• *LIBL will be used to search for the program or service program at the time COMMENT, GRANT, LABEL, or REVOKE operations are performed on the function.

The validity of the name is checked at the application server. If the format of the name is not correct, an error is returned.

The program, service program, or Java class need not exist at the time the function is created, but it must exist at the time the function is invoked.

CONNECT, SET CONNECTION, RELEASE, DISCONNECT, COMMIT, ROLLBACK, and SET TRANSACTION statements are not allowed in the external program of the function.

NOT SECURED or SECURED

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

NOT SECURED

Specifies that the function is considered not secure for row access control and column access control. This 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. This option can be used for an external function that is written in C, C++, ILE RPG, ILE COBOL, ILE CL, or Java. The program or service program must exist when the CREATE FUNCTION statement is executed.

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

SET OPTION-statement

Specifies the options that will be used for parameter defaults. The default values for the options depend on the options in effect at create time. For more information, see SET OPTION.

The following options are used when processing default value expressions: ALWCPYDTA, CONACC, DATFMT, DATSEP, DECFLTRND, DECMPT, DECRESULT, DFTRDBCOL, LANGID, SQLCURRULE, SQLPATH, SRTSEQ, TGTRLS, TIMFMT, and TIMSEP. The options CNULRQD, CNULIGN, COMPILEOPT, EXTIND, NAMING, and SQLCA are not allowed in the CREATE FUNCTION statement. Other options are accepted but will be ignored.

Notes

General considerations for defining user-defined functions: See CREATE FUNCTION for general information on defining user-defined functions.