SYSFUNCS

The SYSFUNCS view contains one row for each function created by the CREATE FUNCTION statement.

The following table describes the columns in the SYSFUNCS view:

Table 1. SYSFUNCS view
Column Name System Column Name Data Type Description
SPECIFIC_SCHEMA SPECSCHEMA VARCHAR(128) Schema name of the routine (function) instance.
SPECIFIC_NAME SPECNAME VARCHAR(128) Specific name of the routine instance.
ROUTINE_SCHEMA FUNCSCHEMA VARCHAR(128) Name of the SQL schema (schema) that contains the routine.
ROUTINE_NAME FUNCNAME VARCHAR(128) Name of the routine.
ROUTINE_CREATED RTNCREATE TIMESTAMP Identifies the timestamp when the routine was created.
ROUTINE_DEFINER DEFINER VARCHAR(128) Name of the user that defined the routine.
ROUTINE_BODY BODY VARCHAR(8) The type of the routine body:
EXTERNAL
This is an external routine.
SQL
This is an SQL routine.
EXTERNAL_NAME EXTNAME VARCHAR(279)

Nullable

This column identifies the external program name.
  • For SQL functions or ILE service programs, the external program name is schema-name/service-program-name(entry-point-name).
  • For Java™ programs, the external program name is an optional jar-id followed by a fully-qualified-class-name!method-name or fully-qualified-class-name.method-name.
  • For all other languages, the external program name is schema-name/program-name.

Contains the null value if this is a system-generated function.

EXTERNAL_LANGUAGE LANGUAGE VARCHAR(8)

Nullable

If this is an external routine, this column identifies the external program name.
C      
The external program is written in C.
C++      
The external program is written in C++.
CL     
The external program is written in 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.
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.

Contains the null value if this is not an external routine.

PARAMETER_STYLE PARM_STYLE VARCHAR(7)

Nullable

If this is an external routine, this column identifies the parameter style (calling convention).
DB2SQL
This is the DB2SQL calling convention.
DB2GNRL
This is the DB2GENERAL calling convention.
GENERAL
This is the GENERAL calling convention.
JAVA
This is the JAVA calling convention.
NULLS
This is the GENERAL WITH NULLS calling convention.
SQL
This is the SQL standard calling convention.

Contains the null value if this is not an external routine.

IS_DETERMINISTIC DETERMINE VARCHAR(3) This column identifies whether the routine is deterministic. That is, whether a call to the routine with the same arguments will always return the same result.
NO
The routine is not deterministic.
YES
The routine is deterministic.
SQL_DATA_ACCESS DATAACCESS VARCHAR(8)

Nullable

This column identifies whether a routine contains SQL and whether it reads or modifies data.
NONE
The routine does not contain any SQL statements.
CONTAINS
The routine contains SQL statements.
READS
The routine possibly reads data from a table or view.
MODIFIES
The routine possibly modifies data in a table or view or issues SQL DDL statements.
SQL_PATH SQL_PATH VARCHAR(3483)

Nullable

If this is an SQL routine, this column identifies the path.

Contains the null value if this is an external routine.

PARM_SIGNATURE SIGNATURE VARCHAR(2048) This column identifies the routine signature.
NUMBER_OF_RESULTS NUMRESULTS SMALLINT

Nullable

Identifies the number of results.
IN_PARMS IN_PARMS SMALLINT Identifies the number of input parameters. 0 indicates that there are no input parameters.
LONG_COMMENT REMARKS Start of changeVARGRAPHIC(2000) CCSID 1200

Nullable

End of change
A character string supplied with the COMMENT statement.

Contains the null value if there is no long comment.

Start of changeROUTINE_DEFINITIONEnd of change Start of changeROUTINEDEFEnd of change Start of changeDBCLOB(2M)    CCSID 13488

Nullable

End of change
Start of changeIf this is an SQL routine, this column contains the SQL routine body.

Start of changeContains the null value if this is not an SQL routine.End of change

End of change
FUNCTION_ORIGIN ORIGIN CHAR(1) Identifies the type of function. If this is a procedure, this column contains a blank.
B
This is a built-in function (defined by DB2® for i).
E
This is a user-defined function.
U
This is a user-defined function that is based on another function.
S
This is a system-generated function.
FUNCTION_TYPE TYPE CHAR(1) Identifies the form of the function. If this is a procedure, this column contains a blank.
S
This is a scalar function.
C
This is a column function.
T
This is a table function.
EXTERNAL_ACTION EXT_ACTION CHAR(1)

Nullable

Identifies the whether the invocation of the function has external effects.
E
This function has external side effects.
N
This function does not have any external side effects.
IS_NULL_CALL NULL_CALL VARCHAR(3)

Nullable

Identifies whether the function needs to be called if an input parameter is the null value.
NO
This function need not be called if an input parameter is the null value. If this is a scalar function, the result of the function is implicitly null if any of the operands are null. If this is a table function, the result of the function is an empty table if any of the operands are the null value.
YES
This function must be called even if an input operand is null.
SCRATCH_PAD SCRATCHPAD INTEGER

Nullable

Identifies whether the address of a static memory area (scratch pad) is passed to the function.
0
The function does not have a scratch pad.
integer
Indicates the size of the scratch pad passed to the function.
FINAL_CALL FINAL_CALL VARCHAR(3)

Nullable

Indicates whether a final call to the function should be made to allow the function to clean up its work areas (scratch pads).
NO
No final call is made.
YES
A final call to the function is made when the statement is complete.
PARALLELIZABLE PARALLEL VARCHAR(3)

Nullable

Identifies whether the function can be run in parallel.
NO
The function must be synchronous.
YES
The function can be run in parallel.
DBINFO DBINFO VARCHAR(3)

Nullable

Identifies whether information about the database is passed to the function.
NO
No database information is passed to the function.
YES
Information about the database is passed to the function.
SOURCE_ SPECIFIC_SCHEMA SRCSCHEMA VARCHAR(128)

Nullable

If this is sourced function and the source is user-defined, this column contains the name of the source schema. If this is a sourced function and the source is built-in, this column contains 'QSYS2'.

Contains the null value if this is not a sourced function.

SOURCE_SPECIFIC_NAME SRCNAME VARCHAR(128)

Nullable

If this is sourced function and the source is user-defined, this column contains the specific name of the source function name.

Contains the null value if this is not a sourced function.

IS_USER_DEFINED_CAST CAST_FUNC VARCHAR(3)

Nullable

Identifies whether this function is a cast function created when a distinct type was created.
NO
This function is not a cast function.
YES
This function is a cast function.
CARDINALITY CARD BIGINT

Nullable

Specifies the cardinality for a table function.

Contains the null value if the function is not a table function or if cardinality was not specified.

FENCED FENCED VARCHAR(3)

Nullable

Identifies whether the function is fenced.
NO
The function is not fenced.
YES
The function is fenced.
IASP_NUMBER IASPNUMBER SMALLINT Specifies the independent auxiliary storage pool (IASP) number.
ROUNDING_MODE DECFLTRND CHAR(1)

Nullable

If this is an SQL function, identifies the DECFLOAT rounding mode.
C
ROUND_CEILING
D
ROUND_DOWN
F
ROUND_FLOOR
G
ROUND_HALF_DOWN
E
ROUND_HALF_EVEN
H
ROUND_HALF_UP
U
ROUND_UP

Contains the null value if the function is not an SQL function.

Start of changeINLINEEnd of change Start of changeINLINEEnd of change Start of changeVARCHAR(3)

Nullable

End of change
Start of changeIdentifies whether the function can potentially be inlined.
NO
The function cannot be inlined.
YES
The function can be inlined.

Contains the null value if the function is not an SQL function.

End of change
Start of changeROUTINE_TEXTEnd of change Start of changeLABELEnd of change Start of changeVARGRAPHIC(50) CCSID 1200

Nullable

End of change
Start of changeContains the label for a routine. Contains the null value if a label does not exist.End of change