SYSTABLES

The systables system catalog table contains a row for each table object (a table, view, synonym, or in HCL OneDB™, a sequence) that has been defined in the database, including the tables and views of the system catalog.

Table 1. SYSTABLES table column descriptions
Column	Type	Explanation
tabname	VARCHAR(128)	Name of table, view, synonym, or sequence
owner	CHAR(32)	Owner of table (user informix for system catalog tables and username for database tables)
partnum	INTEGER	Physical storage location code
tabid	SERIAL	System-assigned sequential identifying number
rowsize	SMALLINT	Maximum row size in bytes ( < 32,768)
ncols	SMALLINT	Number of columns in the table
nindexes	SMALLINT	Number of indexes on the table
nrows	FLOAT	Number of rows in the table
created	DATE	Date when table was created or last modified
version	INTEGER	Number that changes when table is altered
tabtype	CHAR(1)	Code indicating the type of table object: T = Table E = External Table V = View Q = Sequence P = Private synonym S = Public synonym (Type S is unavailable in an ANSI-compliant database.)
locklevel	CHAR(1)	Lock mode for the table: B = Page and row level P = Page level R = Row level
npused	FLOAT	Number of data pages that have ever been initialized in the tablespace by the database server
fextsize	INTEGER	Size of initial extent (in KB)
nextsize	INTEGER	Size of all subsequent extents (in KB)
flags	SMALLINT	Codes for classifying permanent tables: ROWID 1 - Has rowid column defined UNDER 2 - Table created under a supertable VIEWREMOTE 4 - View is based on a remote table CDR 8 - Has CDRCOLS defined RAW 16 - (HCL OneDB) RAW table EXTERNAL 32- External table AUDIT 64 - Audit table attribute - FGA AQT 128 - View is an AQT for DWA offloading VIRTAQT 256 - View is a virtual AQT
site	VARCHAR(128)	Reserved for future use
dbname	VARCHAR(128)	Reserved for future use
type_xid	INTEGER	Code from sysxtdtypes.extended_id for typed tables, or `0` for untyped tables
am_id	INTEGER	Access method code (key to sysams table) NULL or 0 indicates built-in storage manager
pagesize	INTEGER	The pagesize, in bytes, of the dbspace (or dbspaces, if the table is fragmented) where the table data resides.
ustlowts	DATETIME YEAR TO FRACTION (5)	When table, row, and page-count statistics were last recorded
secpolicyid	INTEGER	ID of the SECURITY policy attached to the table. NULL for non-protected tables
protgranularity	CHAR(1)	LBAC granularity level: R: Row level granularity C: Column level granularity B: Both column and row granularity Blank for non-protected tables
statlevel	CHAR(1)	Statistics level T = table F = fragment A = automatic
statchange	SMALLINT	For internal use only

Each table, view, sequence, and synonym recorded in the systables table is assigned a tabid, which is a system-assigned SERIAL value that uniquely identifies the object. The first 99 tabid values are reserved for the system catalog. The tabid of the first user-defined table object in a database is always 100.

The tabid column is indexed and contains only unique values. A composite index on the tabname and owner columns also requires unique values.

The version column contains an encoded number that is stored in systables when a new table is created. Portions of this value are incremented when data-definition statements, such as ALTER INDEX, ALTER TABLE, DROP INDEX, and CREATE INDEX, are performed on the table.

In the flags column, ST_RAW represents a nonlogging permanent table in a database that supports transaction logging.

The setting of the SQL_LOGICAL_CHAR parameter is encoded into the systables.flags column value in the row that describes the ' VERSION' table. Note the leading blank space in the identifier of this system-generated table.

To determine whether the database enables the SQL_LOGICAL_CHAR configuration parameter, which can apply logical character semantics to the declarations of character columns, you can execute the following query:

SELECT flags INTO $value FROM 'informix'.systables WHERE tabname = ' VERSION';

Because the SQL_LOGICAL_CHAR setting is encoded in the two least significant bits of the " VERSION.flags" value, you can calculate its setting from the returned flags value by the following formula:

SQL_LOGICAL_CHAR = (value & 0x03) + 1

Here & is the bitwise AND operator. Any SQL_LOGICAL_CHAR setting greater than 1 indicates that SQL_LOGICAL_CHAR was enabled when the database was created, and that explicit or default maximum size specifications of character columns are multiplied by that setting.

When a prepared statement that references a database table is executed, the version value is checked to make sure that nothing has changed since the statement was prepared. If the version value has been changed by DDL operations that modified the table schema while automatic recompilation was disabled by the IFX_AUTO_REPREPARE setting of the SET ENVIRONMENT statement, the prepared statement is not executed, and you must prepare the statement again.

The npused column does not reflect the number of pages used for BYTE or TEXT data, nor the number of pages that are freed in DELETE or TRUNCATE operations.

The nrows column and the npused columns might not accurately reflect the number of rows and the number of data pages used by an external table unless the NUMROWS clause was specified when the external table was created. See the HCL OneDB Administrator's Guide for more information.

The systables table has two rows that store information about the database locale: GL_COLLATE with a tabid of 90 and GL_CTYPE with a tabid of 91. To view these rows, enter the following SELECT statement:

SELECT * FROM systables WHERE tabid=90 OR tabid=91;