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.