HP 3000 Manuals

ALLBASE/SQL Database Administration Guide

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.


            

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:


            

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.

MPE/iX 5.5 Documentation