SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-6
Performance Considerations
•
Administrative control is easier, and perhaps safer, with fewer catalogs. If
restricting the authority for users to create SQL objects is important, controlling
fewer security strings is easier.
If you are using the $SYSTEM default location for the system catalog, you might want
to limit use of the catalog to minimize SQL/MP disk accesses on $SYSTEM during
SQL compilations. In this situation, you would not use the system catalog as a general-
purpose catalog for applications, but only as a catalog directory, and you would have
one or more other catalogs for your applications.
Performance Considerations
The disk process cache setting for a disk that contains an SQL catalog affects the
performance of SQL DDL statements. The system administrator should set the disk
process cache to an appropriate value by using the PUP SETCACHE command (D-
series only) and the SCF ALTER DISK, CACHE command (G-series only). This action
is especially useful for catalogs that store information about tables with many
partitions. The performance of DDL statements such as CREATE TABLE, ALTER
TABLE ADD PARTITION, and DROP TABLE can be greatly enhanced with an effective
cache setting.
For example, a table with 200 partitions, all described in a single catalog, has 40,000
rows in the PARTNS catalog table and in the IXPART01 index on the PARTNS catalog
table. Creating such a table causes more than 80,000 writes to the catalog. Using the
default cache value can cause this operation to take up to 25 times longer than if you
set disk cache to 4 MB.
For more information about cache memory, see Managing Cache Memory Size on
page 14-17. For information about PUP, see the Peripheral Utility Program (PUP)
Reference Manual (D-series only). For more information about SCF, see the SCF
Reference Manual for G-Series RVUs.
Creating a Catalog
This example creates a catalog on $VOL1.SALES, the current default volume and
subvolume:
>> CREATE CATALOG;
--- SQL operation complete.
You can use the SQLCI ENV command to list information about the current
environment.
When creating catalogs in SQLCI, you must be aware of the SQLCI session
environment. When you have not specified a current catalog in a session and you do
Note. The recommendation for the number of catalogs is to create the smallest number of
catalogs you need for your business operations. Typically, you should establish catalog
boundaries along the lines of application and user security requirements. Associate catalogs
with sets of tables that are logically associated or that are used together.