SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-134
Considerations—CREATE CATALOG
Considerations—CREATE CATALOG

CREATE CATALOG requires authority to write to the SQL.CATALOGS table,
because SQL adds an entry to that table for the new catalog.
The owner of the new catalog is the user whose process created the catalog.
However, the operations allowed on the tables and indexes that make up the
catalog itself (as described under Catalogs on page C-8) are more limited than
those allowed on ordinary tables and indexes, even for the owner. You can delete
catalog tables only with DROP CATALOG (not even with CLEANUP unless you
specify CLEANUP *,CATALOG;), you cannot divide catalog tables, and you cannot
alter file attributes of catalog tables except for those related to security.
Secure catalogs so that other users who need to access them have appropriate
authority. Users who require write access must have read access as well.
Programs that use objects described in a catalog must have write access to the
TRANSIDS and USAGES tables. For programs to be registered in a catalog, the
SQL-compiling process must have write access to the PROGRAMS, TRANSIDS,
and USAGES tables.
Catalogs can be resecured with ALTER CATALOG. The PROGRAMS, TRANSIDS,
and USAGES tables (but not other catalog tables) can be individually resecured
with ALTER TABLE.
Only one DDL statement can operate on a given SQL object (or partition of an SQL
object) at a time. An error occurs if you attempt to execute a CREATE CATALOG
statement while another process is executing a DDL operation on the same object.
The specific error depends on the DDL operation involved and the phase of the
operation at which the conflict occurs. (For information, see DDL (Data Definition
Language) Statements on page D-20.)

For better performance when several SQL catalogs are on the same disk volume,
the system administrator should set the disk process cache to an appropriate value
with the Peripheral Utility Program (PUP) SETCACHE command. This strategy is
especially important for 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 the operation to take up to 25
times longer than if you set disk cache to 4 MB.
For more information about managing cache, see the SQL/MP Installation and
Management Guide. For information about PUP for D-series systems, see the
Peripheral Utility Program (PUP) Reference Manual.
If SMF is installed on your node, there are two ways to place the set of catalog
tables on a single physical volume: