Examining the System Catalog

Whenever you create objects in ALLBASE/SQL, their characteristics are stored in the system catalog, which is a special system-created database. The system catalog is like a listing of the contents of a DBEnvironment. (See the illustration below.)

You can look at the system catalog by performing queries on system tables. Issue the following query to see entries for the tables and views you have just created:

   isql=> SELECT * FROM SYSTEM.TABLE Return

   > WHERE OWNER = USER; Return

The next figure shows the first display you see.

Figure 3-2 System.Table Display



   select * from system.table where owner = USER;

   --------------------+--------------------+--------------------+------+-----

   NAME                |OWNER               |DBEFILESET          |TYPE  |RTYPE

   --------------------+--------------------+--------------------+------+-----

   ALBUMS              |PETERW              |ALBUMFS             |     0|

   SELECTIONS          |PETERW              |SYSTEM              |     1|

   TITLES              |PETERW              |ALBUMFS             |     0|

























   ---------------------------------------------------------------------------

   Number of rows selected is 3

   U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e



   isql=>

Enter "r" to scroll right to see all the columns. A complete description of each column appears in chapter 9 of the ALLBASE/SQL Database Administration Guide. For now, notice the value in the TYPE column. A zero (0) indicates a table, a one (1) indicates a view.

Updating Statistics in the System Catalog

Use the UPDATE STATISTICS statement for each table as follows:

   isql=> UPDATE STATISTICS FOR TABLE ALBUMS;

   isql=> UPDATE STATISTICS FOR TABLE TITLES;

This ensures that the current number of rows in each table and other statistical information are updated in the system catalog. The statistical information is used by SQLCore when it decides whether to use an index or not. Statistics also provide information about ongoing processes within the DBEnvironment.

Examining the System Catalog

Technical documentation

» Table of Contents

» Index

Updating Statistics in the System Catalog