DB2 Universal Database for iSeries SQL Reference

SQLCOLUMNS

The SQLCOLUMNS view contains one row for every column in a table, view, or alias. The following table describes the columns in the view:

Table 137. SQLCOLUMNS view

Column Name Data Type Description
TABLE_CAT VARCHAR(128) Relational database name.
TABLE_SCHEM VARCHAR(128) Name of the SQL schema that contains the table.
TABLE_NAME VARCHAR(128) Table name.
COLUMN_NAME VARCHAR(128) Column name.
DATA_TYPE SMALLINT The data type of the column:

-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(128) The name of the data type of the column:

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 The length of the column.
BUFFER_LENGTH INTEGER Indicates the length of the column in a buffer.
DECIMAL_DIGITS SMALLINT
Nullable
Indicates the number of digits for a numeric column.

Contains the null value if the object is not numeric.

NUM_PREC_RADIX SMALLINT
Nullable
Indicates the radix of a numeric column.

Contains the null value if the object is not numeric.

NULLABLE SMALLINT Indicates whether the column can contain the null value.

0
The column does not allow nulls.

1
The column 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(2000)
Nullable
The default value of the column.

Contains the null value if there is no default value.

SQL_DATA_TYPE SMALLINT Indicates the SQL data type of the column.
SQL_DATETIME_SUB SMALLINT
Nullable
The datetime subtype of the data type:

1
DATE

2
TIME

3
TIMESTAMP

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

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

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

ORDINAL_POSITION INTEGER Indicates the ordinal position of the column in the table.
IS_NULLABLE VARCHAR(3) Indicates whether the column can contain the null value.

NO
The column is not nullable.

YES
The column is nullable.
JDBC_DATA_TYPE SMALLINT Indicates the JDBC data type of the column.

-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
SCOPE_CATALOG VARCHAR(128)
Nullable
Reserved. Contains the null value.
SCOPE_SCHEMA VARCHAR(128)
Nullable
Reserved. Contains the null value.
SCOPE_TABLE VARCHAR(128)
Nullable
Reserved. Contains the null value.
SOURCE_DATA_TYPE VARCHAR(128)
Nullable
The source data type if the data type of the column is a distinct type.

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

DBNAME VARCHAR(8)
Nullable
Reserved. Contains the null value.
PSEUDO_COLUMN SMALLINT Indicates whether this is a ROWID or identity column.

1
The column is not a ROWID or identity column.

2
The column is a ROWID or identity column.
COLUMN_TEXT VARCHAR(50)
Nullable
The text of the column.

Contains the null value if the column has no column text.

SYSTEM_COLUMN_NAME CHAR(10) The system name of the column.
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 ]