IBM Books

Administration Guide


SYSCAT.COLUMNS

Contains one row for each column that is defined for a table or view. All of the catalog views have entries in the SYSCAT.COLUMNS table.

Table 65. SYSCAT.COLUMNS Catalog View
Column Name Data Type Nullable Description
TABSCHEMA CHAR(8)
Qualified name of the table or view that contains the column.
TABNAME VARCHAR(18)
COLNAME VARCHAR(18)
Column name.
COLNO SMALLINT
Numerical place of column in table or view, beginning at zero.
TYPESCHEMA CHAR(8)
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 INTEGER
Number of distinct values in the column; -1 if statistics are not gathered.
HIGH2KEY VARCHAR(33)
Second highest value of the column. This field is empty if statistics are not gathered. See Note 2.
LOW2KEY VARCHAR(33)
Second lowest value of the column. Empty if statistics not gathered. See Note 2.
AVGCOLLEN INTEGER
Average column length. -1 if a long field or LOB, or statistics have not been collected.
KEYSEQ SMALLINT Yes The column's numerical position within the table's primary key. This field is null or 0 if the column is not part of the primary key.
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.
NQUANTILES SMALLINT
Number of quantile values recorded in SYSCAT.SYSCOLDIST for this column; -1 if no statistics.
NMOSTFREQ SMALLINT
Number of most-frequent values recorded in SYSCAT.COLDIST for this column; -1 if statistics not gathered.
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 | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]