The SYSTABLES view contains one row for every table, view or alias in the
SQL schema, including the tables and views of the SQL catalog. The
following table describes the columns in the SYSTABLES view:
Column name | System Column Name | Data Type | Description |
---|---|---|---|
TABLE_NAME | NAME | VARCHAR(128) | Name of the table, view or alias. This is the SQL table, view or alias name if it exists; otherwise, it is the system table, view or alias name. |
TABLE_OWNER | CREATOR | VARCHAR(128) | Owner of the table, view or alias |
TABLE_TYPE | TYPE | CHAR(1) | If the row describes a table, view, or alias:
|
COLUMN_COUNT | COLCOUNT | INTEGER | Number of columns in the table or view. Zero for an alias. |
ROW_LENGTH | RECLENGTH 111 | INTEGER | Maximum length of any record in the table. Zero for an alias. |
TABLE_TEXT | LABEL | CHAR(50) | A character string provided with the LABEL statement. |
LONG_COMMENT | REMARKS |
VARCHAR(2000) Nullable | A character string supplied with the COMMENT statement.
Contains the null value if there is no long comment. |
TABLE_SCHEMA | DBNAME | VARCHAR(128) | Name of the SQL schema that contains the table, view or alias |
LAST_ALTERED_TIMESTAMP | ALTEREDTS | TIMESTAMP | Table last changed timestamp |
SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | System table name. |
SYSTEM_TABLE_SCHEMA | SYS_DNAME | CHAR(10) | System schema name |
FILE_TYPE | FILETYPE | CHAR(1) | File type
|
BASE_TABLE_SCHEMA | TBDBNAME |
VARCHAR(128) Nullable | For an alias, this is the name of the SQL schema that contains the table
or view the alias is based on.
Contains the null value if the table is not an alias. |
BASE_TABLE_NAME | TBNAME |
VARCHAR(128) Nullable | For an alias, this is the name of the table or view the alias is based
on.
Contains the null value if the table is not an alias. |
BASE_TABLE_MEMBER | TBMEMBER |
VARCHAR(10) Nullable | For an alias, this is the name of the file member the alias is based
on. Contains *FIRST if this is an alias, but a member name was not
specified.
Contains the null value if the table is not an alias. |
SYSTEM_TABLE | SYSTABLE | CHAR(1) | System table
|
SELECT_OMIT | SELECTOMIT | CHAR(1) | Select/omit logical file
|
IS_INSERTABLE_INTO | INSERTABLE | VARCHAR(3) | Identifies whether an INSERT is allowed on the table.
|
IASP_NUMBER | IASPNUMBER | SMALLINT | Specifies the independent auxiliary storage pool (IASP) number. |
ENABLED | ENABLED | VARCHAR(3)
Nullable | Indicates whether the materialized query table is enabled for
optimization:
Contains the null value if the table is not a materialized query table. |
MAINTENANCE | MAINTAIN | VARCHAR(6)
Nullable | Indicates whether the materialized query table is user or system
maintained:
Contains the null value if the table is not a materialized query table. |
REFRESH | REFRESH | VARCHAR(9)
Nullable | Indicates the materialized query table REFRESH option:
Contains the null value if the table is not a materialized query table. |
REFRESH_TIME | REFRESHDTS | TIMESTAMP
Nullable | Indicates the timestamp of the last materialized query table
REFRESH:
Contains the null value if the table is not a materialized query table or if the table has never been refreshed. |
MQT_DEFINITION | MQTDEF | DBCLOB(1048576)
Nullable | Indicates the query expression of the materialized query table:
Contains the null value if the table is not a materialized query table. |
ISOLATION | ISOLATION | CHAR(2)
Nullable | Indicates the isolation level used for the select-statement when
refreshing the materialized query table:
RR Repeatable Read (*RR) RS Read Stability (*ALL) CS Cursor Stability (*CS) UR Uncommitted Read (*CHG) NO None (*NONE) Contains the null value if the table is not a materialized query table. |
PARTITION_TABLE | PART_TABLE | VARCHAR(3) | Indicates whether the table is a partitioned table:
|
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.