DB2 Universal Database for iSeries SQL Reference

SQLPROCEDURECOLS

The SQLPROCEDURECOLS view contains one row for every parameter of a procedure. The following table describes the columns in the view:

Table 140. SQLPROCEDURECOLS view

Column Name Data Type Description
PROCEDURE_CAT VARCHAR(128) Relational database name
PROCEDURE_SCHEM VARCHAR(128) Schema name of the procedure instance.
PROCEDURE_NAME VARCHAR(128) Name of the procedure instance.
COLUMN_NAME VARCHAR(128)
Nullable
Name of a procedure parameter.

Contains the null value if the parameter does not have a name.

COLUMN_TYPE SMALLINT Type of the parameter:

1
IN

2
INOUT

4
OUT
DATA_TYPE SMALLINT The data type of the parameter:

-5
BIGINT

4
INTEGER

5
SMALLINT

3
DECIMAL

2
NUMERIC

8
DOUBLE PRECISION

7
REAL

1
CHARACTER

-2
CHARACTER FOR BIT DATA

12
VARCHAR

-3
VARCHAR FOR BIT DATA

40
CLOB

-95
GRAPHIC

-96
VARGRAPHIC

-350
DBCLOB

-2
BINARY

-3
VARBINARY

30
BLOB

91
DATE

92
TIME

93
TIMESTAMP

70
DATALINK

-100
ROWID

17
DISTINCT
TYPE_NAME VARCHAR(260) The name of the data type of the parameter:

BIGINT
BIGINT

INTeger
INTEGER

SMALLINT
SMALLINT

DECIMAL
DECIMAL

NUMERIC
NUMERIC

FLOAT
DOUBLE PRECISION

REAL
REAL

CHARacter
CHARACTER

CHARacter FOR BIT DATA
CHARACTER FOR BIT DATA

VARCHAR
VARCHAR

VARCHAR FOR BIT DATA
VARCHAR FOR BIT DATA

CLOB
CLOB

GRAPHIC
GRAPHIC

VARGRAPHIC
VARGRAPHIC

DBCLOB
DBCLOB

BINARY
BINARY

VARBINARY
VARBINARY

BLOB
BLOB

DATE
DATE

TIME
TIME

TIMESTAMP
TIMESTAMP

DATALINK
DATALINK

ROWID
ROWID

Qualified Type Name
DISTINCT
COLUMN_SIZE INTEGER Length of the parameter.
BUFFER_LENGTH INTEGER Indicates the length of the parameter in a buffer.
DECIMAL_DIGITS SMALLINT
Nullable
Scale of numeric or datetime data.

Contains the null value if the parameter is not decimal, numeric, binary, time or timestamp.

NUM_PREC_RADIX SMALLINT
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.

NULLABLE SMALLINT Indicates whether the parameter is nullable.

0
The parameter does not allow nulls.

1
The parameter does allow nulls.
REMARKS VARCHAR(2000)
Nullable
A character string supplied with the COMMENT statement.

Contains the null value if there is no long comment.

COLUMN_DEF VARCHAR(1)
Nullable
The default value for the column.

Contains the null value if there is no default value.

SQL_DATA_TYPE SMALLINT The SQL data type of the parameter:

-5
BIGINT

4
INTEGER

5
SMALLINT

3
DECIMAL

2
NUMERIC

8
DOUBLE PRECISION

7
REAL

1
CHARACTER

-2
CHARACTER FOR BIT DATA

12
VARCHAR

-3
VARCHAR FOR BIT DATA

-99
CLOB

-95
GRAPHIC

-96
VARGRAPHIC

-350
DBCLOB

-2
BINARY

-3
VARBINARY

-98
BLOB

9
DATE

10
TIME

11
TIMESTAMP

70
DATALINK

-100
ROWID

17
DISTINCT
SQL_DATETIME_SUB SMALLINT
Nullable
The datetime subtype of the parameter:

1
DATE

2
TIME

3
TIMESTAMP

Contains the null value if the data type is not a datetime data type.

CHAR_OCTET_LENGTH INTEGER
Nullable
Indicates the length in characters of the parameter.

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

ORDINAL_POSITION INTEGER Numeric place of the parameter in the parameter list, ordered from left to right.
IS_NULLABLE VARCHAR(3) Indicates whether the parameter is nullable.

NO
The parameter does not allow nulls.

YES
The parameter does allow nulls.
JDBC_DATA_TYPE SMALLINT The JDBC data type of the parameter:

-5
BIGINT

4
INTEGER

5
SMALLINT

3
DECIMAL

2
NUMERIC

8
DOUBLE PRECISION

7
REAL

1
CHARACTER

-2
CHARACTER FOR BIT DATA

12
VARCHAR

-3
VARCHAR FOR BIT DATA

2005
CLOB

1
GRAPHIC

12
VARGRAPHIC

1111
DBCLOB

-2
BINARY

-3
VARBINARY

2004
BLOB

91
DATE

92
TIME

93
TIMESTAMP

70
DATALINK

1111
ROWID

2001
DISTINCT
I_DATA_TYPE SMALLINT Indicates the iSeries CLI data type of the column.

19
BIGINT

4
INTEGER

5
SMALLINT

3
DECIMAL

2
NUMERIC

8
DOUBLE PRECISION

7
REAL

1
CHARACTER

-2
CHARACTER FOR BIT DATA

12
VARCHAR

-3
VARCHAR FOR BIT DATA

14
CLOB

95
GRAPHIC

96
VARGRAPHIC

15
DBCLOB

-2
BINARY

-3
VARBINARY

13
BLOB

91
DATE

92
TIME

93
TIMESTAMP

16
DATALINK

1111
ROWID

2001
DISTINCT


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