SYSCOLUMNS

The syscolumns system catalog table describes each column in the database.

One row exists for each column that is defined in a table or view.

Table 1. The SYSCOLUMNS table
Column Type Explanation
colname VARCHAR(128) Column name
tabid INTEGER Identifying code of table containing the column
colno SMALLINT Column number

The system sequentially assigns this (from left to right within each table).

coltype SMALLINT Code indicating the data type of the column:
  • 0 = CHAR
  • 1 = SMALLINT
  • 2 = INTEGER
  • 3 = FLOAT
  • 4 = SMALLFLOAT
  • 5 = DECIMAL
  • 6 = SERIAL 1
  • 7 = DATE
  • 8 = MONEY
  • 9 = NULL
  • 10 = DATETIME
  • 11 = BYTE
  • 12 = TEXT
  • 13 = VARCHAR
  • 14 = INTERVAL
  • 15 = NCHAR
  • 16 = NVARCHAR
  • 17 = INT8
  • 18 = SERIAL8 1
  • 19 = SET
  • 20 = MULTISET
  • 21 = LIST
  • 22 = ROW (unnamed)
  • 23 = COLLECTION
  • 40 = LVARCHAR fixed-length opaque types 2
  • 41 = BLOB, BOOLEAN, CLOB variable-length opaque types 2
  • 43 = LVARCHAR (client-side only)
  • 45 = BOOLEAN
  • 52 = BIGINT
  • 53 = BIGSERIAL 1
  • 2061 = IDSSECURITYLABEL 2, 3
  • 4118 = ROW (named)
collength Any of the following data types:
  • Integer-based
  • Varying-length character
  • Time
  • Fixed-point
  • Simple-large-object
  • IDSSECURITYLABEL
The value depends on the data type of the column. For some data types, the value is the column length (in bytes). See Storing Column Length for more information.
colmin INTEGER Minimum column length (in bytes)
colmax INTEGER Maximum column length (in bytes)
extended_id INTEGER Data type code, from the sysxtdtypes table, of the data type specified in the coltype column
seclabelid INTEGER The label ID of the security label associated with the column if it is a protected column. NULL otherwise.
colattr SMALLINT
HIDDEN
1 - Hidden column
ROWVER
2 - Row version column
ROW_CHKSUM
4 - Row key column
ER_CHECKVER
8 - ER row version column
UPGRD1_COL
16 - ER auto primary key column
UPGRD2_COL
32 - ER auto primary key column
UPGRD3_COL
64 - ER auto primary key column
PK_NOTNULL
128 - NOT NULL by PRIMARY KEY
Note:
  • 1 In DB-Access, an offset value of 256 is always added to these coltype codes because DB-Access sets SERIAL, SERIAL8, and BIGSERIAL columns to NOT NULL.
  • 2 The built-in opaque data types do not have a unique coltype value. They are distinguished by the extended_id column in the SYSXTDTYPES system catalog table.
  • 3 DISTINCT OF VARCHAR(128).

A composite index on tabid and colno allows only unique values.

The coltype codes can be incremented by bitmaps showing the following features of the column.

Bit Value Significance When Bit Is Set
0x0100 NULL values are not allowed
0x0200 Value is from a host variable
0x0400 Float-to-decimal for networked database server
0x0800 DISTINCT data type
0x1000 Named ROW type
0x2000 DISTINCT type from LVARCHAR base type
0x4000 DISTINCT type from BOOLEAN base type
0x8000 Collection is processed on client system

For example, the coltype value 4118 for named row types is the decimal representation of the hexadecimal value 0x1016, which is the same as the hexadecimal coltype value for an unnamed row type (0x016), with the named-row-type bit set. The file $ONEDB_HOME/incl/esql/sqltypes.h contains additional information about syscolumns.coltype codes.

The following table lists the coltype values for the built-in opaque data types:

NOT NULL constraints

Similarly, the coltype value is incremented by 256 if the column does not allow NULL values. To determine the data type for such columns, subtract 256 from the value and evaluate the remainder, based on the possible coltype values. For example, if the coltype value is 262, subtracting 256 leaves a remainder of 6, indicating that the column has a SERIAL data type.

Storing the column data type

The database server stores the coltype value as bitmap, as listed in SYSCOLUMNS.