This CREATE FUNCTION (External Scalar) statement defines an external scalar function at the current server. A user-defined external scalar function returns a single value each time it is invoked.
This statement can be embedded in an application program, or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization id of the statement must include at least one of the following:
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:
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:
If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:
To replace an existing function, the
privileges held by the authorization ID of the statement must include
at least one of the following:

The system authority *READ to the SYSFUNCS catalog
view and SYSPARMS catalog table
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.
>>-CREATE--+------------+--FUNCTION--function-name--------------> '-OR REPLACE-' >--(--+---------------------------+--)--------------------------> | .-,---------------------. | | V | | '---parameter-declaration-+-' >--RETURNS -+-data-type2--+------------+------------------------+--> | '-AS LOCATOR-' | '-data-type3--CAST FROM--data-type4--+------------+-' '-AS LOCATOR-' >--option-list-------------------------------------------------><
parameter-declaration |--+----------------+--data-type1--+-----------------------+----| '-parameter-name-' '-AS -+-LOCATOR-------+-' '-XML-cast-type-' data-type1, data-type2, data-type3, data-type4 |--+-built-in-type------+---------------------------------------| '-distinct-type-name-' XML-cast-type .-(--1--)-------. |--+-+-+-CHARACTER-+--+---------------+--+--------------+-----------------------------------------------+-+--| | | '-CHAR------' '-(--integer--)-' '-ccsid-clause-' | | | +-+-+-CHARACTER-+--VARYING-+--(--integer--)--+--------------+--------------------------------------+ | | | | '-CHAR------' | '-ccsid-clause-' | | | | '-VARCHAR----------------' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+--------------+--+---------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' '-ccsid-clause-' '-LOCATOR-' | | '-CLOB------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+--+--------------+--------------------+------------------------------+ | | '-(--integer--)-' '-ccsid-clause-' | | | +-+-GRAPHIC VARYING-+--(--integer--)--+--------------+----------------+ | | | '-VARGRAPHIC------' '-ccsid-clause-' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+--+--------------+--+---------+-' | | '-(--integer--+---+--)-' '-ccsid-clause-' '-LOCATOR-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | '-+-+-BINARY--+---------------+---------+------------------------------+-------------------------------' | | '-(--integer--)-' | | | '-+-BINARY VARYING-+--(--integer--)-' | | '-VARBINARY------' | | .-(--1M--)-------------. | '---+-BLOB----------------+----+----------------------+--+---------+-' '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' '-LOCATOR-' +-K-+ +-M-+ '-G-'
built-in-type |--+-+---SMALLINT---+-------------------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+----------------------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+-------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+-----------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+---------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | +---ROWID----------------------------------------------------------------------------------------------+ '---XML--+------------------+--------------------------------------------------------------------------' | (1) | '-ccsid-clause-----'
ccsid-clause |--CCSID--integer--+----------------------+---------------------| | (1) | '-normalize-clause-----' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------|
option-list (1) |--+-----------------------+------------------------------------> '-LANGUAGE--+-C-------+-' +-C++-----+ +-CL------+ +-COBOL---+ +-COBOLLE-+ +-JAVA----+ +-PLI-----+ +-RPG-----+ '-RPGLE---' .-PARAMETER STYLE SQL--------------------. >--+----------------------------------------+-------------------> '-+-PARAMETER STYLE JAVA---------------+-' +-PARAMETER STYLE GENERAL------------+ +-PARAMETER STYLE GENERAL WITH NULLS-+ '-PARAMETER STYLE DB2GENERAL---------' .-NOT DETERMINISTIC . >--+-------------------------+--+------------------------+------> '-SPECIFIC--specific-name-' '-DETERMINISTIC----------' .-READS SQL DATA----. .-CALLED ON NULL INPUT ------. >--+-------------------+--+----------------------------+--------> +-NO SQL------------+ '-RETURNS NULL ON NULL INPUT-' +-CONTAINS SQL------+ '-MODIFIES SQL DATA-' .-INHERIT SPECIAL REGISTERS-. .-STATIC DISPATCH-. >--+---------------------------+--+-----------------+-----------> .-NO DBINFO . .-EXTERNAL ACTION----. .-FENCED-----. >--+-----------+--+--------------------+--+------------+--------> '-DBINFO----' '-NO EXTERNAL ACTION-' '-NOT FENCED-' .-NO FINAL CALL-. >--+-------------------+--+---------------+---------------------> +-PROGRAM TYPE MAIN-+ '-FINAL CALL----' '-PROGRAM TYPE SUB--' .-NO SCRATCHPAD-----------. >--+-------------------+--+-------------------------+-----------> +-ALLOW PARALLEL----+ | .-100-----. | '-DISALLOW PARALLEL-' '-SCRATCHPAD--+---------+-' '-integer-' .-EXTERNAL-----------------------------. >--+--------------------------------------+---------------------| '-EXTERNAL NAME--external-program-name-'
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.
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:
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.
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.
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.
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.

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).
If a CCSID is specified,
If a CCSID is not specified,
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.

CREATE FUNCTION SQRT (DECIMAL15,0)) RETURNS DECIMAL(15,0) CAST FROM DOUBLE ...The value of data-type4 must not be 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.
For more information on the AS LOCATOR clause, see Specifying AS LOCATOR for a parameter.
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:
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.
The user may set the SQLSTATE to any valid value in the external program to return an error or warning from the function.
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.
DB2GENERAL is only allowed when the LANGUAGE is JAVA.
return_val func(parameter-1, parameter-2, ...)
GENERAL is only allowed when EXTERNAL NAME identifies a service program.
return_val func(parameter-1, parameter-2, ...)
GENERAL WITH NULLS is only allowed when EXTERNAL NAME identifies a service program.
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.
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.
The default is READS SQL DATA.
DBINFO is only allowed with PARAMETER STYLE SQL or PARAMETER STYLE DB2GENERAL.
| 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
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:
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:
|
| Version and release | CHAR(8) | The version, release, and modification level of the database manager. |
| Platform | INTEGER | The server's platform type. |
NO EXTERNAL ACTION functions might perform better than EXTERNAL ACTION functions because they might not be invoked for each successive function invocation.
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 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.
FINAL CALL is only allowed with PARAMETER STYLE SQL or PARAMETER STYLE DB2GENERAL.
The types of calls are:
A final call occurs at these times:
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.
Commitable 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.
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.
See the descriptions of NOT DETERMINISTIC, EXTERNAL ACTION, MODIFIES SQL DATA, SCRATCHPAD, and FINAL CALL for considerations that apply to specification of ALLOW PARALLEL.
SELECT A, UDFX(A) FROM TABLEB WHERE UDFX(A) > 103 OR UDFX(A) < 19If 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.
If external-program-name is not specified, the external program name is assumed to be the same as the function name.
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.
General considerations for defining user-defined functions: See CREATE FUNCTION for general information on defining user-defined functions.


Creating the function: When an external function associated with an ILE external program or service program is created, an attempt is made to save the function's attributes in the associated program or service program object. If the *PGM or *SRVPGM object is saved and then restored to this or another system, the catalogs are automatically updated with those attributes.
The attributes can be saved for external functions subject to the following restrictions:
If system naming is specified and the external program name is not qualified, the external program must be found in the library list.
If the object cannot be updated, the function will still be created.
During restore of the function:
Invoking the function: When an external function is invoked, it runs in whatever activation group was specified when the external program or service program was created. However, ACTGRP(*CALLER) should normally be used so that the function runs in the same activation group as the calling program. ACTGRP(*NEW) is not allowed.
LANGUAGE JAVA functions always run in the default activation
group (*DFTACTGRP). Caution should be used when writing MODIFIES SQL
DATA Java functions. Since changes performed by the Java function
are performed in the default activation group, transaction problems
may occur if the invoker runs in a new activation group (*NEW).
Notes for Java functions: To be able to run Java functions, you must have the IBM Developer Kit for Java (5761-JV1) installed on your system. Otherwise, an SQLCODE of -443 will be returned and a CPDB521 message will be placed in the job log.
If an error occurs while running a Java function, an SQLCODE of -443 will be returned. Depending on the error, other messages may exist in the job log of the job where the function was run.
MODIFIES SQL DATA and EXTERNAL ACTION functions: If a MODIFIES SQL DATA or EXTERNAL ACTION function is invoked in other than the outermost select list, the results are unpredictable since the number of times the function is invoked will vary depending on the access plan used.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
Example 1: Assume an external function program in C is needed that implements the following logic:
rslt = 2 * input - 4
The function should return a null value if and only if one of the input arguments is null. The simplest way to avoid a function call and get a null result when an input value is null is to specify RETURNS NULL ON NULL INPUT on the CREATE FUNCTION statement. The following statement defines the function, using the specific name MINENULL1.
CREATE FUNCTION NTEST1 (SMALLINT) RETURNS SMALLINT EXTERNAL NAME NTESTMOD SPECIFIC MINENULL1 LANGUAGE C DETERMINISTIC NO SQL FENCED PARAMETER STYLE SQL RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION
The program code:
void nudft1
(int *input, /* ptr to input argument */
int *output, /* ptr to output argument */
short *input_ind, /* ptr to input indicator */
short *output_ind, /* ptr to output indicator */
char sqlstate[6], /* sqlstate */
char fname[140], /* fully qualified function name */
char finst[129], /* function specific name */
char msgtext[71]) /* msg text buffer */
{
if (*input_ind == -1)
*output_ind = -1;
else
{
*output = 2*(*input)-4;
*output_ind = 0;
}
return;
}
Example 2: Assume that a user wants to define an external function named CENTER. The function program will be written in C. The following statement defines the function, and lets the database manager generate a specific name for the function. The name of the program containing the function body is the same as the name of the function, so the EXTERNAL clause does not include 'NAME external-program-name'.
CREATE FUNCTION CENTER (INTEGER, FLOAT) RETURNS FLOAT LANGUAGE C DETERMINISTIC NO SQL PARAMETER STYLE SQL NO EXTERNAL ACTION
Example 3: Assume that user McBride (who has administrative authority) wants to define an external function named CENTER in the SMITH schema. McBride plans to give the function specific name FOCUS98. The function program uses a scratchpad to perform some one-time only initialization and save the results. The function program returns a value with a DOUBLE data type. The following statement written by user McBride defines the function and ensures that when the function is invoked, it returns a value with a data type of DECIMAL(8,4).
CREATE FUNCTION SMITH.CENTER (DOUBLE, DOUBLE, DOUBLE) RETURNS DECIMAL(8,4) CAST FROM DOUBLE EXTERNAL NAME CMOD SPECIFIC FOCUS98 LANGUAGE C DETERMINISTIC NO SQL FENCED PARAMETER STYLE SQL NO EXTERNAL ACTION SCRATCHPAD NO FINAL CALL
Example 4: The following example defines a Java user-defined function that returns the position of the first vowel in a string. The user-defined function is written in Java, is to be run fenced, and is the FINDVWL method of class JAVAUDFS.
CREATE FUNCTION FINDV (VARCHAR(32000)) RETURNS INTEGER FENCED LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'JAVAUDFS.FINDVWL' NO EXTERNAL ACTION CALLED ON NULL INPUT DETERMINISTIC NO SQL