Understanding the System Catalog

The system catalog is a collection of tables and views that contain data about the following:

Tables and views in a DBEnvironment
Any indexes, hash structures, constraints, and rules defined for tables
DBEFiles and DBEFileSets in the DBEnvironment
Specific authorities granted to each user
Programs that can access data in the DBEnvironment
Current DBEnvironment statistics
Temporary space for sorts
Procedures

ALLBASE/SQL uses the system catalog to maintain data integrity and to optimize data access. The system views are primarily a tool for the DBA. Initially, only the DBA can access these views. Other users need to be granted SELECT authority by the DBA to access them. Users without SELECT authority can retrieve descriptions of database objects they own from the CATALOG views. For information on system and catalog views, refer to chapter "System Catalog" in the ALLBASE/SQL Database Administration Guide.

When a DBEnvironment is first configured, the information in the system catalog describes the system tables and views themselves. As database objects are defined, their definitions are stored in the system catalog. As database activities occur, most of the information in the catalog is updated automatically, so the system catalog provides an up-to-date source of information on a DBEnvironment.

Immediately following an UPDATE STATISTICS statement, the views in the system catalog, summarized in Table 2-2, are a source of up-to-date information on a DBEnvironment and the structure and use of its databases. Refer to the ALLBASE/SQL Database Administration Guide for additional information on the system catalog.

Table 2-2 System Views

View Name	Purpose
SYSTEM.ACCOUNT	Identifies the I/O usage of current database sessions.
SYSTEM.CALL	Identifies current internal calls.
SYSTEM.CHECKDEF	Contains the search condition defined for each table check constraint. Contains the column name for each column check constraint.
SYSTEM.COLAUTH	Identifies users and groups and their column update and reference authorities.
SYSTEM.COLDEFAULT	Describes the default value of each column defined with a non-NULL default.
SYSTEM.COLUMN	Contains the definition of each column in each table and view.
SYSTEM.CONSTRAINT	Contains information on integrity constraints.
SYSTEM.CONSTRAINTCOL	Contains information on the columns within unique and referential constraints.
SYSTEM.CONSTRAINTINDEX	Describes each unique and referential constraint index.
SYSTEM.COUNTER	Describes the status of internal system counters.
SYSTEM.DBEFILE	Describes the characteristics of each DBEFile.
SYSTEM.DBEFILESET	Decribes the characteristics of each DBEFileset.
SYSTEM.GROUP	Describes each authorization group.
SYSTEM.HASH	Describes each hash structure.
SYSTEM.IMAGEKEY	Describes each Master and Detail Dataset key associated with TurboIMAGE databases attached to the DBE.
SYSTEM.INDEX	Describes each index.
SYSTEM.INSTALLAUTH	Identifies users and authorization groups that have been granted INSTALL authority.
SYSTEM.MODAUTH	Identifies users and groups and the programs they can run.
SYSTEM.PARAMDEFAULT	Describes the default value of each parameter defined with a non-NULL default.
SYSTEM.PARAMETER	Describes each parameter of each procedure.
SYSTEM.PARTITION	Contains partition information.
SYSTEM.PLAN	Stores the result of one GENPLAN for each session.
SYSTEM.PROCAUTH	Identifies users and groups and the procedures they can execute.
SYSTEM.PROCEDURE	Describes each procedure.
SYSTEM.PROCEDUREDEF	Contains the definition of each procedure.
SYSTEM.PROCRESULT	Describes procedure result columns.
SYSTEM.RULE	Describes each rule.
SYSTEM.RULECOLUMN	Describes columns an update rule checks for.
SYSTEM.RULEDEF	Contains the referencing, WHERE, and EXECUTE PROCEDURE clause of each rule.
SYSTEM.SECTION	Describes stored modules and views.
SYSTEM.SETOPTINFO	Contains SETOPT settings for optimizing specific stored sections.
SYSTEM.SPACEAUTH	Identifies users and groups and what DBEFileSets they can use when creating tables, or stored sections.
SYSTEM.SPACEDEFAULT	Identifies the default DBEFileSet to use for a new table or stored section.
SYSTEM.SPECAUTH	Identifies users and groups who have special authorities.
SYSTEM.TABAUTH	Identifies users and groups and table/view operations they can perform.
SYSTEM.TABLE	Contains a description of each table and view in the DBEnvironment, including size, owner, and associated DBEFileSet.
SYSTEM.TEMPSPACE	Defines the TempSpace locations.
SYSTEM.TPINDEX	Describes third-party indexes used in TurboIMAGE databases attached to the DBE.
SYSTEM.TRANSACTION	Identifies transactions.
SYSTEM.USER	Identifies users currently using the database.
SYSTEM.VIEWDEF	Contains the SELECT statement that created each view defined in the system.

Understanding the System Catalog

Technical documentation

» Table of Contents

» Index