SYSPARMS

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:

Table 1. 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:
IN
This is an input parameter.
OUT
This is an output parameter.
INOUT
This is an input/output 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:
BIGINT
Big number
INTEGER
Large number
SMALLINT
Small number
DECIMAL
Packed decimal
NUMERIC
Zoned decimal
DOUBLE PRECISION
Floating point; DOUBLE PRECISION
REAL
Floating point; REAL
DECFLOAT
Decimal floating-point
CHARACTER
Fixed-length character string
CHARACTER VARYING
Varying-length character string
CHARACTER LARGE OBJECT
Character large object string
GRAPHIC
Fixed-length graphic string
GRAPHIC VARYING
Varying-length graphic string
DOUBLE-BYTE CHARACTER LARGE OBJECT
Double-byte character large object string
BINARY
Fixed-length binary string
BINARY VARYING
Varying-length binary string
BINARY LARGE OBJECT
Binary large object string
DATE
Date
TIME
Time
TIMESTAMP
Timestamp
DATALINK
Datalink
ROWID
Row ID
Start of changeXMLEnd of change
Start of changeXMLEnd of change
DISTINCT
Distinct type
Start of changeARRAYEnd of change
Start of changeArray typeEnd of change
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

Start of changeThe CCSID value for CHAR, VARCHAR, CLOB, DATE, TIME, TIMESTAMP, GRAPHIC, VARGRAPHIC, DBCLOB, and DATALINK parameters.

Start of changeA CCSID of 0 indicates that the CCSID of the job at run time is used.End of change

XML parameters use the value of SQL_XML_DATA_CCSID from the QAQQINI file.

Contains the null value if the parameter is numeric.

End of change
CHARACTER_MAXIMUM_LENGTH CHARLEN INTEGER

Nullable

Start of changeMaximum 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.

End of change
CHARACTER_OCTET_LENGTH CHARBYTE INTEGER

Nullable

Start of changeNumber of bytes for binary, character, and graphic string and XML data types.

Contains the null value if the parameter is not a string.

End of change
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:
2
Binary; floating-point precision is specified in binary digits.
10
Decimal; all other numeric types are specified in 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.
0
For DATE and TIME data types
6
For TIMESTAMP data types (number of microseconds).

Contains the null value if the parameter is not date, time, or timestamp.

IS_NULLABLE NULLS VARCHAR(3) Indicates whether the parameter is nullable.
NO
The parameter does not allow nulls.
YES
The parameter does allow nulls.
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.

ROW_TYPE ROWTYPE CHAR(1)

Nullable

Indicates the type of row.
P
Parameter.
R
If the function is a table function, this indicates a result column. Otherwise, the result before casting.
C
Result after casting.
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.
NO
The parameter was not specified as a locator.
YES
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.
NO
The value should not be normalized.
YES
The value should be normalized.