SYSTAB system view

Each row of the SYSTAB system view describes one table or view in the database. Additional information for views can be found in the SYSVIEW system view. The underlying system table for this view is ISYSTAB.

Column name Data type Description
table_id UNSIGNED INT Each table is assigned a unique number (the table number).
dbspace_id SMALLINT A value indicating which dbspace contains the table.
count UNSIGNED BIGINT The number of rows in the table or materialized view. This value is updated during each successful checkpoint. This number is used to optimize database access. The count is always 0 for a non-materialized view or remote table.
creator UNSIGNED INT The user number of the owner of the table or view.
table_page_count INTEGER The total number of main pages used by the underlying table.
ext_page_count INTEGER The total number of extension pages used by the underlying table.
commit_action INTEGER For global temporary tables, 0 indicates that the ON COMMIT PRESERVE ROWS clause was specified when the table was created, 1 indicates that the ON COMMIT DELETE ROWS clause was specified when the table was created (the default behavior for temporary tables), and 3 indicates that the NOT TRANSACTIONAL clause was specified when the table was created. For non-temporary tables, commit_action is always 0.
share_type INTEGER For global temporary tables, 4 indicates that the SHARE BY ALL clause was specified when the table was created, and 5 indicates that the SHARE BY ALL clause was not specified when the table was created. For non-temporary tables, share_type is always 5 because the SHARE BY ALL clause cannot be specified when creating non-temporary tables.
object_id UNSIGNED BIGINT The object ID of the table.
last_modified_at TIMESTAMP The time at which the data in the table was last modified. This column is only updated at checkpoint time.
last_modified_tsn UNSIGNED BIGINT A sequence number assigned to the transaction that modified the table.
file_id SMALLINT DEPRECATED. This column is present in SYSVIEW, but not in the underlying system table ISYSTAB. The contents of this column is the same as dbspace_id and is provided for compatibility. Use dbspace_id instead.
table_name CHAR(128) The name of the table or view. One creator cannot have two tables or views with the same name.
table_type TINYINT

The type of table or view. Values include:

  • 1 – Base table
  • 2 – Materialized view
  • 3 – Global temporary table
  • 4 – Local temporary table
  • 5 – Text index base table
  • 6 – Text index global temporary table
  • 21 – View
replicate CHAR(1) This value is for internal use only.
server_type TINYINT

The location of the data for the underlying table. Values include:

  • 1 – Local server
  • 3 – Remote server
tab_page_list LONG VARBIT For internal use only. The set of pages that contain information for the table, expressed as a bitmap.
ext_page_list LONG VARBIT For internal use only. The set of pages that contain row extensions and large object (LOB) pages for the table, expressed as a bitmap.
pct_free UNSIGNED INT The PCT_FREE specification for the table, if one has been specified; otherwise, NULL.
clustered_index_id UNSIGNED INT The ID of the clustered index for the table. If none of the indexes are clustered, then this field is NULL.
encrypted CHAR(1) Whether the table or materialized view is encrypted.
table_type_str CHAR(9)

Readable value for table_type. Values include:

  • BASE – Base table
  • MAT VIEW – Materialized view
  • GBL TEMP – Global temporary table
  • VIEW – View

Constraints on underlying system table

FOREIGN KEY (dbspace_id) references SYS.ISYSDBSPACE (dbspace_id)
FOREIGN KEY (object_id) references SYS.ISYSOBJECT (object_id)
PRIMARY KEY (table_id)
FOREIGN KEY (creator) references SYS.ISYSUSER (user_id)
UNIQUE Index (table_name, creator)
Parent topic: System Views