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 VARGRAPHIC(2000) CCSID 1200
Nullable
 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
XML
XML
USER-DEFINED
Distinct type
CHARACTER_MAXIMUM_LENGTH INTEGER
Nullable
 Maximum 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.
CHARACTER_OCTET_LENGTH INTEGER
Nullable
 Number of bytes for binary, character, and graphic string and XML data types.

Contains the null value if the column is not a string.
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
Start of change0-12End of change
Start of changeFor TIMESTAMP data types (number of fractional seconds).End of change

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'.
Start of changeIS_IDENTITYEnd of change Start of changeVARCHAR(3)End of change Start of changeThis column identifies whether the column is an identity column.
NO
The column is not an identity column.
YES
The column is an identity column.
End of change
Start of changeIDENTITY_GENERATIONEnd of change Start of changeVARCHAR(10)
Nullable
End of change		 Start of changeThis column identifies whether the column is GENERATED ALWAYS or GENERATED BY DEFAULT.
ALWAYS
The column value is always generated.
BY DEFAULT
The column value is generated by default.

Contains the null value if the column is not a ROWID, IDENTITY, or row change timestamp column.

End of change
Start of changeIDENTITY_STARTEnd of change Start of changeDECIMAL(31,0)
Nullable
End of change		 Start of changeStarting value of the identity column.

Contains the null value if the column is not an IDENTITY column.

End of change
Start of changeIDENTITY_INCREMENTEnd of change Start of changeDECIMAL(31,0)
Nullable
End of change		 Start of changeIncrement value of the identity column.

Contains the null value if the column is not an IDENTITY column.

End of change
Start of changeIDENTITY_MAXIMUMEnd of change Start of changeDECIMAL(31,0)
Nullable
End of change		 Start of changeMaximum value of the identity column.

Contains the null value if the column is not an IDENTITY column.

End of change
Start of changeIDENTITY_MINIMUMEnd of change Start of changeDECIMAL(31,0)
Nullable
End of change		 Start of changeMinimum value of the identity column.

Contains the null value if the column is not an IDENTITY column.

End of change
Start of changeIDENTITY_CYCLEEnd of change Start of changeVARCHAR(3)
Nullable
End of change		 Start of changeThis column identifies whether the identity column values will continue to be generated after the minimum or maximum value has been reached.
NO
Values will not continue to be generated.
YES
Values will continue to be generated.

Contains the null value if the column is not an IDENTITY column.

End of change
Start of changeIS_GENERATEDEnd of change Start of changeVARCHAR(5)End of change Start of changeReserved. Contains 'NEVER'.End of change
Start of changeGENERATION_EXPRESSIONEnd of change Start of changeVARCHAR(128)
Nullable
End of change		 Start of changeReserved. Contains the null value.End of change
Start of changeIS_SYSTEM_TIME_PERIOD_STARTEnd of change Start of changeVARCHAR(3)End of change Start of changeReserved. Contains 'NO'.End of change
Start of changeIS_SYSTEM_TIME_PERIOD_ENDEnd of change Start of changeVARCHAR(3)End of change Start of changeReserved. Contains 'NO'.End of change
Start of changeSYSTEM_TIME_PERIOD_TIMESTAMP_GENERATIONEnd of change Start of changeVARCHAR(6)
Nullable
End of change		 Start of changeReserved. Contains the null value.End of change
Start of changeIS_UPDATABLEEnd of change Start of changeVARCHAR(3)End of change Start of changeThis column identifies whether the column is updatable.
NO
The column cannot be updated.
YES
The column can be updated.
End of change
Start of changeDECLARED_DATA_TYPEEnd of change Start of changeVARCHAR(128)
Nullable
End of change		 Start of changeReserved. Contains the null value.End of change
Start of changeDECLARED_NUMERIC_PRECISIONEnd of change Start of changeINTEGER
Nullable
End of change		 Start of changeReserved. Contains the null value.End of change
Start of changeDECLARED_NUMERIC_SCALEEnd of change Start of changeINTEGER
Nullable
End of change		 Start of changeReserved. Contains the null value.End of change