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.

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 ]