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:

Table 1. 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:
A
Alias
L
Logical file
M
Materialized query table
P
Physical file
T
Table
V
View
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 Start of changeVARGRAPHIC(50) CCSID 1200End of change A character string provided with the LABEL statement.
LONG_COMMENT REMARKS Start of changeVARGRAPHIC(2000) CCSID 1200

Nullable

End of change
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
D
Data file or alias
S
Source file
Start of changeBASE_TABLE_CATALOGEnd of change Start of changeLOCATIONEnd of change Start of changeVARCHAR(18)

Nullable

End of change
Start of changeFor 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.

End of change
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
N
The table is not a system table.
Y
The table is a system table.
SELECT_OMIT SELECTOMIT CHAR(1) Select/omit logical file
D
The table is a dynamic select/omit logical file.
N
The table is not a select/omit logical file.
Y
The table is a select/omit logical file.
IS_INSERTABLE_INTO INSERTABLE VARCHAR(3) Identifies whether an INSERT is allowed on the table.
NO
An INSERT is not allowed on this table.
YES
An INSERT is allowed on this 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:
NO
The materialized query table is not enabled for optmization.
YES
The materialized query table is enabled for optmization.

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:
USER
The materialized query table is user 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:
DEFERRED
The materialized query table is REFRESH DEFERRED.

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)
NC
No Commit (*NONE)

Contains the null value if the table is not a materialized query table.

PARTITION_TABLE PART_TABLE Start of changeVARCHARA(11)End of change Indicates whether the table is a partitioned table:
Start of changeDISTRIBUTEDEnd of change
Start of changeThe table is a distributed table.End of change
NO
The table is not a partitioned table.
YES
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:
Y
The MQT is deferred as the result of a restore.
N
The MQT is not deferred.

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:
C
ROUND_CEILING
D
ROUND_DOWN
F
ROUND_FLOOR
G
ROUND_HALF_DOWN
E
ROUND_HALF_EVEN
H
ROUND_HALF_UP
U
ROUND_UP

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.

1 The length is the number of bytes passed in database buffers, not the internal storage length.