Calculating Storage for the System Catalog

Each DBEnvironment requires space allocation for the system catalog, located in DBEFile0, which is associated with the SYSTEM DBEFileSet. The system catalog is a collection of data that describes the contents of the DBEnvironment. It contains the definitions of all database objects (tables, indexes, views, procedures, rules, constraints, stored sections, and authorization data) as well as information about the relationship between DBEFiles and DBEFileSets. Refer to the "System Catalog" chapter for details on the contents of the system catalog.

Space for the initial contents of the system catalog is allocated in the DBEFILE0 DBEFILE clause of the START DBE NEW statement. Refer to the ALLBASE/SQL Reference Manual for further information on the START DBE NEW statement. When a DBEnvironment is configured, ALLBASE/SQL automatically creates one DBEFileSet called SYSTEM and one DBEFile with the default file name of DBEFile0. ALLBASE/SQL associates DBEFile0 with the SYSTEM DBEFileSet and stores in it the initial system catalog data.

The minimum and default size of DBEFile0 is 150 pages, which is sufficient space for the initial system catalog information plus 20% overhead for expansion. You can use the default name and size, or you can specify a name and size (up to 16,777,215 pages) during configuration.

The following START DBE NEW statement for the PartsDBE sample DBEnvironment creates a 150 page DBEFile0 named PartsDBE0 and an MPE/iX system file name of PartsF0:

   isql=> START DBE 'PartsDBE.SomeGrp.SomeAcct' NEW

   > DBEFILE0 DBEFILE PartsDBE0

   > WITH PAGES = 150,

   > NAME = 'PartsF0';

As the system catalog becomes larger, you can add DBEFiles to the SYSTEM DBEFileSet as needed. Alternatively, you can anticipate the growth and configure the DBEnvironment with a DBEFile0 large enough to accommodate the potential system catalog growth. Conservatively, you can figure that approximately two DBEFile pages are needed to store catalog information for every 33 user tables and every 37 indexes. Add a 20% page overhead for temporary storage during query processing and expansion.

As objects are added to the DBEnvironment, the DBA can monitor the system catalog to determine if a new DBEFile must be added to the SYSTEM DBEFileSet. Refer to the "Maintenance" chapter for more details on how to determine when to add a DBEFile to the SYSTEM DBEFileSet.

The following factors affect system catalog size:

SQL statements that create ALLBASE/SQL objects and grant authorities.
The number of sections stored in the system catalog from preprocessed SQL statements.
The number of columns in all tables and views in the DBEnvironment.
Query operations that require sorting.

Storage of Definitions for Newly Created Objects

SQL statements that create objects or alter existing objects will add or update rows in the system catalog each time they are processed. The following statements add rows to the system catalog:

   ADD TO GROUP         ALTER TABLE          CREATE DBEFILE        

   CREATE DBEFILESET    CREATE GROUP         CREATE INDEX          

   CREATE PROCEDURE     CREATE RULE          CREATE TABLE         

   CREATE VIEW          DECLARE CURSOR       GRANT                

   PREPARE

Sections are removed when any DROP statement is executed.

Number of Columns in Tables

The number of columns in the DBEnvironment affects the system catalog size because rows are stored in the system catalog for each column in each table in the DBEnvironment. Therefore, as columns are added to tables and new tables are created, the system catalog size increases.

Definitions of Rules, Procedures, Constraints, and Views

The system catalog contains a definition string for each rule, procedure, constraint, and view you create. These definitions are stored as character data, and they add additional overhead to the system catalog.

For procedures, sections and static information are also stored in the system catalog in a base table that is not exposed to the user.

Storage of Sections

Sections can be stored in the system catalog by SQL statements when they are preprocessed as part of an application program. They can also be stored interactively via ISQL.

It is difficult to put an exact number on how many DBEFile pages are needed for a given number of sections since more complex statements use more space. For example, a preprocessed SELECT statement that describes a three-table join stores a larger section than a SELECT on one table.

Refer to the ALLBASE/SQL Reference Manual chapter "Constraints, Procedures, and Rules" for information about sections created for procedures. Refer to the appropriate ALLBASE/SQL Application Programming Guide for more information on which SQL statements in an application cause sections to be stored.

Calculating Space Needed for Sections

For each section in the SYSTEM.SECTION view, ALLBASE/SQL stores at least one input tree and, conditionally, one run tree. A tree may take one or more rows depending on the SQL statement. A row is 504 bytes. SQL statements containing several column names or host variables may generate multiple-row input trees. Views only store input trees. Complex SELECT statements such as joins and queries using the GROUP BY or HAVING clauses tend to generate multiple-row run trees.

To calculate the number of pages needed to store a module, you must calculate the number of non-query SQL statements (N1) and the number of queries (N2) in the module. The formula shown below calculates the approximate number of bytes required to store sections. Only space for sections belonging to modules is calculated.

Tree Bytes = (N1 * 504 + N2 * 504 * 1.2) * 2

The above formula assumes that 20% of the queries are complex. You can alter the formula to fit a specific module by changing 1.2 to the appropriate ratio. If 50% of the queries are complex, use 1.5 instead of 1.2 to calculate tree bytes for that module.

Assume a program stores eighteen sections during preprocessing and three of those sections are queries. N2 is three and N1 is fifteen. The number of bytes needed for the module is:

Tree Bytes = (15 * 504 + 3 * 504 * 1.2) * 2 = 18,748.4

Rounded up, the value is 18,749. A DBEFile page can contain eight 504-byte rows. To translate the 18,749 bytes into pages use the following calculation:

Rounding up, the result is 38 rows. Then,

Rounding up, the result is 5 pages. The result is always rounded up because DBEFiles cannot be created with partial pages. These formulas help you determine how many DBEFile pages should be available in the SYSTEM DBEFileSet for preprocessed applications. Keep in mind the other factors that affect space in the SYSTEM DBEFileSet such as the number of objects in the DBEnvironment and the DBEFile pages needed to perform CREATE INDEX, ORDER BY, GROUP BY, HAVING, and DISTINCT operations. Refer to the "Physical Design" chapter for more information.

Remember to UPDATE STATISTICS and COMMIT WORK after an application is preprocessed to get an accurate reading of how many DBEFile pages are left in the SYSTEM DBEFileSet.

Monitoring System Catalog Size

As objects are added to the DBEnvironment, the DBA can monitor the system catalog to determine if a new DBEFile must be added to the SYSTEM DBEFileSet. Refer to the "Maintenance" chapter for details on how to determine when to add a DBEFile to the SYSTEM DBEFileSet.

Although user-defined tables can be created in the SYSTEM DBEFileSet, it is recommended that you create new DBEFileSets to keep user data separate from system catalog data to facilitate maintenance and performance tuning. Refer to "Arranging Tables and Indexes in DBEFileSets," earlier in this chapter.