SYSCOLUMNS catalog table

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

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
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
G
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.

Related information:
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
Not used 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 came from the basic machine-readable material (MRM) tape. 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.

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.
G
DEFAULT (continued)
CHAR(1)
NOT NULL
Default indicator:
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

Start of changeIf 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 is an updatable column.End of change

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. Remember that 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. Remember that 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.

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.

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 *.
Start of changeREnd of change
Start of change Partially hidden. The column is a ROWID column that was explicitly defined with the IMPLICITLY HIDDEN attribute.End of change
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
Start of changeColumn is the end of period BUSINESS_TIME with an exclusive end point.End of change
Start of changeIEnd of change
Start of changeColumn is the end of period BUSINESS_TIME with an inclusive end point.End of change
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
Start of changeENCODING_SCHEMEEnd of change Start of change
CHAR(1)
NOT NULL
WITH DEFAULT 'E'
End of change
Start of changeEncoding scheme of the column:
blank
Start of changeThe column has a data type that does not have an encoding scheme, or the column was created prior to Db2 12.End of change
A
ASCII
E
EBCDIC
U
Unicode
End of change
Start of changeGEnd of change