HP 3000 Manuals HP 3000 Manuals
Views owned by SYSTEM and CATALOG
Three distinct groups of system catalog views are created by ALLBASE/SQL:
* Views owned by special user SYSTEM
* Views owned by special user CATALOG
* Pseudotables owned by special user SYSTEM
The SYSTEM views contain information about database objects owned by all
users, and access is restricted to users with DBA authority or SELECT
authority on the SYSTEM views. The DBA can grant SELECT on these views
to other users. SYSTEM views are used to perform administration tasks
such as monitoring system usage to improve performance and monitoring
physical storage to determine when to add DBEFiles.
The CATALOG views contain information about database objects owned by a
particular user. These views permit users without DBA authority or
SELECT authority on the SYSTEM views to examine system catalog
information for objects to which they have access.
In addition to system and catalog views, ALLBASE/SQL generates a set of
pseudotables. These contain statistical data, including counts and other
runtime information about system usage. Pseudotables are generated by
SQLCore each time they are accessed. They reside in shared memory, and,
although formatted to appear like tables, they are not physically stored
in the system catalog. Pseudotables are owned by SYSTEM, and are
accessible only to users with DBA authority.
Summary of System Catalog Views by Function
Table 8-1 shows the system catalog views arranged according to their
function.
Table 8-1. System Catalog Views by Function
-----------------------------------------------------------------------------------------------
| | | |
| Function | Name of View | Description |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| User Authority | SYSTEM.COLAUTH, CATALOG.COLAUTH | UPDATE authority on specific |
| | | columns in a table |
| | | |
| | SYSTEM.GROUP, CATALOG.GROUP | Authorization groups |
| | | |
| | SYSTEM.MODAUTH, CATALOG.MODAUTH | RUN authority on programs |
| | | |
| | SYSTEM.PROCAUTH, CATALOG.PROCAUTH | EXECUTE authority on procedures |
| | | |
| | SYSTEM.SPACEAUTH, | TABLESPACE and SECTIONSPACE |
| | CATALOG.SPACEAUTH | authority for a DBEFileSet |
| | | |
| | SYSTEM.SPECAUTH, CATALOG.SPECAUTH | SPECIAL authorities[REV BEG] |
| | | |
| | SYSTEM.INSTALLAUTH, | INSTALL authority[REV END] |
| | CATALOG.INSTALLAUTH | |
| | | |
| | SYSTEM.TABAUTH, CATALOG.TABAUTH | Authorities for operations on |
| | | tables and views |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Object | SYSTEM.CHECKDEF, CATALOG.CHECKDEF | Check constraint definitions |
| Definitions | | |
| | | |
| | SYSTEM.COLDEFAULT, | Defaults for each column |
| | CATALOG.COLDEFAULT | |
| | | |
| | SYSTEM.COLUMN, CATALOG.COLUMN | Columns in tables and views |
| | | |
| | SYSTEM.CONSTRAINT, | Constraints defined on tables and |
| | CATALOG.CONSTRAINT | views |
| | | |
| | SYSTEM.CONSTRAINTCOL, | Columns with a unique, |
| | CATALOG.CONSTRAINTCOL | referential,, check constraint |
| | | |
| | SYSTEM.CONSTRAINTINDEX, | Indexes in constraints |
| | CATALOG.CONSTRAINTINDEX | |
| | | |
| | SYSTEM.HASH, CATALOG.HASH | Hash structures |
| | | |
-----------------------------------------------------------------------------------------------
Table 8-1. System Catalog Views by Function (cont.)
-----------------------------------------------------------------------------------------------
| | | |
| Function | Name of View | Description |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| | SYSTEM.INDEX, CATALOG.INDEX | Indexes on tables |
| | | |
| | SYSTEM.PARAMDEFAULT, | Defaults for parameters in |
| | CATALOG.PARAMDEFAULT | procedures |
| | | |
| | SYSTEM.PARAMETER, | Parameters in procedures |
| | CATALOG.PARAMETER | |
| | | |
| | SYSTEM.PROCEDURE, | Procedures |
| | CATALOG.PROCEDURE | |
| | | |
| | SYSTEM.PROCEDUREDEF, | Procedure definitions |
| | CATALOG.PROCEDUREDEF | |
| | | |
| | SYSTEM.PROCRESULT, | Procedure results |
| | CATALOG.PROCRESULT | |
| | | |
| | SYSTEM.RULE , CATALOG.RULE | Rules |
| | | |
| | SYSTEM.RULECOLUMN , | Columns listed for a rule |
| | CATALOG.RULECOLUMN | triggered by the UPDATE statement |
| | | type |
| | | |
| | SYSTEM.RULEDEF, CATALOG.RULEDEF | Rule definitions |
| | | |
| | SYSTEM.SECTION, CATALOG.SECTION | Sections and views |
| | | |
| | SYSTEM.TABLE, CATALOG.TABLE | Tables and views |
| | | |
| | SYSTEM.VIEWDEF, CATALOG.VIEWDEF | View definitions[REV BEG] |
| | | |
| | SYSTEM.PARTITION | Partition information |
| | | |
| | SYSTEM.IMAGEKEY, CATALOG.IMAGEKEY | Master and detail dataset keys |
| | | associated with TurboIMAGE |
| | | databases attached to DBE |
| | | |
| | SYSTEM.TPINDEX, CATALOG.TPINDEX | Third-party indexes used in |
| | | TurboIMAGE database attached to |
| | | DBE[REV END] |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Storage | SYSTEM.DBEFILE | DBEFiles |
| Management | | |
| | | |
| | SYSTEM.DBEFILESET | DBEFileSets |
| | | |
| | SYSTEM.SPACEDEFAULT | Default TABLESPACE and |
| | | SECTIONSPACE DBEFileSets |
| | | |
| | SYSTEM.TEMPSPACE | TempSpace names |
| | | |
-----------------------------------------------------------------------------------------------
Table 8-1. System Catalog Views by Function (cont.)
-----------------------------------------------------------------------------------------------
| | | |
| Function | Name of View | Description |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| System Usage and | SYSTEM.ACCOUNT | I/O resource currently in use |
| Statistics | | |
| (pseudotables) | | |
| | | |
| | SYSTEM.CALL | Current DBCore calls |
| | | |
| | SYSTEM.COUNTER | Internal system counters |
| | | |
| | SYSTEM.TRANSACTION | Current transactions |
| | | |
| | SYSTEM.USER | Users currently accessing the |
| | | DBEnvironment |
| | | |
-----------------------------------------------------------------------------------------------
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
* Use ALTER TABLE to change the base system table locking. For
example, the DBA may decide to change the HPRDBSS.SECTION table
from PUBLIC to PUBLICROW to help resolve concurrency problems
associated with section validation.
* Use UPDATE STATISTICS on the base system tables to provide
up-to-date statistics for the optimizer in optimizing queries on
the system catalog views.
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.