SYSTABLES catalog table

The SYSTABLES table contains one row for each table, view, or alias. The schema is SYSIBM. Each SYSTABLES table row indicates whether the object that it describes is a table, view, or alias, its name, who created it, the database that it belongs to, the table space it belongs to, and other information. The SYSTABLES table also has a REMARKS column in which you can store your own information about the table in question.

For an example query for the SYSTABLES catalog table, see Retrieving catalog information about a table.

Table 1. SYSIBM.SYSTABLES table column descriptions
Column name Data type Description Use
NAME
VARCHAR(128)
NOT NULL
 Name of the table, view, or alias. G
CREATOR
VARCHAR(128)
NOT NULL
 The schema of the table, view, or alias. G
TYPE
CHAR(1)
NOT NULL
 Type of object:
A
Alias
C
Clone table
D
Accelerator-only table
G
Created global temporary table
H
History table
M
Materialized query table
P
Table that was implicitly created for XML columns
R
Archive table
T
Table
V
View
X
Auxiliary table
 G
DBNAME
VARCHAR(24)
NOT NULL
 For a table, or a view of tables, the name of the database that contains the table space that is named in TSNAME. For a created temporary table, an alias, or a view of a view, the value is DSNDB06. G
TSNAME
VARCHAR(24)
NOT NULL
 For a table, or a view of one table, the name of the table space that contains the table. For a view of more than one table, the name of a table space that contains one of the tables. For a created temporary table, a view of a view, or an alias, it is SYSTSTAB. G
DBID
SMALLINT
NOT NULL
 Internal identifier of the database; 0 if the row describes a view, alias, or created temporary table. Non-zero if the view has an INSTEAD OF trigger defined. S
OBID
SMALLINT
NOT NULL
 Internal identifier of the table; 0 if the row describes a view, an alias, or a created temporary table. Non-zero if the view has an INSTEAD OF trigger defined. S
COLCOUNT
SMALLINT
NOT NULL
 Number of columns in the table or view. The value is 0 if the row describes an alias. G
EDPROC
VARCHAR(24)
NOT NULL
 Name of the edit procedure; blank if the row describes a view or alias or a table without an edit procedure. G
VALPROC
VARCHAR(24)
NOT NULL
 Name of the validation procedure; blank if the row describes a view or alias or a table without a validation procedure. G
CLUSTERTYPE
CHAR(1)
NOT NULL
 Whether RESTRICT ON DROP applies:
blank
No
Y
Yes. You cannot drop the table or any table space or database that contains the table.
 G
CLUSTERRID
INTEGER
NOT NULL
1
The table is a system-period temporal table with versioning, and the table is defined with the ON DELETE ADD EXTRA ROW clause.
0
All other tables.
 N
CARD
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 CARDF column. N
NPAGES
INTEGER
NOT NULL
 Total number of pages that include rows of the table. The value is -1 if statistics have not been gathered, or the row describes a view, an alias, a created temporary table, an accelerator-only table, or an auxiliary table. This column can be updated. S
PCTPAGES
SMALLINT
NOT NULL
 Percentage of active table space pages that contain rows of the table. A page is termed active if it is formatted for rows, regardless of whether it contains any. If the table space is segmented, the percentage is based on the number of active pages in the set of segments that are assigned to the table. The value is -1 if statistics have not been gathered, or the row describes a view, alias, created temporary table, an accelerator-only table, or auxiliary table. This column can be updated. S
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. RELCREATED should be used instead.

 G
REMARKS
VARCHAR(762)
NOT NULL
 A character string that is provided by the user with the COMMENT statement. G
PARENTS
SMALLINT
NOT NULL
 Number of relationships in which the table is a dependent. The value is 0 if the row describes a view, an alias, a created temporary table, or a materialized query table. G
CHILDREN
SMALLINT
NOT NULL
 Number of relationships in which the table is a parent. The value is 0 if the row describes a view, an alias, a created temporary table, or a materialized query table. G
KEYCOLUMNS
SMALLINT
NOT NULL
 Number of columns in the primary key of the table. The value is 0 if the row describes a view, an alias, or a created temporary table. G
RECLENGTH
SMALLINT
NOT NULL
 For user tables, the maximum length of any record in the table. Length is 8+N+L, where:
  • The number 8 accounts for the header (6 bytes) and the ID map entry (2 bytes).
  • N is 10 if the table has an edit procedure, or 0 otherwise.
  • L is the sum of the maximum column lengths. In determining the maximum length of a column, take into account whether the column allows nulls and the data type of the column. If the column can contain nulls and is not a LOB or ROWID column, add 1 byte for a null indicator. Use 4 bytes for the length of a LOB column and 19 bytes for the length of a ROWID column. If the column has a varying-length data type (for example, VARCHAR, CLOB, or BLOB), add 2 bytes for a length indicator. For more information about column lengths, see Data types in Db2 for z/OS.

The value is 0 if the row describes a view, alias, or auxiliary table. For maximum row and record sizes, see the note on the maximum record size in CREATE TABLE statement.

 G
STATUS
CHAR(1)
NOT NULL
 Indicates the status of the table definition:
I
The definition of the table is incomplete. The TABLESTATUS column indicates the reason why the table definition is incomplete.
R
An error occurred when an attempt was made to regenerate the internal representation of the view.
X
The table has a unique constraint (primary key or unique key) and the table definition is complete.
blank
The table has no unique constraint (primary key or unique key), the table is a catalog table, or the row describes a view or alias. The definition of the table, view, or alias is complete.
 G
KEYOBID
SMALLINT
NOT NULL
 Internal Db2 identifier of the index that enforces uniqueness of the primary key of the table; 0 if not applicable. S
LABEL
VARCHAR(90)
NOT NULL
 The label as given by a LABEL statement; otherwise, the value is an empty string. G
CHECKFLAG
CHAR(1)
NOT NULL
WITH DEFAULT
C
The table space that contains the table is in CHECK-pending status. One of the following conditions is true:
  • There are rows in the table that violate referential constraints, check constraints, or both
  • The table is a materialized query table that might contain inconsistent data
blank
Indicates one of the following conditions:
  • The table contains no rows that violate referential constraints, check constraints, or both
  • The table is a materialized query table that contains consistent data
  • The row describes a view, an alias, or a temporary table
 G
CHECKRID
CHAR(4)
NOT NULL
WITH DEFAULT
FOR BIT DATA
 A value of 'FFFFFF00' in this column indicates that the edit procedure on this table is defined without row attribute sensitivity. Any other value indicates that the edit procedure is defined with row attribute sensitivity. G
AUDITING
CHAR(1)
NOT NULL
WITH DEFAULT
 Value of the audit option:
A
AUDIT ALL
C
AUDIT CHANGE
blank
AUDIT NONE, or the row describes a view, an alias, or a created temporary table.
 G
CREATEDBY
VARCHAR(128)
NOT NULL
WITH DEFAULT
 Primary authorization ID of the user who created the table, view, or alias. G
LOCATION
VARCHAR(128)
NOT NULL
WITH DEFAULT
 Location name of the object of an alias. The value is blank for a table, a view, an alias that was not defined with a three-part object name, or a materialized query table. G
TBCREATOR
VARCHAR(128)
NOT NULL
WITH DEFAULT
  • For an alias, the schema of the referenced table or view
  • For a base table that is involved in a clone relationship, the name of the creator of the clone table
  • For a clone table that is involved in a clone relationship, the name of the creator of the base table
  • Otherwise, TBCREATOR is blank
 G
TBNAME
VARCHAR(128)
NOT NULL
WITH DEFAULT
  • For an alias, the name for the referenced table or view
  • For a base table that is involved in a clone relationship, the name of the clone table
  • For a clone table that is involved in a clone relationship, the name of the base table
  • Otherwise, TBNAME is blank
 G
CREATEDTS
TIMESTAMP
NOT NULL
WITH DEFAULT
 Time when the CREATE statement was executed for the table, view, or alias G
ALTEREDTS
TIMESTAMP
NOT NULL
WITH DEFAULT
 For a table, the time when the latest ALTER TABLE statement was applied. If no ALTER TABLE statement was applied, or if the row is for an alias, ALTEREDTS has the value of CREATEDTS. For a view, the time when the last ALTER VIEW REGENERATE statement was applied. G
DATACAPTURE
CHAR(1)
NOT NULL
WITH DEFAULT
 Records the value of the DATA CAPTURE option for a table:
blank
No
Y
Yes

For a created temporary table, DATACAPTURE is always blank.

 G
RBA1
CHAR(10)
NOT NULL
WITH DEFAULT
FOR BIT DATA
 The log RBA when the table was created. Otherwise, RBA1 is X'00000000000000000000', indicating that the log RBA is not known, or that the object is a view, an alias, or a created temporary table. In a data sharing environment, RBA1 is the LRSN (Log Record Sequence Number) value. S
RBA2
CHAR(10)
NOT NULL
WITH DEFAULT
FOR BIT DATA
 The log RBA when the table was last altered. Otherwise, RBA2 is X'00000000000000000000' indicating that the log RBA is not known, or that the object is a view, an alias, or a created temporary table. RBA1 equals RBA2 if the table has not been altered. In a data sharing environment, RBA2 is the LRSN (Log Record Sequence Number) value. S
PCTROWCOMP
SMALLINT
NOT NULL
WITH DEFAULT
 Percentage of rows that are compressed within the total number of active rows in the table. This number includes any row in a table space that is defined with compression. The value is -1 if statistics have not been gathered, or the row describes a view, alias, created temporary table, an accelerator-only table, or auxiliary table. This column can be updated. S
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.

For a created temporary table, the value of STATSTIME is always the default value.		 G
CHECKS
SMALLINT
NOT NULL
WITH DEFAULT
 Number of check constraints that are defined on the table. The value is 0 if either of the following conditions are true:
  • The row describes a view, an alias, a created temporary table, or a materialized query table.
  • No constraints are defined on the table.
 G
CARDF
FLOAT
NOT NULL
WITH DEFAULT -1
 Total number of rows in the table or total number of LOBs in an auxiliary table. The value is -1 if statistics have not been gathered or the row describes a view, alias, accelerator-only table, or created temporary table. This column can be updated. S
CHECKRID5B
CHAR(5)
NOT NULL
WITH DEFAULT
FOR BIT DATA
 RID of the first row of the table space partition that can violate referential constraints, check constraints, or both. The value of X'0000000000' indicates that any row can violate referential constraints.

The value is blank if any of the following conditions are true:

  • The table or partition is not in CHECK-pending status (CHECKFLAG is blank)
  • The table space is not partitioned
  • The table is a created temporary table
 S
ENCODING_SCHEME
CHAR(1)
NOT NULL
WITH DEFAULT 'E'
 Encoding scheme for a table, view, or the table or view that is referred to by a local alias:
E
EBCDIC. This value is 'E' even if the table contains a Unicode column.
A
ASCII
M
Multiple CCSID set or multiple encoding schemes
U
Unicode
blank
For remote aliases

The value is 'E' for tables in non-work file databases. The value is blank for tables in work file databases that were created before Version 5 or in the default database, DSNDB04.

This column is not applicable for objects that were created before Db2 for z/OS Version 5.

This field contains the default encoding scheme for table columns for which a CCSID is not explicitly defined. The ENCODING_SCHEME column in SYSIBM.SYSCOLUMNS table contains the encoding schemes for columns for which a CCSID is explicitly defined.

 G
TABLESTATUS
VARCHAR(30)
NOT NULL
WITH DEFAULT
 Indicates the reason for an incomplete table definition:
F
The table lacks a required BUSINESS_TIME WITHOUT OVERLAPS index on a foreign key.
L
An auxiliary table or auxiliary index has not been defined for a LOB column.
P
The table lacks a primary index.
R
The table lacks a required index on a row ID.
U
The table lacks a required index on a unique key.
V
An error occurred when an attempt was made to regenerate the internal representation of the view.
blank
Definition is complete.
 G
NPAGESF
FLOAT(8)
NOT NULL
WITH DEFAULT -1
 Number of pages that are used by the table. The value is -1 if statistics have not been gathered or the table is an auxiliary table or accelerator-only table. This column can be updated. G
SPACEF
FLOAT(8)
NOT NULL
WITH DEFAULT -1
 Kilobytes of DASD storage. The value is -1 if statistics have not been gathered. The value might be non-zero for an auxiliary table in the LOB table space. This column can be updated. G
AVGROWLEN
INTEGER
NOT NULL
WITH DEFAULT -1
 Average length of rows for the tables in the table space. If the table space is compressed, the value is the compressed row length. If the table space is not compressed, the value is the uncompressed row length. The value is -1 if statistics have not been gathered. 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
NUM_DEP_MQTS
SMALLINT
NOT NULL
WITH DEFAULT
 Number of dependent materialized query tables. The value is zero if the row describes an alias or a created temporary table, or if no materialized query tables are defined on the table. G
VERSION
SMALLINT
NOT NULL
WITH DEFAULT
 The version of the data row format for this table.
  • A value of zero indicates that an alter operation that creates a new version has never occurred for this table.
  • A value of -1 indicates that the view has been regenerated because a column of the base table has been altered.
  • A value of 800 indicates that a successful CREATE VIEW or ALTER VIEW statement has occurred against this view in Version 8 or later.
  • A value of 900 indicates that a successful ALTER TABLE statement with a DROP COLUMN clause has occurred against this view.
 G
PARTKEYCOLNUM
SMALLINT
NOT NULL
WITH DEFAULT
 The number of columns in the partitioning key. This value is zero for tables that do not have partitioning or use index-controlled partitioning. The value is non-zero for tables that use table-controlled partitioning. G
SPLIT_ROWS
CHAR(1)
NOT NULL
WITH DEFAULT
 This column is blank except for volatile tables. For volatile table, this column contains 'Y' to indicate to Db2 to use index access on this table whenever possible. G
SECURITY_LABEL
CHAR(1)
NOT NULL
 This column is only meaningful if the TYPE column is a T (for table) or M (for materialized query table). The value indicates whether the table has multilevel security:
Blank
The table does not have multilevel security.
R
The table has multilevel security with row granularity.
 G
OWNER
VARCHAR(128)
NOT NULL
WITH DEFAULT
 Authorization ID of the owner of the table, view, or alias. This column is blank for tables, views, or aliases that were created before Db2 for z/OSDB2 9. G
APPEND
CHAR(1)
NOT NULL
WITH DEFAULT
 Indicates whether the APPEND option is specified for the table.
Y
The APPEND option is specified.
N
The APPEND option is not specified.
 G
OWNERTYPE
CHAR(1)
NOT NULL
WITH DEFAULT
 Indicates the type of owner:
blank
Authorization ID
L
Role
 G
CONTROL
CHAR(1)
NOT NULL
WITH DEFAULT
 Indicates whether access to the table is enforced by using row or column access control:
blank
No access control enforcement
B
The table is enforced by using both row and column access control
C
The table is enforced by using column access control
R
The table is enforced by using row access control
 G
VERSIONING_SCHEMA
VARCHAR(128)
NOT NULL
WITH DEFAULT
 Indicates the schema name of the history table if the table is a system-period temporal table with versioning. Indicates the schema name of the system-period temporal table if the table is a history table. Otherwise, the value is blank. G
VERSIONING_TABLE
VARCHAR(128)
NOT NULL
WITH DEFAULT
 Indicates the table name of the history table if the table is a system-period temporal table with versioning. Indicates the table name of system-period temporal table if the table is a history table. Otherwise, the value is blank. G
HASHKEYCOLUMNS
SMALLINT
NOT NULL
WITH DEFAULT
 The number of columns in the hash key of the table. The value is 0 if the row describes a view, an alias, or a created temporary table. G
ARCHIVING_SCHEMA
VARCHAR(128)
NOT NULL
WITH DEFAULT
 Contains a schema name as follows:
  • If the table is an archive-enabled table, this column contains the schema name of the archive table.
  • If the table is an archive table, this column contains the schema name of the archive-enabled table.
  • If the table is not an archive-enabled table or an archive table, the value is blank.
 G
ARCHIVING_TABLE
VARCHAR(128)
NOT NULL
WITH DEFAULT
 Contains a table name as follows:
  • If the table is an archive-enabled table, this column contains the table name of the archive table.
  • If the table is an archive table, this column contains the table name of the archive-enabled table.
  • If the table is not an archive-enabled table or an archive table, the value is blank.
 G
STATS_FEEDBACK
CHAR (1)
NOT NULL
WITH DEFAULT
 When a query qualifies for statistics collection based on DSNZPARM STATFDBK_SCOPE, this column controls whether statistics recommendations for this table are placed in SYSIBM.SYSSTATFEEDBACK. You can update this flag to 'Y' or 'N' to enable or disable collection for the table. The default value is 'Y'. G
REGENERATETS TIMESTAMP(12) NOT NULL The time when the object was regenerated. The value is valid only for objects that can be regenerated. If no regeneration has occurred, this column contains the same value as the CREATEDTS column. G
KEYLABEL VARCHAR(192) NOT NULL WITH DEFAULT The key label that is specified at the table level. Otherwise, the value is an empty string.