DB2 10.5 for Linux, UNIX, and Windows

SYSCAT.COLUMNS catalog view

Each row represents a column defined for a table, view, or nickname.

Table 1. SYSCAT.COLUMNS Catalog View
Column Name Data Type Nullable Description
TABSCHEMA VARCHAR (128)   Schema name of the table, view, or nickname that contains the column.
TABNAME VARCHAR (128)   Unqualified name of the table, view, or nickname that contains the column.
COLNAME VARCHAR (128)   Name of the column.
COLNO SMALLINT   Number of this column in the table (starting with 0).
TYPESCHEMA VARCHAR (128)   Schema name of the data type for the column.
TYPENAME VARCHAR (128)   Unqualified name of the data type for the column.
LENGTH INTEGER   Maximum length of the data; 0 for distinct types. The LENGTH column indicates precision for DECIMAL fields, and indicates the number of bytes of storage required for decimal floating-point columns; that is, 8 and 16 for DECFLOAT(16) and DECFLOAT(34), respectively.
SCALE SMALLINT   Scale if the column type is DECIMAL or number of digits of fractional seconds if the column type is TIMESTAMP; 0 otherwise.
TYPESTRINGUNITS VARCHAR (11) Y In a Unicode database, the string units that apply to a character string or graphic string data type. Otherwise, the null value.
STRINGUNITSLENGTH INTEGER Y In a Unicode database, the declared number of string units for a character string or graphic string data type. Otherwise, the null value.
DEFAULT CLOB (64K) Y 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. Can also be the keyword NULL. Values might be converted from what was specified as a default value. For example, date and time constants are shown in ISO format, cast-function names are qualified with schema names, and identifiers are delimited. Null value if a DEFAULT clause was not specified or the column is a view column.
NULLS CHAR (1)   Nullability attribute for the column.
  • N = Column is not nullable
  • Y = Column is nullable
The value can be 'N' for a view column that is derived from an expression or function. Nevertheless, such a column allows null values when the statement using the view is processed with warnings for arithmetic errors.
CODEPAGE SMALLINT   Code page used for data in this column; 0 if the column is defined as FOR BIT DATA or is not a string type.
COLLATIONSCHEMA VARCHAR (128) Y For string types, the schema name of the collation for the column; the null value otherwise.
COLLATIONNAME VARCHAR (128) Y For string types, the unqualified name of the collation for the column; the null value otherwise.
LOGGED CHAR (1)   Applies only to columns whose type is LOB or distinct based on LOB; blank otherwise.
  • N = Column is not logged
  • Y = Column is logged
COMPACT CHAR (1)   Applies only to columns whose type is LOB or distinct based on LOB; blank otherwise.
  • N = Column is not compacted
  • Y = Column is compacted in storage
COLCARD BIGINT   Number of distinct values in the column; -1 if statistics are not collected; -2 for inherited columns and columns of hierarchy tables.
HIGH2KEY1 VARCHAR (254) Y Second-highest data value. Representation of numeric data changed to character literals. Empty if statistics are not collected. Empty for inherited columns and columns of hierarchy tables.
LOW2KEY1 VARCHAR (254) Y Second-lowest data value. Representation of numeric data changed to character literals. Empty if statistics are not collected. Empty for inherited columns and columns of hierarchy tables.
AVGCOLLEN INTEGER   Average space in bytes when the column is stored in database memory or a temporary table. For LOB data types that are not inlined, LONG data types, and XML documents, the value used to calculate the average column length is the length of the data descriptor. An extra byte is required if the column is nullable; -1 if statistics have not been collected; -2 for inherited columns and columns of hierarchy tables. Note: The average space required to store the column on disk may be different than the value represented by this statistic.
KEYSEQ SMALLINT Y The column's numerical position within the table's primary key. The null value for columns of subtables and hierarchy tables.
PARTKEYSEQ SMALLINT Y The column's numerical position within the table's distribution key; 0 or the null value if the column is not in the distribution key. The null value for columns of subtables and hierarchy tables.
NQUANTILES SMALLINT   Number of quantile values recorded in SYSCAT.COLDIST for this column; -1 if statistics are not gathered; -2 for inherited columns and columns of hierarchy tables.
NMOSTFREQ SMALLINT   Number of most-frequent values recorded in SYSCAT.COLDIST for this column; -1 if statistics are not gathered; -2 for inherited columns and columns of hierarchy tables.
NUMNULLS BIGINT   Number of null values in the column; -1 if statistics are not collected.
TARGET_TYPESCHEMA VARCHAR (128) Y Schema name of the target row type, if the type of this column is REFERENCE; null value otherwise.
TARGET_TYPENAME VARCHAR (128) Y Unqualified name of the target row type, if the type of this column is REFERENCE; null value otherwise.
SCOPE_TABSCHEMA VARCHAR (128) Y Schema name of the scope (target table), if the type of this column is REFERENCE; null value otherwise.
SCOPE_TABNAME VARCHAR (128) Y Unqualified name of the scope (target table), if the type of this column is REFERENCE; null value otherwise.
SOURCE_TABSCHEMA VARCHAR (128) Y For columns of typed tables or views, the schema name of the table or view in which the column was first introduced. For non-inherited columns, this is the same as TABSCHEMA. The null value for columns of non-typed tables and views.
SOURCE_TABNAME VARCHAR (128) Y For columns of typed tables or views, the unqualified name of the table or view in which the column was first introduced. For non-inherited columns, this is the same as TABNAME. The null value for columns of non-typed tables and views.
DL_FEATURES CHAR (10) Y This column is no longer used and will be removed in a future release.
SPECIAL_PROPS CHAR (8) Y Applies to REFERENCE type columns only; blanks otherwise. Each byte position is defined as follows:
  • 1 = Object identifier (OID) column ('Y' for yes; 'N' for no)
  • 2 = User-generated or system-generated ('U' for user; 'S' for system)
Bytes 3 through 8 are reserved for future use.
HIDDEN CHAR (1)   Type of hidden column.
  • I = Column is defined as IMPLICITLY HIDDEN
  • S = System-managed hidden column
  • Blank = Column is not hidden
INLINE_LENGTH INTEGER   Maximum size in bytes of the internal representation of an instance of an XML document, a structured type, or a LOB data type, that can be stored in the base table; 0 when not applicable.
PCTINLINED SMALLINT   Percentage of inlined data for columns with VARCHAR, VARGRAPHIC, LOB, or XML data types. -1 if statistics have not been collected or the column data type does not support storing data outside the row. Also -1 for VARCHAR and VARGRAPHIC column if the table is organized by column or the table is organized by row and the row size of the table does not exceed the maximum record length for the page size of the table space.
IDENTITY CHAR (1)  
  • N = Not an identity column
  • Y = Identity column
ROWCHANGETIMESTAMP CHAR (1)  
  • N = Not a row change timestamp column
  • Y = Row change timestamp column
GENERATED CHAR (1)   Type of generated column.
  • A = Column value is always generated
  • D = Column value is generated by default
  • Blank = Column is not generated
TEXT CLOB (2M) Y For columns defined as generated as expression, this field contains the text of the generated column expression, starting with the keyword AS.
COMPRESS CHAR (1)  
  • O = Compress off
  • S = Compress system default values
AVGDISTINCTPERPAGE DOUBLE Y For future use.
PAGEVARIANCERATIO DOUBLE Y For future use.
SUB_COUNT SMALLINT   Average number of sub-elements in the column. Applicable to character string columns only.
SUB_DELIM_LENGTH SMALLINT   Average length of the delimiters that separate each sub-element in the column. Applicable to character string columns only.
AVGCOLLENCHAR INTEGER   Average number of characters (based on the collation in effect for the column) required for the column; -1 if the data type of the column is long, LOB, or XML or if statistics have not been collected; -2 for inherited columns and columns of hierarchy tables.
IMPLICITVALUE2 VARCHAR (254) Y For a column that was added to a table after the table was created, stores the default value at the time the column was added. For a column that was defined when the table was created, stores the null value.
SECLABELNAME VARCHAR (128) Y Name of the security label that is associated with the column if it is a protected column; the null value otherwise.
ROWBEGIN CHAR (1)  
  • N = Not a row begin column
  • Y = Row begin column
ROWEND CHAR (1)  
  • N = Not a row end column
  • Y = Row end column
TRANSACTIONSTARTID CHAR (1)  
  • N = Not a transaction start ID column
  • Transaction start ID column
QUALIFIER VARCHAR (128) Y Reserved for future use.
FUNC_PATH CLOB (2K) Y Reserved for future use.
PCTENCODED SMALLINT   Percentage of values that are encoded as a result of compression for a column in a column-organized table; -1 if the table is not organized by column or if statistics are not collected; -2 for inherited columns and columns of hierarchy tables.
REMARKS VARCHAR (254) Y User-provided comments, or the null value.
Note:
  1. In the catalog view, the values of HIGH2KEY and LOW2KEY are always shown in the database code page and can contain substitution characters. However, the statistics are gathered internally in the code page of the column's table, and will therefore use actual column values when applied during query optimization.
  2. Attaching a data partition is allowed unless IMPLICITVALUE for a specific column is a non-null value for both the source column and the target column, and the values do not match. In this case, you must drop the source table and then re-create it. A column can have a non-null value in the IMPLICITVALUE field if one of the following conditions is met:
    • The column is created as the result of an ALTER TABLE...ADD COLUMN statement
    • The IMPLICITVALUE field is propagated from a source table during attach
    • The IMPLICITVALUE field is inherited from a source table during detach
    • The IMPLICITVALUE field is set during database upgrade from Version 8 to Version 9, where it is determined to be an added column, or might be an added column. If the database is not certain whether the column is added or not, it is treated as added. An added column is a column that was created as the result of an ALTER TABLE...ADD COLUMN statement.
    To avoid these inconsistencies during non-migration scenarios, it is recommended that you always create the tables that you are going to attach with all the columns already defined. That is, never use the ALTER TABLE statement to add columns to a table before attaching it.
Related tasks:
Attaching data partitions
Related reference:
ADMINTEMPCOLUMNS administrative view and ADMIN_GET_TEMP_COLUMNS table function - Retrieve column information for temporary tables