SYSPARMS catalog table

The SYSPARMS table contains a row for each parameter of a routine or multiple rows for table parameters (one for each column of the table). The schema is SYSIBM.

Table 1. SYSIBM.SYSPARMS table column descriptions
Column name Data type Description Use
SCHEMA
VARCHAR(128)
NOT NULL
 Schema of the routine. G
OWNER
VARCHAR(128)
NOT NULL
 Owner of the routine. G
NAME
VARCHAR(128)
NOT NULL
 Name of the routine. G
SPECIFICNAME
VARCHAR(128)
NOT NULL
 Specific name of the routine. G
ROUTINETYPE
CHAR(1)
NOT NULL
 Type of routine:
F
User-defined function or cast function
P
Stored procedure
 G
CAST_FUNCTION
CHAR(1)
NOT NULL
 Whether the routine is a cast function:
N
Not a cast function
Y
A cast function

The only way to get a value of Y is if a user creates a distinct type when Db2 implicitly generates cast functions for the distinct type.

 G
PARMNAME
VARCHAR(128)
NOT NULL
 Name of the parameter. For a table parameter, the parameter name in the row corresponding to the first column of the table is the parameter name specified on CREATE; an empty string or blanks are stored for the parameter name for the rows corresponding to the remaining columns. G
ROUTINEID
INTEGER
NOT NULL
 Internal identifier of the routine. S
ROWTYPE
CHAR(1)
NOT NULL
 The following values indicate the type of parameter described by this row:
P
Input parameter.
O
Output parameter; not applicable for functions
B
Both an input and an output parameter; not applicable for functions
R
Result before casting; not applicable for stored procedures
C
Result after casting; not applicable for stored procedures
S
Input parameter of the underlying built-in source function. For a sourced function and a given ORDINAL value:
  • The row with ROWTYPE = P describes the input parameter of the user-defined function (identified by ROUTINEID).
  • The row with ROWTYPE = S describes the corresponding input parameter of the built-in function that is the underlying source function (identified by the SOURCESCHEMA and SOURCESPECIFIC values).

A value of 'X' indicates that the row is not used to describe a particular parameter of the routine. Instead, for a routine that was created prior to Version 9, the row is used to record a CCSID for the encoding scheme specified in a PARAMETER CCSID clause, or a DATATYPEID for the representation of the variable length character string parameters of a LANGUAGE C routine, as specified in a PARAMETER VARCHAR clause. For routines created with Version 8 (new function mode) or later releases, the CCSID is recorded in the PARAMETER_CCSID column of SYSROUTINES. For routines created with Version 9 or later releases, the DATATYPEID information to support PARAMETER VARCHAR is recorded in the PARAMETER_VARCHARFORM column of SYSIBM.SYSROUTINES.

 G
ORDINAL
SMALLINT
NOT NULL
 If ROWTYPE is B, O, P, or S, the value is the ordinal number of the parameter within the routine signature.

If ROWTYPE is C or R, the value depends on the type of function:

  • For a scalar function, the value is 0.
  • For a table function, the value is the ordinal number of the column of the output table.

If ROWTYPE is X, the value is 0.

 G
TYPESCHEMA
VARCHAR(128)
NOT NULL
 Schema of the data type of the parameter. G
TYPENAME
VARCHAR(128)
NOT NULL
 Name of the data type of the parameter. G
DATATYPEID
INTEGER
NOT NULL
 For a built-in data type, the internal ID of the built-in type. For a distinct type, the internal ID of the distinct type.

When ROWTYPE is X and ORDINAL is 0, a non-zero DATATYPEID indicates that actual representation, for a LANGUAGE C routine, of any varying length string parameters that appear in the routine's parameter list ot in the RETURNS clause.

 S
SOURCETYPEID
INTEGER
NOT NULL
 For a built-in data type, 0. For a distinct type, the internal ID of the built-in data type upon which the distinct type is based. S
LOCATOR
CHAR(1)
NOT NULL
 Indicates whether a locator to a value, instead of the actual value, is to be passed or returned when the routine is called:
N
The actual value is to be passed.
Y
A locator to a value is to be passed
 G
TABLE
CHAR(1)
NOT NULL
 The data type of a column for a table parameter:
N
This is not a table parameter.
Y
This is a table parameter.
 G
TABLE_COLNO
SMALLINT
NOT NULL
 For table parameters, the column number of the table. Otherwise, the value is 0. G
LENGTH
INTEGER
NOT NULL
 Length attribute of the parameter or result; If the parameter or result length is determined during function resolution, the length attribute can also be 0. In the case of a decimal parameter or result this is the precision.

If the parameter is an array, the value is 0.

 G
SCALE
SMALLINT
NOT NULL
 Scale of the data type of the parameter or number of fractional second digits of timestamp or timestamp with time zone parameter. If it is TIMESTAMP parameter where LENGTH is 10 and SCALE is 0, the number of fractional second digits is 6. G
SUBTYPE
CHAR(1)
NOT NULL
 If the data type is a distinct type, the subtype of the distinct type, which is based on the subtype of its source type:
B
The subtype is FOR BIT DATA.
S
The subtype is FOR SBCS DATA.
M
The subtype is FOR MIXED DATA.
blank
The source type is not a character type.

If the parameter is an array type, the value is blank.

 G
CCSID
INTEGER
NOT NULL

CCSID of the data type for a character, date, time, timestamp or graphic data type. If the parameter is a datetime array, the value is 0. (not null)

When ROWTYPE is X and ORDINAL is 0, the CCSID column is the CCSID for all character and graphic string parameters.

 G
CAST_FUNCTION_ID
INTEGER
NOT NULL
 Internal function ID of the function used to cast the argument, if this function is sourced on another function, or result. Otherwise, the value is 0. Not applicable for stored procedures. S
ENCODING_SCHEME
CHAR(1)
NOT NULL
 Encoding scheme of the parameter:
A
ASCII
E
EBCDIC
U
Unicode
blank
The source type is not a character, graphic, or datetime type.

If the parameter is an array type, the value is blank.

 G
IBMREQD
CHAR(1)
NOT NULL
 A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators.

The value in this field is not a reliable indicator of release dependencies.

 G
VERSION
VARCHAR(122)
NOT NULL WITH
DEFAULT
 The version identifier for the routine. The column is a zero-length string if the value of ORIGIN is not 'I' or if the rows were created prior to Version 9. G
OWNERTYPE
CHAR(1)
NOT NULL WITH
DEFAULT
 Indicates the type of owner:
blank
Authorization ID
L
Role
 G