DB2 Universal Database for iSeries SQL Reference

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 following table describes the columns in the SYSPARMS table:

Table 121. 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.
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 column:

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

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

DISTINCT
Distinct type
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 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.

A CCSID of 0 indicates that the CCSID of the job at run time is used.

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 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 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:

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 VARCHAR(2000)

Nullable
A character string supplied with the COMMENT statement.

Contains the null value if there is no long comment.

ROW_TYPE ROWTYPE CHAR(1) Indicates the type of row. If this is a parameter to a procedure, this column contains the null value.

P
Parameter.

R
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) 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.


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