SQL Reference

SYSCAT.TABLES

Contains one row for each table, view, nickname or alias that is created. All of the catalog tables and views have entries in the SYSCAT.TABLES catalog view.

Table 91. SYSCAT.TABLES Catalog View
Column Name Data Type Nullable Description
TABSCHEMA VARCHAR(128)
Qualified name of the table, view, nickname or alias.
TABNAME VARCHAR(128)
DEFINER VARCHAR(128)
User who created the table, view, nickname or alias.
TYPE CHAR(1)
The type of object:

A = Alias

H = Hierarchy table

N = Nickname

S = Summary table

T = Table

U = Typed table

V = View

W = Typed view

STATUS CHAR(1)
The type of object:

N = Normal table, view, alias or nickname

C = Check pending on table or nickname

X = Inoperative view or nickname

BASE_TABSCHEMA VARCHAR(128) Yes If TYPE = A, these columns identify the table, view, alias or nickname that is referenced by this alias; otherwise they are null.
BASE_TABNAME VARCHAR(128) Yes
ROWTYPESCHEMA VARCHAR(128) Yes Contains the qualified name of the rowtype of this table, where applicable. Null otherwise.
ROWTYPENAME VARCHAR(18)
CREATE_TIME TIMESTAMP
The timestamp indicating when the object was created.
STATS_TIME TIMESTAMP Yes Last time when any change was made to recorded statistics for this table. Null if no statistics available.
COLCOUNT SMALLINT
Number of columns in table.
TABLEID SMALLINT
Internal table identifier.
TBSPACEID SMALLINT
Internal identifier of primary table space for this table.
CARD BIGINT
Total number of rows in the table. For tables in a table hierarchy, its the number of rows at the given level of the hierarchy -1 if statistics are not gathered or the row describes a view or alias; -2 for hierarchy tables (H-tables)
NPAGES INTEGER
Total number of pages on which the rows of the table exist; -1 if statistics are not gathered or the row describes a view or alias; -2 for subtables or H-tables.
FPAGES INTEGER
Total number of pages; -1 if statistics are not gathered or the row describes a view or alias; -2 for subtables or H-tables.
OVERFLOW INTEGER
Total number of overflow records in the table; -1 if statistics are not gathered or the row describes a view or alias; -2 for subtables or H-tables.
TBSPACE VARCHAR(18) Yes Name of primary table space for the table. If no other table space is specified, all parts of the table are stored in this table space. Null for aliases and views.
INDEX_TBSPACE VARCHAR(18) Yes Name of table space that holds all indexes created on this table. Null for aliases and views, or if the INDEX IN clause was omitted or specified with the same value as the IN clause of the CREATE TABLE statement.
LONG_TBSPACE VARCHAR(18) Yes Name of table space that holds all long data (LONG or LOB column types) for this table. Null for aliases and views, or if the LONG IN clause was omitted or specified with the same value as the IN clause of the CREATE TABLE statement.
PARENTS SMALLINT Yes Number of parent tables of this table (the number of referential constraints in which this table is a dependent).
CHILDREN SMALLINT Yes Number of dependent tables of this table (the number of referential constraints in which this table is a parent).
SELFREFS SMALLINT Yes Number of self-referencing referential constraints for this table (the number of referential constraints in which this table is both a parent and a dependent).
KEYCOLUMNS SMALLINT Yes Number of columns in the primary key of the table.
KEYINDEXID SMALLINT Yes Index ID of the primary index. This field is null or 0 if there is no primary key.
KEYUNIQUE SMALLINT
Number of unique constraints (other than primary key) defined on this table.
CHECKCOUNT SMALLINT
Number of check constraints defined on this table.
DATACAPTURE CHAR(1)

Y = Table participates in data replication

N = Does not participate

L = Table participates in data replication, including replication of LONG VARCHAR and LONG VARGRAPHIC columns


CONST_CHECKED CHAR(32)
Byte 1 represents foreign key constraints. Byte 2 represents check constraints. Byte 5 represents summary table. Byte 6 represents generated columns. Other bytes are reserved. Encodes constraint information on checking. Values:

Y = Checked by system

U = Checked by user

N = Not checked (pending)

W = Was in a 'U' state when the table was placed in check pending (pending)

PMAP_ID SMALLINT Yes Identifier of the partitioning map used by this table. Null for aliases and views.
PARTITION_MODE CHAR(1)
Mode used for tables in a partitioned database.

H = Hash on the partitioning key

R = Table replicated across database partitions

Blank for aliases, views and tables in single partition nodegroups with no partitioning key defined. Also blank for nicknames.

LOG_ATTRIBUTE CHAR(1)

0 = Default logging

1 = Table created not logged initially


PCTFREE SMALLINT
Percentage of each page to be reserved for future inserts. Can be changed by ALTER TABLE.
APPEND_MODE CHAR(1)
Controls how rows are inserted on pages:

N = New rows are inserted into existing spaces if available

Y = New rows are appended at end of data

Initial value is N.
REFRESH CHAR(1)
Refresh mode

D = Deferred

I = Immeidate

O = Once

Blank if not a summary table

REFRESH_TIME TIMESTAMP Yes For REFRESH = D or O, timestamp of the REFRESH TABLE statement that last refreshed the data. Otherwise null.
LOCKSIZE CHAR(1)
Indicates preferred lock granularity for tables when accessed by DML statements. Only applies to tables. Possible values are:

R = Row

T = Table

Blank if not applicable

Initial value is R.
VOLATILE CHAR(1)

C = Cardinality of the table is volatile

Blank if not applicable


REMARKS VARCHAR(254) Yes User-provided comment.


[ Top of Page | Previous Page | Next Page ]