HPlogo ALLBASE/SQL Reference Manual > Chapter 2 Using ALLBASE/SQL

Understanding the System Catalog

MPE documents

Complete PDF
Table of Contents
Index

E0300 Edition 9 ♥
E0399 Edition 8
E0897 Edition 7

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 Describes 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.



Administering a Database

Chapter 3 SQL Queries