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 114 | 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 | Timestamp when the table was last altered or created. |
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(2M) CCSID 13488
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:
|
TABLE_DEFINER | DEFINER | VARCHAR(128) | Name of the user that defined the table. |
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.