SQL Reference

SYSCAT.FUNCTIONS

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

Note:

Descriptions that state "functions" also apply to methods, unless otherwise stated.

Table 62. SYSCAT.FUNCTIONS Catalog View

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 Q = User-defined, SQL U = User-defined, based on a source S = System-generated
TYPE	CHAR(1)		C = Column function R = Row function S = Scalar function T = Table function
METHOD	CHAR(1)		Y = Method N = Not a method
EFFECT	CHAR(2)		MU = mutator method OB = observer method CN = constructor method Blanks = Not a system-generated 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)		Y = This method can be invoked by using functional notation N = This method cannot be invoked by using functional notation
TYPE_PRESERVING	CHAR(1)		Y = Return type is governed by a "type-preserving" parameter. All system-generated mutator methods are type-preserving. N = Return type is the declared return type of the method.
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 a function or method contains SQL. C = CONTAINS SQL: only SQL that does not read or modify SQL data is allowed. N = NO SQL: SQL is not allowed. R = READS SQL DATA: only SQL that reads SQL data is allowed.
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 or Q.
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)		Y = function is implemented. M = method is implemented and does not have function access. See note 1. H = method is implemented and has function access. See note 1. N = method specification without an implementation.
SELECTIVITY	DOUBLE		Used for user-defined predicates. -1 if there are no user-defined predicates. See Note 2.
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	When language is SQL, the text of the CREATE FUNCTION or CREATE METHOD statement.
REMARKS	VARCHAR(254)	Yes	User-supplied comment, or null.
Note: This value may not appear in future versions of DB2 This column will be set to -1 during migration in the packed descriptor and system catalogs for all user-defined functions. For a user-defined predicate, the selectivity in the system catalog will be -1. In this case, the selectivity value used by the optimizer is 0.01.