SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-17
Additional Guidelines for Creating Tables
°
Use AUDIT to protect the table with TMF auditing. If neither AUDIT nor NO
AUDIT is specified, AUDIT is assigned by default.
°
Use AUDITCOMPRESS to minimize the amount of audit-trail resources
required. Use NO AUDITCOMPRESS if you need to read the complete before-
images and after-images directly from the audit trails.
°
Use BUFFERED, SERIALWRITES, and VERIFIEDWRITES to control the disk
processing of the table.
°
Use CLEARONPURGE, SECURE, and NOPURGEUNTIL to control the
security and the ability to write to or purge (drop) a table.
•
Create tables from EDIT files that you use as OBEY command files within SQLCI.
These EDIT files store the data definitions outside the data dictionary and make
the definitions available for repeatable operations, if necessary. Because CREATE
TABLE statements can be very long, it is easier to correct errors in an EDIT file
than interactively in SQLCI.
•
Use class MAP DEFINE names to identify the actual table names. The use of
DEFINEs allows mobility of the EDIT command files: you can use the same files to
create tables on different volumes and systems. For more information about
DEFINEs, see Using DEFINEs on page 10-30.
•
Consider creating dependent objects at the same time you create a table. To
simplify these operations, you can put all the statements (such as CREATE
TABLE, CREATE VIEW, CREATE CONSTRAINT, COMMENT, and CREATE
INDEX) in the same EDIT file.
•
To specify the catalog in which the table is to be registered, include the CATALOG
option in the CREATE TABLE statement if this catalog is different from the default
catalog. You can use a class CATALOG DEFINE name to identify the target
catalog. The target catalog must be an existing catalog.
•
To create a table exactly like an existing table, use the LIKE option in the CREATE
TABLE statement. The new table always contains the same column structure as
the source table. The new table is not partitioned, however, even if the source table
is partitioned, unless you use the PARTITION clause when creating the table. In
such a case, the target table has partitions even if the source table does not.
Optionally, you can create the new table with the same comments, constraints,
headings, and help text as the source table. Alternatively, you can override these
attributes and create the new table with different comments, constraints, headings,
and help text.
The new table inherits collation information from the existing table. The columns in
the new table cannot refer to different collations.
•
If you plan to use similarity checking with the table, be sure to use the CREATE
TABLE statement’s SIMILARITY CHECK ENABLE clause.