SQL Reference

SYSCAT.COLUMNS

Contains one row for each column (including inherited columns where applicable) that is defined for a table or view. All of the catalog views have entries in the SYSCAT.COLUMNS table.

Table 51. SYSCAT.COLUMNS Catalog View
Column Name Data Type Nullable Description
TABSCHEMA VARCHAR(128)
Qualified name of the table or view that contains the column.
TABNAME VARCHAR(128)
COLNAME VARCHAR(128)
Column name.
COLNO SMALLINT
Numerical place of column in table or view, beginning at zero.
TYPESCHEMA VARCHAR(128)
Contains the qualified name of the type, if the data type of the column is distinct. Otherwise TYPESCHEMA contains the value SYSIBM and TYPENAME contains the data type of the column (in long form, for example, CHARACTER). If FLOAT or FLOAT(n) with n greater than 24 is specified, TYPENAME is renamed to DOUBLE. If FLOAT(n) with n less than 25 is specified, TYPENAME is renamed to REAL. Also, NUMERIC is renamed to DECIMAL.
TYPENAME VARCHAR(18)
LENGTH INTEGER
Maximum length of data. 0 for distinct types. The LENGTH column indicates precision for DECIMAL fields.
SCALE SMALLINT
Scale for DECIMAL fields; 0 if not DECIMAL.
DEFAULT VARCHAR(254) Yes Default value for the column of a table expressed as a constant, special register, or cast-function appropriate for the data type of the column. May also be the keyword NULL.

Values may be converted from what was specified as a default value. For example, date and time constants are presented in ISO format and cast-function names are qualified with schema name and the identifiers are delimited (see Note 3).

Null value if a DEFAULT clause was not specified or the column is a view column.

NULLS CHAR(1)

Y = Column is nullable.

N = Column is not nullable.

The value can be N for a view column that is derived from an expression or function. Nevertheless, such a column allows nulls when the statement using the view is processed with warnings for arithmetic errors.

See Note 1.

CODEPAGE SMALLINT
Code page of the column. For character-string columns not defined with the FOR BIT DATA attribute, the value is the database code page. For graphic-string columns, the value is the DBCS code page implied by the (composite) database code page. Otherwise, the value is 0.
LOGGED CHAR(1)
Applies only to columns whose type is LOB or distinct based on LOB (blank otherwise).

Y=Column is logged.

N=Column is not logged.

COMPACT CHAR(1)
Applies only to columns whose type is LOB or distinct based on LOB (blank otherwise).

Y = Column is compacted in storage.

N = Column is not compacted.

COLCARD BIGINT
Number of distinct values in the column; -1 if statistics are not gathered; -2 for inherited columns and columns of H-tables.
HIGH2KEY VARCHAR(254) Yes Second highest value of the column. This field is empty if statistics are not gathered and for inherited columns and columns of H-tables. See Note 2.
LOW2KEY VARCHAR(254) Yes Second lowest value of the column. This field is empty if statistics are not gathered and for inherited columns and columns of H-tables. See Note 2.
AVGCOLLEN INTEGER
Average column length. -1 if a long field or LOB, or statistics have not been collected; -2 for inherited columns and columns of H-tables.
KEYSEQ SMALLINT Yes The column's numerical position within the table's primary key. This field is null for subtables and hierarchy tables.
PARTKEYSEQ SMALLINT Yes The column's numerical position within the table's partitioning key. This field is null or 0 if the column is not part of the partitioning key. This field is also null for subtables and hierarchy tables.
NQUANTILES SMALLINT
Number of quantile values recorded in SYSCAT.COLDIST for this column; -1 if no statistics; -2 for inherited columns and columns of H-tables.
NMOSTFREQ SMALLINT
Number of most-frequent values recorded in SYSCAT.COLDIST for this column; -1 if no statistics; -2 for inherited columns and columns of H-tables.
NUMNULLS BIGINT
Contains the number of nulls in a column. -1 if statistics are not gathered.
TARGET_TYPESCHEMA VARCHAR(128) Yes Qualified name of the target type, if the type of the column is REFERENCE. Null value if the type of the column is not REFERENCE.
TARGET_TYPENAME VARCHAR(18) Yes
SCOPE_TABSCHEMA VARCHAR(128) Yes Qualified name of the scope (target table), if the type of the column is REFERENCE. Null value if the type of the column is not REFERENCE or the scope is not defined.
SCOPE_TABNAME VARCHAR(128) Yes
SOURCE_TABSCHEMA VARCHAR(128)
Qualified name of the table or view in the respective hierarchy where the column was introduced. For non-inherited columns, the values are the same as TBCREATOR and TBNAME. Null for columns of non-typed tables and views
SOURCE_TABNAME VARCHAR(128)
DL_FEATURES CHAR(10) Yes Applies to DATALINK type columns only. Null otherwise. Each character position is defined as follows:
  1. Link type (U for URL)
  2. Link control (F for file, N for no)
  3. Integrity (A for all, N for none)
  4. Read permission (F for file system, D for database)
  5. Write permission (F for file system, B for blocked)
  6. Recovery (Y for yes, N for no)
  7. On unlink (R for restore, D for delete, N for not applicable)
Characters 8 through 10 are reserved for future use.
SPECIAL_PROPS CHAR(8) Yes Applies to REFERENCE type columns only. Null otherwise. Each character position is defined as follows:

Object identifier (OID) column (Y for yes, N for no)

User generated or system generated (U for user, S for system)

HIDDEN CHAR(1)
Type of hidden column

S = System managed hidden column

Blank if column is not hidden

INLINE_LENGTH INTEGER
Length of structured type column that can be kept with base table row. 0 if no value explicitly set by ALTER/CREATE TABLE statement.
IDENTITY CHAR(1)
'Y' indicates that the column is an identity column; 'N' indicates that the column is not an identity column.
GENERATED CHAR(1)
Type of generated column

A = Column value is always generated

D = Column value is generated by default

Blank if column is not generated

TEXT CLOB(64K)
Contains the text of the generated column, starting with the keyword AS.
REMARKS VARCHAR(254) Yes User-supplied comment.
Note:
  1. Starting with Version 2, value D (indicating not null with a default) is no longer used. Instead, use of WITH DEFAULT is indicated by a non-null value in the DEFAULT column.
  2. Starting with Version 2, representation of numeric data has been changed to character literals. The size has been enlarged from 16 to 33 bytes.
  3. For Version 2.1.0, cast-function names were not delimited and may still appear this way in the DEFAULT column. Also, some view columns included default values which will still appear in the DEFAULT column.


[ Top of Page | Previous Page | Next Page ]