SQL Reference

SYSCAT.FUNCTIONS

Contains a row for each user-defined function (scalar, table or source). Does not include built-in functions.

Column Name Data Type Nullable Description
FUNCSCHEMA VARCHAR(128)
Qualified function name.
FUNCNAME VARCHAR(18)
SPECIFICNAME VARCHAR(18)
The name of the function instance (may be system-generated).
DEFINER VARCHAR(128)
Authorization ID of function definer.
FUNCID INTEGER
Internally-assigned function ID.
RETURN_TYPE SMALLINT
Internal type code of return type of function.
ORIGIN CHAR(1)

B = Built-in
E = User-defined, external
U = User-defined, based on a source
S = System-generated

TYPE CHAR(1)

C = Column function
S = Scalar function
T = Table function

METHOD CHAR(1)

N = Not a method

EFFECT CHAR(2)
Blanks if not a method
PARM_COUNT SMALLINT
Number of function parameters.
PARM_SIGNATURE VARCHAR(180) FOR BIT DATA
Concatenation of up to 90 parameter types, in internal format. Zero length if function takes no parameters.
CREATE_TIME TIMESTAMP
Timestamp of function creation. Set to 0 for Version 1 functions.
QUALIFIER VARCHAR(128)
Value of default schema at object definition time.
WITH_FUNC_ACCESS CHAR(1)
Reserved for future use.
TYPE_PRESERVING CHAR(1)
Reserved for future use.
VARIANT CHAR(1)

Y = Variant (results may differ)
N = Invariant (results are consistent)
Blank if ORIGIN is not E

SIDE_EFFECTS CHAR(1)

E = Function has external side-effects (number of invocations is important)
N = No side-effects
Blank if ORIGIN is not E

FENCED CHAR(1)

Y = Fenced
N = Not fenced
Blank if ORIGIN is not E

NULLCALL CHAR(1)

Y = CALLED ON NULL INPUT
N = RETURNS NULL ON NULL INPUT (function result is implicitly null if operand(s) are null).
Blank if ORIGIN is not E.

CAST_FUNCTION CHAR(1)

Y = This is a cast function
N = This is not a cast function

ASSIGN_FUNCTION CHAR(1)

Y = Implicit assignment function
N = Not an assignment function

SCRATCHPAD CHAR(1)

Y = This function has a scratch pad.
N = This function does not have a scratch pad.
Blank if ORIGIN is not E

FINAL_CALL CHAR(1)

Y = Final call is made to this function at run time end-of-statement.
N = No final call is made.
Blank if ORIGIN is not E

PARALLELIZABLE CHAR(1)

Y = Function can be executed in parallel.
N = Function cannot be executed in parallel.
Blank if ORIGIN is not E

CONTAINS_SQL CHAR(1)
Indicates whether an external function contains SQL.

N = Function does not contain SQL statements.
Blank if ORIGIN is not E

DBINFO CHAR(1)
Indicates whether a DBINFO parameter is passed to an external function.

Y = DBINFO is passed.
N = DBINFO is not passed.
Blank if ORIGIN is not E

RESULT_COLS SMALLINT
For a table function (TYPE=T) contains the number of columns in the result table; otherwise contains 1.
LANGUAGE CHAR(8)
Implementation language of function body. Possible values are C, JAVA, OLE or OLEDB. Blank if ORIGIN is not E.
IMPLEMENTATION VARCHAR(254) Yes If ORIGIN = E, identifies the path/module/function that implements this function. If ORIGIN = U and the source function is built-in, this column contains the name and signature of the source function. Null otherwise.
CLASS VARCHAR(128) Yes If LANGUAGE = JAVA, identifies the class that implements this function. Null otherwise.
JAR_ID VARCHAR(128) Yes If LANGUAGE = JAVA, identifies the jar file that implements this function. Null otherwise.
PARM_STYLE CHAR(8)
Indicates the parameter style declared in the CREATE FUNCTION statement. Values:

DB2SQL
DB2GENRL
JAVA
Blank if ORIGIN is not E

SOURCE_SCHEMA VARCHAR(128) Yes If ORIGIN = U and the source function is a user-defined function, contains the qualified name of the source function. If ORIGIN = U and the source function is built-in, SOURCE_SCHEMA is 'SYSIBM' and SOURCE_SPECIFIC is 'N/A for built-in'. Null if ORIGIN is not U.
SOURCE_SPECIFIC VARCHAR(18) Yes
IOS_PER_INVOC DOUBLE
Estimated number of I/Os per invocation; -1 if not known (0 default).
INSTS_PER_INVOC DOUBLE
Estimated number of instructions per invocation; -1 if not known (450 default).
IOS_PER_ARGBYTE DOUBLE
Estimated number of I/O's per input argument byte; -1 if not known (0 default).
INSTS_PER_ARGBYTE DOUBLE
Estimated number of instructions per input argument byte; -1 if not known (0 default).
PERCENT_ARGBYTES SMALLINT
Estimated average percent of input argument bytes that the function will actually read; -1 if not known (100 default).
INITIAL_IOS DOUBLE
Estimated number of I/O's performed the first/last time the function is invoked; -1 if not known (0 default).
INITIAL_INSTS DOUBLE
Estimated number of instructions executed the first/last time the function is invoked; -1 if not known (0 default).
CARDINALITY BIGINT
The predicted cardinality of a table function. -1 if not known or if function is not a table function.
IMPLEMENTED CHAR(1)
Reserved for future use.
SELECTIVITY DOUBLE
Reserved for future use.
OVERRIDEN_FUNCID INTEGER Yes Reserved for future use.
SUBJECT_TYPESCHEMA VARCHAR(128) Yes Subject type schema for the user defined method.
SUBJECT_TYPENAME VARCHAR(18) Yes Subject type name for the user defined method.
FUNC_PATH VARCHAR(254) Yes Function path at the time the function was defined.
BODY CLOB(1M) Yes Reserved for future use.
REMARKS VARCHAR(254) Yes User-supplied comment, or null.

Column Name	Data Type	Nullable	Description
FUNCSCHEMA	VARCHAR(128)		Qualified function name.
FUNCNAME	VARCHAR(18)
SPECIFICNAME	VARCHAR(18)		The name of the function instance (may be system-generated).
DEFINER	VARCHAR(128)		Authorization ID of function definer.
FUNCID	INTEGER		Internally-assigned function ID.
RETURN_TYPE	SMALLINT		Internal type code of return type of function.
ORIGIN	CHAR(1)		B = Built-in E = User-defined, external U = User-defined, based on a source S = System-generated
TYPE	CHAR(1)		C = Column function S = Scalar function T = Table function
METHOD	CHAR(1)		N = Not a method
EFFECT	CHAR(2)		Blanks if not a method
PARM_COUNT	SMALLINT		Number of function parameters.
PARM_SIGNATURE	VARCHAR(180) FOR BIT DATA		Concatenation of up to 90 parameter types, in internal format. Zero length if function takes no parameters.
CREATE_TIME	TIMESTAMP		Timestamp of function creation. Set to 0 for Version 1 functions.
QUALIFIER	VARCHAR(128)		Value of default schema at object definition time.
WITH_FUNC_ACCESS	CHAR(1)		Reserved for future use.
TYPE_PRESERVING	CHAR(1)		Reserved for future use.
VARIANT	CHAR(1)		Y = Variant (results may differ) N = Invariant (results are consistent) Blank if ORIGIN is not E
SIDE_EFFECTS	CHAR(1)		E = Function has external side-effects (number of invocations is important) N = No side-effects Blank if ORIGIN is not E
FENCED	CHAR(1)		Y = Fenced N = Not fenced Blank if ORIGIN is not E
NULLCALL	CHAR(1)		Y = CALLED ON NULL INPUT N = RETURNS NULL ON NULL INPUT (function result is implicitly null if operand(s) are null). Blank if ORIGIN is not E.
CAST_FUNCTION	CHAR(1)		Y = This is a cast function N = This is not a cast function
ASSIGN_FUNCTION	CHAR(1)		Y = Implicit assignment function N = Not an assignment function
SCRATCHPAD	CHAR(1)		Y = This function has a scratch pad. N = This function does not have a scratch pad. Blank if ORIGIN is not E
FINAL_CALL	CHAR(1)		Y = Final call is made to this function at run time end-of-statement. N = No final call is made. Blank if ORIGIN is not E
PARALLELIZABLE	CHAR(1)		Y = Function can be executed in parallel. N = Function cannot be executed in parallel. Blank if ORIGIN is not E
CONTAINS_SQL	CHAR(1)		Indicates whether an external function contains SQL. N = Function does not contain SQL statements. Blank if ORIGIN is not E
DBINFO	CHAR(1)		Indicates whether a DBINFO parameter is passed to an external function. Y = DBINFO is passed. N = DBINFO is not passed. Blank if ORIGIN is not E
RESULT_COLS	SMALLINT		For a table function (TYPE=T) contains the number of columns in the result table; otherwise contains 1.
LANGUAGE	CHAR(8)		Implementation language of function body. Possible values are C, JAVA, OLE or OLEDB. Blank if ORIGIN is not E.
IMPLEMENTATION	VARCHAR(254)	Yes	If ORIGIN = E, identifies the path/module/function that implements this function. If ORIGIN = U and the source function is built-in, this column contains the name and signature of the source function. Null otherwise.
CLASS	VARCHAR(128)	Yes	If LANGUAGE = JAVA, identifies the class that implements this function. Null otherwise.
JAR_ID	VARCHAR(128)	Yes	If LANGUAGE = JAVA, identifies the jar file that implements this function. Null otherwise.
PARM_STYLE	CHAR(8)		Indicates the parameter style declared in the CREATE FUNCTION statement. Values: DB2SQL DB2GENRL JAVA Blank if ORIGIN is not E
SOURCE_SCHEMA	VARCHAR(128)	Yes	If ORIGIN = U and the source function is a user-defined function, contains the qualified name of the source function. If ORIGIN = U and the source function is built-in, SOURCE_SCHEMA is 'SYSIBM' and SOURCE_SPECIFIC is 'N/A for built-in'. Null if ORIGIN is not U.
SOURCE_SPECIFIC	VARCHAR(18)	Yes
IOS_PER_INVOC	DOUBLE		Estimated number of I/Os per invocation; -1 if not known (0 default).
INSTS_PER_INVOC	DOUBLE		Estimated number of instructions per invocation; -1 if not known (450 default).
IOS_PER_ARGBYTE	DOUBLE		Estimated number of I/O's per input argument byte; -1 if not known (0 default).
INSTS_PER_ARGBYTE	DOUBLE		Estimated number of instructions per input argument byte; -1 if not known (0 default).
PERCENT_ARGBYTES	SMALLINT		Estimated average percent of input argument bytes that the function will actually read; -1 if not known (100 default).
INITIAL_IOS	DOUBLE		Estimated number of I/O's performed the first/last time the function is invoked; -1 if not known (0 default).
INITIAL_INSTS	DOUBLE		Estimated number of instructions executed the first/last time the function is invoked; -1 if not known (0 default).
CARDINALITY	BIGINT		The predicted cardinality of a table function. -1 if not known or if function is not a table function.
IMPLEMENTED	CHAR(1)		Reserved for future use.
SELECTIVITY	DOUBLE		Reserved for future use.
OVERRIDEN_FUNCID	INTEGER	Yes	Reserved for future use.
SUBJECT_TYPESCHEMA	VARCHAR(128)	Yes	Subject type schema for the user defined method.
SUBJECT_TYPENAME	VARCHAR(18)	Yes	Subject type name for the user defined method.
FUNC_PATH	VARCHAR(254)	Yes	Function path at the time the function was defined.
BODY	CLOB(1M)	Yes	Reserved for future use.
REMARKS	VARCHAR(254)	Yes	User-supplied comment, or null.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]