SYSCOLUMNS catalog table

The SYSCOLUMNS table contains one row for every column of each table and view. The schema is SYSIBM.

Table 1. SYSIBM.SYSCOLUMNS table column descriptions
Column name	Data type	Description	Use
NAME	VARCHAR(128) NOT NULL	Name of the column.	G
TBNAME	VARCHAR(128) NOT NULL	Name of the table or view which contains the column.	G
TBCREATOR	VARCHAR(128) NOT NULL	The schema of the table or view that contains the column.	G
COLNO	SMALLINT NOT NULL	Numeric place of the column in the table or view; for example 4 (out of 10).	G
COLTYPE	CHAR(8) NOT NULL	The type of the column specified in the definition of the column: INTEGER Large integer SMALLINT Small integer FLOAT Floating-point CHAR Fixed-length character string VARCHAR Varying-length character string LONGVAR Varying-length character string (for columns that were added before Version 9) DECIMAL Decimal GRAPHIC Fixed-length graphic string VARG Varying-length graphic string LONGVARG Varying-length graphic string (for columns that were added before Version 9) DATE Date TIME Time TIMESTMP Timestamp TIMESTZ Timestamp with time zone BLOB Binary large object CLOB Character large object DBCLOB Double-byte character large object ROWID Row ID data type	G
COLTYPE (continued)		DISTINCT Distinct type XML XML data type BIGINT Big integer BINARY Fixed-length binary string VARBIN Varying-length binary string, or a Db2 11 VARCHAR or VARGRAPHIC Unicode column in an EBCDIC table DECFLOAT Decimal floating point
LENGTH	SMALLINT NOT NULL	Length attribute of the column or, in the case of a decimal column, its precision. The number does not include the internal prefixes that are used to record the actual length and null state, where applicable. INTEGER 4 SMALLINT 2 BIGINT 8 FLOAT 4 or 8 CHAR Length of string VARCHAR Maximum length of string LONGVAR Maximum length of string (for columns that were added before Version 9) DECIMAL Precision of number DECFLOAT 8 or 16 GRAPHIC Number of DBCS characters VARGRAPHIC Maximum number of DBCS characters. For a Db2 11 VARGRAPHIC Unicode column in an EBCDIC table, this value is the maximum number of bytes. LONGVARG Maximum number of DBCS characters (for columns that were added before Version 9) BINARY Length of string VARBINARY Maximum length of string DATE 4 TIME 3 TIMESTAMP WITHOUT TIME ZONE The integral part of ((`p`+1)/2) + 7 where `p` is the precision of the timestamp TIMESTAMP WITH TIME ZONE The integral part of ((`p`+1)/2) + 9 where `p` is the precision of the timestamp	G
LENGTH (continued)	SMALLINT NOT NULL	LOB 4 - For a table, a field of length of 4 is stored in the base table. The maximum length of the LOB column is found in LENGTH2. INLINE LOB Greater than 4 - For a table, a field of length 4 plus the inline length (in byte) is stored in the base table. The maximum length of the LOB column is found in LENGTH2. BLOB 4 - For a table, a field of length of 4 is stored in the base table. The maximum length of the LOB column is found in LENGTH2. CLOB 4 - For a table, a field of length of 4 is stored in the base table. The maximum length of the CLOB column is found in LENGTH2. DBCLOB 4 - For a table, a field of length of 4 is stored in the base table. The maximum length of the DBCLOB column is found in LENGTH2. ROWID 17 - The maximum length of the stored portion of the identifier. XML 6 - If column cannot contain multiple versions of an XML document. 14 - If column can contain multiple versions of an XML document. For more information, see How Db2 uses XML versions. DISTINCT The length of the source data type.	G
SCALE	SMALLINT NOT NULL	If the column type is DECIMAL, this value represents the scale. If the column type is timestamp or timestamp with time zone, this value represents the number of fractional second digits. Otherwise the value is 0. If the column is a timestamp type, the LENGTH is 10 and the SCALE is 0, the number of fractional second digits is 6.	G
NULLS	CHAR(1) NOT NULL	Whether the column can contain null values: N No Y Yes The value can be N for a view column that is derived from an expression that is not a simple column name or constant, or from a function. Nevertheless, such a column allows nulls when an outer select list refers to it.	G
COLCARD	INTEGER NOT NULL	Db2 utilities might populate this column value, but its use is not recommended in DB2 version 5 and later. For best results use its replacement, the COLCARDF column.	N
HIGH2KEY	VARCHAR(2000) NOT NULL FOR BIT DATA	Second highest value of the column. Blank if statistics have not been gathered, or the column is an indicator column or a column of an auxiliary table. If the column has a non-character data type, the data might not be printable. If the table is empty, the value is a string of length 0. This is an updatable column.	S
LOW2KEY	VARCHAR(2000) NOT NULL FOR BIT DATA	Second lowest value of the column. Blank if statistics have not been gathered, or the column is an indicator column or a column of an auxiliary table. If the column has a non-character data type, the data might not be printable. If the table is empty, the value is a string of length 0. This is an updatable column.	S
UPDATES	CHAR(1) NOT NULL	Whether the column can be updated: N No Y Yes The value is N if the column is: Derived from a function or expression A column with a row ID data type (or a distinct type based on a row ID type) A read-only view	G
IBMREQD	CHAR(1) NOT NULL	A value of Y indicates that the row was provided with the Db2 product code. For all other values, see Release dependency indicators. The value in this field is not a reliable indicator of release dependencies.	G
REMARKS	VARCHAR(762) NOT NULL	A character string provided by the user with the COMMENT statement.	G
DEFAULT	CHAR(1) NOT NULL	The contents of this column are meaningful only if the TYPE column for the associated SYSTABLES row indicates that this is for a table (T) or a created temporary table (G). Default indicator: A The column is defined as a ROWID with the GENERATED ALWAYS attribute. B The column is defined as a built-in data type with a default value that depends on the data type of the column. D The column is defined as a ROWID with the GENERATED BY DEFAULT attribute. E The column is defined with the FOR EACH ROW ON UPDATE and GENERATED ALWAYS attributes. F The column is defined with the FOR EACH ROW ON UPDATE and GENERATED BY DEFAULT attributes. I The column is defined with the AS IDENTITY and GENERATED ALWAYS attributes. J The column is defined with the AS IDENTITY and GENERATED BY DEFAULT attributes. K The column is defined for the implicit DOCID column for a base table that contains XML data. L The column is defined with the AS SECURITY LABEL attribute. N The column has no default value. Q The column is defined with the AS ROW BEGIN attribute. R The column is defined with the AS ROW END attribute.	G
DEFAULT (continued)	CHAR(1) NOT NULL	Default indicator: S The column has a default value that is the value of the SQL authorization ID of the process at the time a default value is used. U The column has a default value that is the value of the SESSION_USER special register at the time a default value is used. X The column is defined with the AS TRANSACTION START ID attribute. Y If the NULLS column is Y, the column has a default value of null. If the NULLS column is N, the default value depends on the data type of the column. Data type Default Value Numeric 0 Fixed-length character string Blanks Fixed-length graphic string Blanks Fixed-length binary string Hexadecimal blanks Varying-length string A string length of 0 Date The current date Time The current time Timestamp The current timestamp Timestamp with time zone The current timestamp with time zone 1 The column has a default value that is the string constant found in the DEFAULTVALUE column of this table row. The column has a graphic data type and has a default value that is the graphic string found in the DEFAULTVALUE column of this table row.	G
DEFAULT (continued)	CHAR(1) NOT NULL	Default indicator: 2 The column has a default value that is the floating-point constant found in the DEFAULTVALUE column of this table row. 3 The column has a default value that is the decimal constant found in the DEFAULTVALUE column of this table row. 4 The column has a default value that is the integer constant found in the DEFAULTVALUE column of this table row. 5 The column has a default value that is the hexadecimal character string found in the DEFAULTVALUE column of this table row. 6 The column has a default value that is the UX string found in the DEFAULTVALUE column of this table row. 7 The column has a graphic data type and has a default value that is the character string constant found in the DEFAULTVALUE column of this table row. 8 The column has a character data type and has a default value that is the graphic string constant found in the DEFAULTVALUE column of this table row. 9 The column has a default value that is the DECFLOAT constant found in the DEFAULTVALUE column of this table row. a The column is defined with an expression that specifies a special register. The default value is the value of the special register. b The column is defined with an expression that specifies a built-in session variable. The default value is the value of the built-in session variable. d The column is defined with the DATA CHANGE OPERATION clause. The default value is an I, U, or D character indicating which data change operation the row represents.	G
KEYSEQ	SMALLINT NOT NULL	The numeric position of the column within the primary key of the table. The value is 0 if it is not part of a primary key.	G
FOREIGNKEY	CHAR(1) NOT NULL	Applies to character or CLOB columns, where it indicates the subtype of the data: B BIT data M MIXED data S SBCS data blank Indicates one of the following subtypes: MIXED data if the encoding scheme is Unicode, or if the encoding scheme is not Unicode and the value of MIXED DATA on installation panel DSNTIPS is YES SBCS data if the encoding scheme is not Unicode and the value of MIXED DATA on the installation panel DSNTIPS is NO. For views defined prior to Version 7, subtype information is not available and the default (MIXED or SBCS) is used.	G
FLDPROC	CHAR(1) NOT NULL	Whether the column has a field procedure: N No Y Yes blank The column is for a view defined prior to Version 7. Views defined after Version 7 contain Y or N.	G
LABEL	VARCHAR(90) NOT NULL	The column label provided by the user with a LABEL statement; otherwise, the value is an empty string.	G
STATSTIME	TIMESTAMP NOT NULL WITH DEFAULT	If RUNSTATS or another utility with inline statistics updated the statistics, the date and time when the last utility invocation updated the statistics. The default value is '0001-01-01-00.00.00.000000'. The default value indicates that statistics were not collected. This column can be updated. The value '0001-01-02-00.00.00.000000' indicates that an ALTER TABLE statement was executed to change the length of a VARCHAR column and RUNSTATS should be run to update the statistics before they are used.	G
DEFAULTVALUE	VARCHAR(1536) NOT NULL WITH DEFAULT	This field is meaningful only if the column being described is for a table (the TYPE column of the associated SYSTABLES row is T for table or G for created temporary table). When the DEFAULT column is 1, 2, 3, 4, 5, 6, 7, 8, or 9, this field contains the default value of the column. If the default value is a string constant or a hexadecimal constant (DEFAULT is 1, 5, 6, 7, or 8 respectively), the value is stored without delimiters. If the default value is a numeric constant (DEFAULT is 2, 3, 4, or 9), the value is stored as specified by the user, including sign and decimal point representation, or special constant values, as appropriate for the constant. When the DEFAULT column is S or U and the default value was specified when a new column was defined with the ALTER TABLE statement, this field contains the value of the CURRENT SQLID or SESSION_USER special register at the time the ALTER TABLE statement was executed. This default value applies only to rows that existed before the ALTER TABLE statement was executed. When the DEFAULT column is L and the column was added as a new column with the ALTER TABLE statement, this field contains the security label of the user at the time the ALTER TABLE statement was executed. This default value applies only to rows that existed before the ALTER TABLE statement was executed. When the DEFAULT column contains a, this field contains the name of the special register. When the DEFAULT column contains b, this field contains the qualified name of the session variable. When the DEFAULT column contains d, this field contains DATA CHANGE OPERATION. FL 503 When the DEFAULT column contains E or F, and the column was added as a new row change timestamp column with the ALTER TABLE statement, this field contains the timestamp of when the ALTER TABLE statement was executed. This default value applies only to rows that existed before the ALTER TABLE statement was executed.	G
COLCARDF	FLOAT NOT NULL WITH DEFAULT	Estimated number of distinct values in the column. For an indicator column, this is the number of LOBs that are not null and have a length greater than zero. The value is -1 if statistics have not been gathered. The value is -2 if the column is a LOB column. This is an updatable column.	S
COLSTATUS	CHAR(1) NOT NULL WITH DEFAULT	Indicates the status of the definition of a column: I The definition is incomplete because a LOB table space, auxiliary table, or index on an auxiliary table has not been created for the column. blank The definition is complete.	G
LENGTH2	INTEGER NOT NULL WITH DEFAULT	Maximum length of the data retrieved from the column. Possible values are: 0 Column is not a LOB or ROWID column 40 For a ROWID column, the length of the returned value 1 to 2,147,483,647 bytes For a LOB column, the maximum length	G
DATATYPEID	INTEGER NOT NULL WITH DEFAULT	For a built-in data type, the internal ID of the built-in type. For a distinct type, the internal ID of the distinct type. The DATATYPEID value corresponds to one of the SQLTYPE values in SQLTYPE and SQLLEN. However, the DATATYPEID value is not a reliable indicator of the nullability of the column. A column with an even DATATYPEID value might allow nulls, and a column with an odd DATATYPEID value might not allow nulls. To determine the nullability of the column, use the NULLS column value. If the column was created prior to DB2 version 6, the value is 0.	S
SOURCETYPEID	INTEGER NOT NULL WITH DEFAULT	For a built-in data type, 0. For a distinct type, the internal ID of the built-in data type upon which the distinct type is based. If the column was created prior to DB2 version 6, the value is 0.	S
TYPESCHEMA	VARCHAR(128) NOT NULL WITH DEFAULT 'SYSIBM'	If COLTYPE is 'DISTINCT', the schema of the distinct type. Otherwise, the value is 'SYSIBM'.	G
TYPENAME	VARCHAR(128) NOT NULL WITH DEFAULT	If COLTYPE is 'DISTINCT', the name of the distinct type. Otherwise, the value is the same as the value of the COLTYPE column. TYPENAME is set only for columns created in DB2 version 6 or later. The value for columns created earlier is not filled in.	G
CREATEDTS	TIMESTAMP NOT NULL WITH DEFAULT	Timestamp when the column was created. The value is '0001-01-01.00.00.00.000000' if the column was created prior to migration to DB2 version 6 or if the column is in a catalog table.	G
STATS_FORMAT	CHAR(1) NOT NULL WITH DEFAULT	The type of statistics gathered: blank Statistics have not been collected or varchar column statistical values are padded. N Varchar column statistical values are not padded. This is an updatable column.	G
PARTKEY_COLSEQ	SMALLINT NOT NULL WITH DEFAULT	The numeric position of the column within the partitioning key of the table. The value is 0 if it is not part of the partitioning key. This column is applicable only if the table uses table-controlled partitioning.	G
PARTKEY_ORDERING	CHAR(1) NOT NULL WITH DEFAULT	Order of the column in the partitioning key: A Ascending D Descending blank Column is not used as part of a partitioning key This column is applicable only if the table uses table-controlled partitioning.	G
ALTEREDTS	TIMESTAMP NOT NULL WITH DEFAULT	Timestamp when alter occurred.	G
CCSID	INTEGER NOT NULL WITH DEFAULT	CCSID of the column. 0 if the object was created prior to Version 8, or is not a character of graphic string column. CCSID is not 0 if COLTYPE is VARBINARY because the object is a Db2 11 Unicode column in an EBCDIC table.	G
HIDDEN	CHAR(1) NOT NULL WITH DEFAULT 'N'	Indicates whether the column is implicitly hidden: P Partially hidden. The column is implicitly hidden from SELECT *. R Partially hidden. The column is a ROWID column that was explicitly defined with the IMPLICITLY HIDDEN attribute. N Not hidden. The column is visible to all SQL statements.	G
RELCREATED	CHAR(1) NOT NULL	The release of Db2 that is used to create the object. See Release dependency indicators for the values.	G
CONTROL_ID	INTEGER NOT NULL WITH DEFAULT	Internal identifier of the column access control mask defined for this column. 0 if no column access control mask is defined for the column.	S
XML_TYPEMOD_ID	INTEGER NOT NULL WITH DEFAULT	The ID of the XML type modifier. It is set to 0 if the column is not an XML column or has no XML type modifier.	G
PERIOD	CHAR(1) NOT NULL WITH DEFAULT	Indicates whether the column is the start or the end of the period for a SYSTEM_TIME or BUSINESS_TIME period: B Column is the start of period BUSINESS_TIME. C Column is the end of period BUSINESS_TIME with an exclusive end point. I Column is the end of period BUSINESS_TIME with an inclusive end point. S Column is the start of period SYSTEM_TIME. T Column is the end of period SYSTEM_TIME. blank Column is not used as either the start or the end of a period.	G
GENERATED_ ATTR	CHAR(1) NOT NULL WITH DEFAULT	Indicates the columns generated attribute: A Column is defined as GENERATED_ALWAYS. D Column is defined as GENERATED BY DEFAULT. blank Not applicable or the value of the DEFAULT column is A, D, E, F, I, or J or defined from a prior release of Db2.	G
HASHKEY_COLSEQ	SMALLINT NOT NULL WITH DEFAULT	The column's numeric position within the table's hash key. The value is 0 if the column is not part of the hash key. This column is applicable only if the table that use hash organization.	G
ENCODING_SCHEME	CHAR(1) NOT NULL WITH DEFAULT 'E'	Encoding scheme of the column: blank The column has a data type that does not have an encoding scheme, or the column was created prior to Db2 12. A ASCII E EBCDIC U Unicode	G