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.

Start of change Additional table attributes are available through the QSYS2.SYSFILES view. End of change

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 ^¹	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 D Data file or alias S Source file
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 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	VARCHAR(11)	Indicates whether the table is a partitioned table: DISTRIBUTED The table is a distributed table. 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.
CONTROL	CONTROL	CHAR(1)	Indicates whether the table is enforced by row or column access control: blank No access control enforcement. R The table is enforced by row access control. C The table is enforced by column access control. B The table is enforced by both row and column access control.
TEMPORAL_TYPE	TEMPORALTY	CHAR(1)	Indicates the type of temporal table: H History table for a system-period temporal table N Not a temporal table S System-period temporal table

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