COLUMNS

The COLUMNS view contains one row for every column.

The following table describes the columns in the view:

Table 1. COLUMNS view
Column Name Data Type Description
TABLE_CATALOG VARCHAR(128) Relational database name
TABLE_SCHEMA VARCHAR(128) Name of the SQL schema containing the table or view
TABLE_NAME VARCHAR(128) Name of the table or view that contains the column
COLUMN_NAME VARCHAR(128) Name of the column
ORDINAL_POSITION INTEGER Numeric place of the column in the table or view, ordered from left to right
COLUMN_DEFAULT Start of changeVARGRAPHIC(2000) CCSID 1200

Nullable

End of change
The default value of a column, if one exists. If the default value of the column cannot be represented without truncation, then the value of the column is the string 'TRUNCATED'. The default value is stored in character form. The following special values also exist:
CURRENT_DATE
The default value is the current date.
CURRENT_TIME
The default value is the current time.
CURRENT_TIMESTAMP
The default value is the current timestamp.
NULL
The default value is the null value and DEFAULT NULL was explicitly specified.
USER
The default value is the current job user.

Contains the null value if:

  • The column has no default value. For example, if the column has an IDENTITY attribute or is a row ID, or
  • A DEFAULT value was not explicitly specified.
IS_NULLABLE VARCHAR(3) Indicates whether the column can contain null values:
NO
The column cannot contain null values.
YES
The column can contain null values.
DATA_TYPE VARCHAR(128) Type of column:
BIGINT
Big number
INTEGER
Large number
SMALLINT
Small number
DECIMAL
Packed decimal
NUMERIC
Zoned decimal
DOUBLE PRECISION
Double-precision floating point
REAL
Single-precision floating point
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
NATIONAL CHARACTER
National character
NATIONAL CHARACTER VARYING
Varying-length national character
NATIONAL CHARACTER LARGE OBJECT
National character large object
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
USER-DEFINED
Distinct type
CHARACTER_MAXIMUM_LENGTH 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 column is not a string.

End of change
CHARACTER_OCTET_LENGTH INTEGER

Nullable

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

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

End of change
NUMERIC_PRECISION INTEGER

Nullable

The precision of all numeric columns.
Note: This column supplies the precision of all numeric data types, including single-and double-precision floating point and decimal 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 column is not numeric.

NUMERIC_PRECISION_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 column is not numeric.

NUMERIC_SCALE INTEGER

Nullable

Scale of numeric data.

Contains the null value if the column is not decimal, numeric, or binary.

DATETIME_PRECISION 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 column is not a date, time, or timestamp.

INTERVAL_TYPE VARCHAR(128)

Nullable

Reserved. Contains the null value.
INTERVAL_PRECISION INTEGER

Nullable

Reserved. Contains the null value.
CHARACTER_SET_CATALOG VARCHAR(128)

Nullable

Relational database name

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

CHARACTER_SET_SCHEMA VARCHAR(128)

Nullable

The schema name of the character set. Contains SYSIBM.

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

CHARACTER_SET_NAME VARCHAR(128)

Nullable

The character set name.

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

COLLATION_CATALOG VARCHAR(128)

Nullable

Relational database name

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

COLLATION_SCHEMA VARCHAR(128)

Nullable

The schema of the collation. Contains SYSIBM.

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

COLLATION_NAME VARCHAR(128)

Nullable

The collation name. Contains IBMBINARY.

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

DOMAIN_CATALOG VARCHAR(128)

Nullable

Reserved. Contains the null value.
DOMAIN_SCHEMA VARCHAR(128)

Nullable

Reserved. Contains the null value.
DOMAIN_NAME VARCHAR(128)

Nullable

Reserved. Contains the null value.
UDT_CATALOG VARCHAR(128)

Nullable

The relational database name if this is a distinct type.

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

UDT_SCHEMA VARCHAR(128)

Nullable

The name of the schema if this is a distinct type.

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

UDT_NAME VARCHAR(128)

Nullable

The name of the distinct type.

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

SCOPE_CATALOG VARCHAR(128)

Nullable

Reserved. Contains the null value.
SCOPE_SCHEMA VARCHAR(128)

Nullable

Reserved. Contains the null value.
SCOPE_NAME VARCHAR(128)

Nullable

Reserved. Contains the null value.
MAXIMUM_CARDINALITY INTEGER

Nullable

Reserved. Contains the null value.
DTD_IDENTIFIER VARCHAR(128)

Nullable

A unique internal identifier for the column.
IS_SELF_REFERENCING VARCHAR(3) Reserved. Contains 'NO'.