HP 3000 Manuals HP 3000 Manuals
System Catalog
The system catalog in ALLBASE/SQL is a database of runtime code and
system information used by SQLCore to carry out internal operations.
Like other databases, the system catalog is a set of tables. The base
tables underlying SYSTEM views are owned by special user HPRDBSS, and
they are located in the SYSTEM DBEFileSet.
The runtime code in the system catalog consists of stored sections for
application programs, procedures, and views, together with validity
information and authorization data. At run time, code is fetched from
the system catalog and stored in the user's memory heap. You can examine
the informational part of the system catalog by doing queries on a set of
views owned by SYSTEM or CATALOG.
The system catalog is accessed in two ways: by user queries and by
internal access. When you perform a query on the system catalog, locks
are obtained and released just as in any other query in your
transactions, and subject to the same isolation levels. However, when
ALLBASE/SQL accesses the system catalog internally on your behalf, it
uses the Repeatable Read (RR) isolation level. For example, if you issue
the query
SELECT * FROM PurchDB.Parts
at the RU isolation level, no locks are obtained by your transaction on
the Parts table. Internally, ALLBASE/SQL acquires share locks at the RR
isolation level on several system tables as it performs the query. Even
though you may have selected RU, ALLBASE/SQL still reads the system
catalog on your behalf at the RR level.
NOTE The locking of system catalog resources is different for dynamic
statements than it is for statements in preprocessed applications.
Consider the following query:
SELECT * FROM SYSTEM.TABLE
If you issue this query in a preprocessed application at the RU
isolation level, your transaction does not obtain any locks on the
SYSTEM.TABLE view or the base table HPRDBSS.TABLE at run time,
provided the section that incorporates the query is valid. In a
dynamic statement (including a query issued within ISQL),
ALLBASE/SQL has to read HPRDBSS.TABLE to obtain information about
SYSTEM.TABLE, so it therefore applies share locks on your
transaction's behalf.
For more information about the locks that are applied on system
catalog resources, refer to the appendix "Locks Held on the System
Catalog by SQL Statements," in the ALLBASE/SQL Database
Administration Guide.
What effect does internal locking of the system catalog have on
performance? If a transaction is doing data definition, it obtains
exclusive locks on system tables. This prevents other system access from
taking place until the data definition transaction is finished. You can
prevent data definition from taking place and thereby prevent lock waits
and deadlocks on the system catalog by disabling data definition. You do
this by using the SQLUtil ALTDBE command to set the DDL Enabled flag to
NO in the DBECon file.
NOTE Setting the DDL Enabled flag to NO does not disable section
validation, which obtains exclusive locks on the system catalog.
Directory Caching
When DDL is disabled (DDL Enabled set to NO), certain system catalog
information is cached in shared memory where it is available for quick
access.