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: Link type (U for URL) Link control (F for file, N for no) Integrity (A for all, N for none) Read permission (F for file system, D for database) Write permission (F for file system, B for blocked) Recovery (Y for yes, N for no) 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: 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. Starting with Version 2, representation of numeric data has been changed to character literals. The size has been enlarged from 16 to 33 bytes. 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.