SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-5
Design Considerations
Design Considerations
Because only a few rules apply to catalog location, many possible schemes exist for
creating catalogs. Before deciding how to create your catalog structure, consider these
performance issues for catalogs:
•
Dependent relationships in the USAGES tables of catalogs should be easily
accessible. Dispersing tables, views, and indexes among catalogs does not
improve throughput.
•
A small performance penalty is associated with the use of multiple catalogs: a one-
time penalty per process at table-open time for each catalog accessed for SQL
compilation. This penalty applies for both DDL and DML statements. Because this
penalty affects both explicit SQL compilation and automatic recompilation at run
time, the penalty might be significant.
•
The number of catalogs does not affect the collective number of rows stored in the
catalog or catalogs, with one exception: When an object described in a catalog is
related to an object described in a different catalog, the USAGES relationship is
stored in both catalogs.
Multiple-Catalog Approach
A multiple-catalog approach, in which all objects are registered in the system catalog,
has these advantages:
•
The multiple-catalog approach simplifies security considerations if different user
groups or applications have different security requirements.
•
Concurrency problems are reduced if many operations use one catalog.
•
If the catalog becomes unavailable because of a down or damaged volume, SQL
compilations that require the catalog are suspended until the catalog is available.
When SQL catalogs are unavailable, users cannot enter any DDL statement,
dynamic SQL statement, or SQLCI command that requires the catalog or that
starts an explicit SQL compilation. Also, programs cannot be automatically
recompiled; only explicitly compiled programs can run, if they do not require any
recompilations. The use of multiple catalogs reduces the effect of a single catalog
becoming unavailable.
Single-Catalog Approach
The advantages of a single-catalog approach are:
•
When using a single catalog, you do not need to unite the information in multiple
catalogs to generate a single report on catalog information. (You can, however, join
multiple catalogs by using the UNION operator to obtain a unified report across
catalogs. Because the catalog tables are identical for all catalogs, these tables are
ideal for use with the UNION operator.)