Views owned by SYSTEM and CATALOG

• Views owned by special user SYSTEM

• Views owned by special user CATALOG

• Pseudotables owned by special user SYSTEM

Summary of System Catalog Views by Function

Table 8-1 “System Catalog Views by Function” shows the system catalog views arranged according to their function.

Using the System Catalog

ALLBASE/SQL creates and initializes the system catalog during DBEnvironment configuration. Thereafter, you query the system views and pseudotables to obtain required information. All view names must be fully qualified when querying the system catalog.

As a DBA, you have limited capabilities to access and alter the base system tables owned by HPRDBSS and STOREDSECT. For example, you cannot read or write directly to these tables. You cannot issue any of the following statements on the system catalog base tables:

   ALTER TABLE (except SetTypeSpecification)

   CREATE INDEX

   DELETE

   DROP INDEX

   DROP TABLE

   GRANT

   INSERT

   LOCK TABLE

   REVOKE

   TRANSFER OWNERSHIP

   UPDATE

However, you can issue some statements on the base system tables, which you can use to tune the DBEnvironment for concurrency and performance. For example, you can

Through the SYSTEM.TABLE view, you can see that the HPRDBSS and STOREDSECT tables exist. However, you cannot see any other information, such as columns and indexes, about the HPRDBSS and STOREDSECT tables. The definitions of the system catalog views and pseudotables are complete in the system catalog.

System Catalog Views

System views are like all other views in that statements normally not allowed on views cannot be executed on the system views. In addition, the following statements, normally allowed on views, are not allowed on system views:

   DELETE

   DROP VIEW

   GRANT (except SELECT)

   INSERT

   REVOKE (except SELECT)

   TRANSFER OWNERSHIP

   UPDATE

You can access the system views with SELECT statements like the following:

   isql=> SELECT * FROM System.DBEFile;

   isql=> SELECT * FROM System.Table

   > WHERE Owner='SYSTEM';

   isql=> SELECT IndexName, TableName FROM System.Index

   > WHERE Cluster=1;

Because the owner of the system views is SYSTEM, you must fully qualify the names of all system views.

Partition values are stored in the system catalog. You can verify the attributes of table partitions by examining the SYSTEM.TABLE and the SYSTEM.PARTITION views.

Using UPDATE STATISTICS on System Views

The UPDATE STATISTICS statement, which is not normally allowed on a view, is accepted on system views. A user with DBA authority can perform an UPDATE STATISTICS on a system view. ALLBASE/SQL makes an exception for the system views so that the DBA may monitor the system catalog for storage use and performance tuning. The following system views are updated by the UPDATE STATISTICS statement:

   SYSTEM.COLUMN          SYSTEM.CONSTRAINTINDEX   SYSTEM.DBEFILE

   SYSTEM.DBEFILESET      SYSTEM.HASH              SYSTEM.INDEX

   SYSTEM.TABLE

You cannot use UPDATE STATISTICS on CATALOG views, and UPDATE STATISTICS does not update the following pseudotables:

   SYSTEM.ACCOUNT         SYSTEM.CALL              SYSTEM.COUNTER

   SYSTEM.TRANSACTION     SYSTEM.USER

Initially, only users with DBA authority have SELECT authority on views owned by SYSTEM. SELECT authority can be granted to non-DBA users. Views can be created on system views to grant partial access to non-DBA users. If you do not want to restrict access to system views, you can grant SELECT authority to the special DBEUserID PUBLIC. However, you also have the option of keeping the contents of the DBEnvironment confidential.

Locking of the System Catalog

Many ALLBASE/SQL statements have the effect of obtaining locks on parts of the system catalog. In particular, UPDATE STATISTICS acquires many locks that can affect concurrency. This overhead can affect performance considerably. Refer to the appendix, "Locks Held on the System Catalog by SQL Statements," for a complete list of locks obtained on the system catalog by ALLBASE/SQL statements.

The rest of this chapter contains the column definition for each system view, plus a brief description of the purpose of the view, and a sample SELECT. The examples contain the DBEUserIDs, tables, indexes, views, groups, and other objects from the sample DBEnvironment. The columns of each view are listed in the order in which they are created.