SYSTABLES
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 1 | INTEGER | Maximum length of any record in the table. Zero for an alias. |
TABLE_TEXT | LABEL | VARGRAPHIC(50) CCSID 1200 | A character string provided with the LABEL statement. |
LONG_COMMENT | REMARKS | VARGRAPHIC(2000) CCSID
1200 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_CATALOG | LOCATION | VARCHAR(18) Nullable
|
For an alias, this is the name of
the relational database that contains the table or view the alias
is based on. Contains the null value if the table is not an alias. |
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:
Contains the null value if the table is not a materialized query table. |
PARTITION_TABLE | PART_TABLE | VARCHAR(11) | Indicates whether the table is a
partitioned table:
|
TABLE_DEFINER | DEFINER | VARCHAR(128) | Name of the user that defined the table. |
MQT_RESTORE_DEFERRED | MQTRSTDFR | CHAR(1) | If the table is a materialized query
table:
Contains the null value if the table is not a materialized query table. |
ROUNDING_MODE | DECFLTRND | CHAR(1) | Indicates the DECFLOAT rounding mode of the
materialized query table or view:
Contains the null value if the table is not a view or MQT, or if the materialized query table or view does not have an expression that references a DECFLOAT column, function, or constant. |
CONTROL | CONTROL | CHAR(1) | Indicates whether the table is enforced
by row or column access control:
|