The SYSPARMS table contains one row for each parameter of a procedure created by the CREATE PROCEDURE statement or function created by the CREATE FUNCTION statement. The result of a scalar function and the result columns of a table function are also returned.
The following table describes the columns in the SYSPARMS table:
| Column Name | System Column Name | Data Type | Description |
|---|---|---|---|
| SPECIFIC_SCHEMA | SPECSCHEMA | VARCHAR(128) | Schema name of the routine instance. |
| SPECIFIC_NAME | SPECNAME | VARCHAR(128) | Specific name of the routine instance. |
| ORDINAL_POSITION | PARMNO | INTEGER | Numeric place of the parameter in
the parameter list, ordered from left to right from 1 (leftmost parameter)
to n (nth parameter). For scalar functions, the result of the function has a value of n+1. For table functions, the result columns are numbered from n+1 (leftmost result column) to n+m (mth result column). |
| PARAMETER_MODE | PARMMODE | VARCHAR(5) | Type of the parameter:
|
| PARAMETER_NAME | PARMNAME | VARCHAR(128) Nullable |
Name of the parameter. Contains the null value if the parameter does not have a name. |
| DATA_TYPE | DATA_TYPE | VARCHAR(128) | Type of parameter:
|
| NUMERIC_SCALE | SCALE | INTEGER Nullable |
Scale of numeric data. Contains the null value if the parameter is not decimal, numeric, or binary. |
| NUMERIC_PRECISION | PRECISION | INTEGER Nullable |
The precision of all numeric parameters.
Note: This column supplies the precision of all numeric data types,
including decimal floating-point and single-and double-precision floating
point. The NUMERIC_PRECISION_RADIX column indicates if the value in
this column is in binary or decimal digits.
Contains the null value if the parameter is not numeric. |
| CCSID | CCSID | INTEGER Nullable |
The CCSID value for CHAR,
VARCHAR, CLOB, DATE, TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC, DBCLOB,
and DATALINK parameters.
XML parameters use the value of SQL_XML_DATA_CCSID from the QAQQINI file. Contains the null value if the parameter is numeric. ![]() |
| CHARACTER_MAXIMUM_LENGTH | CHARLEN | INTEGER Nullable |
Maximum length of the
string for binary, character, and graphic string and XML data types.
Contains the null value if the parameter is not a string. ![]() |
| CHARACTER_OCTET_LENGTH | CHARBYTE | INTEGER Nullable |
Number of bytes for binary,
character, and graphic string and XML data types. Contains the null value if the parameter is not a string. ![]() |
| NUMERIC_PRECISION_RADIX | RADIX | INTEGER Nullable |
Indicates if the precision specified
in column NUMERIC_PRECISION is specified as a number of binary or
decimal digits:
Contains the null value if the parameter is not numeric. |
| DATETIME_PRECISION | DATPRC | INTEGER Nullable |
The fractional part of a date, time,
or timestamp.
Contains the null value if the parameter is not date, time, or timestamp. |
| IS_NULLABLE | NULLS | VARCHAR(3) | Indicates whether the parameter is
nullable.
|
| LONG_COMMENT | REMARKS | VARGRAPHIC(2000) CCSID
1200Nullable ![]() |
A character string supplied with
the COMMENT statement. Contains the null value if there is no long comment. |
| ROW_TYPE | ROWTYPE | CHAR(1) Nullable |
Indicates the type of row.
|
| DATA_TYPE_SCHEMA | TYPESCHEMA | VARCHAR(128) Nullable |
Schema of the data type if this is
a distinct type. Contains the null value if the parameter is not a distinct type. |
| DATA_TYPE_NAME | TYPENAME | VARCHAR(128) Nullable |
Name of the data type if this is
a distinct type. Contains the null value if the parameter is not a distinct type. |
| AS_LOCATOR | ASLOCATOR | VARCHAR(3) | Indicates whether the parameter was
specified as a locator.
|
| IASP_NUMBER | IASPNUMBER | SMALLINT | Specifies the independent auxiliary storage pool (IASP) number. |
| NORMALIZE_DATA | NORMALIZE | VARCHAR(3) Nullable |
Indicates whether the parameter value
should be normalized or not. This attribute only applies to UTF-8
and UTF–16 data.
|