SYSCAT.TABLES catalog view

Each row represents a table, view, alias, or nickname. Each table or view hierarchy has one additional row that represents the hierarchy table or hierarchy view that implements the hierarchy. Catalog tables and views are included.

Table 1. SYSCAT.TABLES Catalog View
Column Name Data Type Nullable Description
TABSCHEMA VARCHAR (128)   Schema name of the object.
TABNAME VARCHAR (128)   Unqualified name of the object.
OWNER VARCHAR (128)   Authorization ID of the owner of the table, view, alias, or nickname.
OWNERTYPE CHAR (1)  
  • S = The owner is the system
  • U = The owner is an individual user
TYPE CHAR (1)   Type of object.
  • A = Alias
  • G = Created temporary table
  • H = Hierarchy table
  • L = Detached table
  • N = Nickname
  • S = Materialized query table
  • T = Table (untyped)
  • U = Typed table
  • V = View (untyped)
  • W = Typed view
STATUS CHAR (1)   Status of the object.
  • C = Set integrity pending
  • N = Normal
  • X = Inoperative
BASE_TABSCHEMA VARCHAR (128) Y If TYPE = 'A', contains the schema name of the table, view, alias, or nickname that is referenced by this alias; null value otherwise.
BASE_TABNAME VARCHAR (128) Y If TYPE = 'A', contains the unqualified name of the table, view, alias, or nickname that is referenced by this alias; null value otherwise.
ROWTYPESCHEMA VARCHAR (128) Y Schema name of the row type for this table, if applicable; null value otherwise.
ROWTYPENAME VARCHAR (128) Y Unqualified name of the row type for this table, if applicable; null value otherwise.
CREATE_TIME TIMESTAMP   Time at which the object was created.
ALTER_TIME TIMESTAMP   Time at which the object was last altered.
INVALIDATE_TIME TIMESTAMP   Time at which the object was last invalidated.
STATS_TIME TIMESTAMP Y Time at which any change was last made to recorded statistics for this object. The null value if statistics are not collected.
COLCOUNT SMALLINT   Number of columns, including inherited columns (if any).
TABLEID SMALLINT   Internal logical object identifier.
TBSPACEID SMALLINT   Internal logical identifier for the primary table space for this object.
CARD BIGINT   Total number of rows in the table; -1 if statistics are not collected.
NPAGES BIGINT   Total number of pages on which the rows of the table exist; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table. Total number of files for a Hadoop or HBase table; -1 if statistics are not collected, or if the table is neither a Hadoop table nor an HBase table.
MPAGES BIGINT   Total number of pages for table metadata. Non-zero only for a table that is organized by column. -1 for a view, an alias, or if statistics are not collected; -2 for subtables or hierarchy tables. Not applicable for a Hadoop table.
FPAGES BIGINT   Total number of pages; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table. The size of a Hadoop or HBase table in kilobytes; -1 if statistics are not collected, or if the table is neither a Hadoop table nor an HBase table.
NPARTITIONS BIGINT   Reserved for future use.Total number of partitions for a Hadoop table: -1 if statistics not collected, or if not a Hadoop table.
NFILES BIGINT   Reserved for future use.Total number of files for a Hadoop or HBase table; -1 if statistics not collected, or if not a Hadoop or HBase table.
TABLESIZE BIGINT   Reserved for future use.The size of a Hadoop or HBase table in kilobytes; -1 if statistics not collected, or if not a Hadoop or HBase table.
OVERFLOW BIGINT   Total number of overflow records in the table; -1 for a view or alias, or if statistics are not collected; -2 for a subtable or hierarchy table.
TBSPACE VARCHAR (128) Y Name of the primary table space for the table. If no other table space is specified, all parts of the table are stored in this table space. The null value for aliases, views, and partitioned tables.
INDEX_TBSPACE VARCHAR (128) Y Name of the table space that holds all indexes created on this table. The null value for aliases, views, and partitioned tables, 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 (128) Y Name of the table space that holds all long data (LONG or LOB column types) for this table. The null value for aliases, views, and partitioned tables, 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 Y Number of parent tables for this object; that is, the number of referential constraints in which this object is a dependent.
CHILDREN SMALLINT Y Number of dependent tables for this object; that is, the number of referential constraints in which this object is a parent.
SELFREFS SMALLINT Y Number of self-referencing referential constraints for this object; that is, the number of referential constraints in which this object is both a parent and a dependent.
KEYCOLUMNS SMALLINT Y Number of columns in the primary key.
KEYINDEXID SMALLINT Y Index identifier for the primary key index: 0 or the null value if there is no primary key.
KEYUNIQUE SMALLINT   Number of unique key constraints (other than the primary key constraint) defined on this object.
CHECKCOUNT SMALLINT   Number of check constraints that are defined on this object.
DATACAPTURE CHAR (1)  
  • L = Table participates in data replication, including replication of LONG VARCHAR and LONG VARGRAPHIC columns
  • N = Table does not participate in data replication
  • Y = Table participates in data replication, excluding replication of LONG VARCHAR and LONG VARGRAPHIC columns
CONST_CHECKED CHAR (32)  
  • Byte 1 represents foreign key constraint.
  • Byte 2 represents check constraint.
  • Byte 5 represents materialized query table.
  • Byte 6 represents generated column.
  • Byte 7 represents staging table.
  • Byte 8 represents data partitioning constraint.
  • Other bytes are reserved for future use.
Possible values are:
  • F = In byte 5, the materialized query table cannot be refreshed incrementally. In byte 7, the content of the staging table is incomplete and cannot be used for incremental refresh of the associated materialized query table.
  • N = Not checked
  • U = Checked by user
  • W = Was in 'U' state when the table was placed in set integrity pending state
  • Y = Checked by system
PMAP_ID SMALLINT Y Identifier for the distribution map that is currently in use by this table (the null value for aliases or views).
PARTITION_MODE CHAR (1)   Indicates how data is distributed among database partitions in a partitioned database system.
  • H = Hashing
  • R = Replicated across database partitions
  • Blank = No database partitioning
LOG_ATTRIBUTE CHAR (1)  
  • Always 0. This column is no longer used.
PCTFREE SMALLINT   Percentage of each page to be reserved for future inserts.
APPEND_MODE CHAR (1)   For row-organized tables, controls how rows are inserted into pages.
  • N = New rows are inserted into existing spaces, if available
  • Y = New rows are appended to the end of the data
For column-organized tables, which are implicitly always in append mode, this field always has a value of 'N'.
REFRESH CHAR (1)   Refresh mode.
  • D = Deferred
  • I = Immediate
  • O = Once
  • Blank = Not a materialized query table
REFRESH_TIME TIMESTAMP Y For REFRESH = 'D' or 'O', time at which the data was last refreshed (REFRESH TABLE statement); null value otherwise.
LOCKSIZE CHAR (1)   Indicates the preferred lock granularity for tables that are accessed by data manipulation language (DML) statements. Applies to tables only. Possible values are:
  • I = Block insert
  • R = Row
  • T = Table
  • Blank = Not applicable
VOLATILE CHAR (1)  
  • C = Cardinality of the table is volatile
  • Blank = Not applicable
ROW_FORMAT CHAR (1)   Not used.
PROPERTY VARCHAR (32)   Properties for a table. A single blank indicates that the table has no properties. The following is position within string, value, and meaning:
  • 1, Y = User maintained materialized query table
  • 2, Y = Staging table
  • 3, Y = Propagate immediate
  • 11, Y = Nickname that will not be cached
  • 13, Y = Statistical view
  • 18, Y = Iceberg DATALAKE table
  • 19, Y = Statistical view for an index with an expression-based key
  • 20, Y = Column-organized table
  • 21, Y = Synopsis table
  • 22, Y = DATALAKE table
  • 22, Y = Hadoop table
  • 23, Y = Shadow table (materialized query table maintained by replication)
  • 24, Y = HBase table
  • 25, Y = Random distribution table
  • 27, Y = External table
STATISTICS_PROFILE CLOB (10M) Y RUNSTATS command used to register a statistical profile for the object.
COMPRESSION CHAR (1)  
  • B = Both value and row compression are enabled
  • N = No compression is enabled; a row format that does not support compression is used
  • R = Row compression is enabled; a row format that supports compression might be used
  • V = Value compression is enabled; a row format that supports compression is used
  • Blank = Not applicable
ROWCOMPMODE CHAR (1)   Row compression mode for the table.
  • A = ADAPTIVE
  • S = STATIC
  • Blank = Row compression is not enabled
ACCESS_MODE CHAR (1)   Access restriction state of the object. These states apply to objects that are in set integrity pending state or to objects that were processed by a SET INTEGRITY statement only. Possible values are:
  • D = No data movement
  • F = Full access
  • N = No access
  • R = Read-only access
CLUSTERED CHAR (1) Y
  • T = Table is clustered by insert time
  • Y = Table is clustered by dimensions (even if only by one dimension)
  • Null value = Table is not clustered by dimensions or insert time
ACTIVE_BLOCKS BIGINT   Total number of active blocks in the table, or -1. Applies to multidimensional clustering (MDC) tables or insert time clustering (ITC) tables only.
DROPRULE CHAR (1)  
  • N = No rule
  • R = Restrict rule applies on drop
MAXFREESPACESEARCH SMALLINT   Reserved for future use.
AVGCOMPRESSEDROWSIZE SMALLINT   Average length (in bytes) of compressed rows in this table; -1 if statistics are not collected.
AVGROWCOMPRESSIONRATIO REAL   For compressed rows in the table, this is the average compression ratio by row, that is, the average uncompressed row length divided by the average compressed row length; -1 if statistics are not collected.
AVGROWSIZE SMALLINT   Average length (in bytes) of both compressed and uncompressed rows in this table; -1 if statistics are not collected.
PCTROWSCOMPRESSED REAL   Compressed rows as a percentage of the total number of rows in the table; -1 if statistics are not collected.
LOGINDEXBUILD VARCHAR (3) Y Level of logging that is to be performed during create, re-create, or reorganize index operations on the table.
  • OFF = Index build operations on the table is logged minimally
  • ON = Index build operations on the table is logged completely
  • Null value = Value of the logindexbuild database configuration parameter is used to determine whether index build operations are to be logged completely
CODEPAGE SMALLINT   Code page of the object. This is the default code page that is used for all character columns, triggers, check constraints, and expression-generated columns.
COLLATIONSCHEMA VARCHAR (128)   Schema name of the collation for the table.
COLLATIONNAME VARCHAR (128)   Unqualified name of the collation for the table.
COLLATIONSCHEMA_ORDERBY VARCHAR (128)   Schema name of the collation for ORDER BY clauses in the table.
COLLATIONNAME_ORDERBY VARCHAR (128)   Unqualified name of the collation for ORDER BY clauses in the table.
ENCODING_SCHEME CHAR (1)  
  • A = CCSID ASCII was specified
  • U = CCSID UNICODE was specified
  • Blank = CCSID clause was not specified
PCTPAGESSAVED SMALLINT N The approximate percentage of pages that are saved in a row-organized table as a result of row compression. For a column-organized table, the estimate is based on the number of data pages that are needed to store the table in uncompressed row organization.-1 if statistics are not collected.
LAST_REGEN_TIME TIMESTAMP Y Time at which any views or check constraints on the table were last regenerated.
SECPOLICYID INTEGER   Identifier for the security policy that protects the table; 0 for non-protected tables.
PROTECTIONGRANULARITY CHAR (1)  
  • B = Both column- and row-level granularity
  • C = Column-level granularity
  • R = Row-level granularity
  • Blank = Non-protected table
AUDITPOLICYID INTEGER Y Identifier for the audit policy.
AUDITPOLICYNAME VARCHAR (128) Y Name of the audit policy.
AUDITEXCEPTIONENABLED CHAR (1)   Reserved for future use.
DEFINER1 VARCHAR (128)   Authorization ID of the owner of the table, view, alias, or nickname.
ONCOMMIT CHAR (1)   Specifies the action that is taken on the created temporary table when a COMMIT operation is performed.
  • D = Delete rows
  • P = Preserve rows
  • Blank = Table is not a created temporary table
LOGGED CHAR (1)   Specifies whether the created temporary table is logged.
  • N = Not logged
  • Y = Logged
  • Blank = Table is not a created temporary table
ONROLLBACK CHAR (1)   Specifies the action that is taken on the created temporary table when a ROLLBACK operation is performed.
  • D = Delete rows
  • P = Preserve rows
  • Blank = Table is not a created temporary table
LASTUSED DATE   Date when the table was last used by any DML statement or the LOAD command. This column is not updated for an alias, created temporary table, nickname, or view. This column is not updated when the table is used on an HADR standby database. The default value is '0001-01-01'. This value is updated asynchronously not more than once within a 24 hour period and might not reflect usage within the last 15 minutes.
CONTROL CHAR (1)   Access control that is enforced for the table
  • B = Both row and column
  • C = Column
  • R = Row
  • Blank = No access control
TEMPORALTYPE CHAR (1)   Type of temporal table.
  • A = Application-period temporal table
  • B = Bitemporal table
  • N = Not a temporal table
  • S = System-period temporal table
TABLEORG CHAR(1)  
  • C = Column-organized table
  • R = Row-organized table
  • N = Not a table
EXTENDED_ROW_SIZE CHAR(1)   Indicates whether the row size of a table that is organized by row exceeds the maximum record length for the page size of the table space in which it is defined.
  • N = Row size does not exceed the maximum record length for the page size
  • Y = Row size exceeds the maximum record length for the page size
  • blank = Not applicable
PCTEXTENDEDROWS REAL   Extended rows as a percentage of the total number of rows in the table; -1 if statistics are not collected.
REMARKS VARCHAR (254) Y User-provided comments, or the null value.
Note:
  1. The DEFINER column is included for backwards compatibility. See OWNER.