SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—544536-007
7-22
Additional Guidelines for Creating Tables
°
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 the CLEARONPURGE attribute to control the security and the ability to
write to or drop a table. CLEARONPURGE controls the erasure of data from
the disk when an SQL/MX table or index is dropped. When you drop an object
that has the NO CLEARONPURGE attribute, the system deallocates disk
space, but does not physically destroy the data in that disk space. This
approach improves the performance of the drop statement by reducing the
number of writes to the disk, but when that disk space is allocated to a new file,
other users might be able to read data left by the object that previously used
the space. CLEARONPURGE increases security for sensitive data by causing
the system to overwrite deallocated disk space when a table or index is
dropped.
•
Create tables from OSS files that you use as OBEY command files within MXCI.
Because CREATE TABLE statements can be very long, it is easier to correct errors
in an OSS file than interactively in MXCI. Optionally, do a SHOWDDL and send the
results to an OSS file.
•
Consider creating dependent objects at the same time you create a table. To
simplify these operations, put all statements (such as CREATE TABLE, CREATE
VIEW, and CREATE INDEX) in the same OSS OBEY command file. Alternately,
you can incorporate these create statements into the CREATE SCHEMA
statement.
•
Use the LIKE option in the CREATE TABLE statement to create a table like the
existing table, source-table. This approach omits constraints (with the
exception of the NOT NULL and PRIMARY KEY NOT DROPPABLE constraints),
headings, and partitions unless include-option clauses are specified.
Optionally, create the new table with the same constraints and headings as the
source table. Alternately, override these attributes and create the new table with
different constraints and headings.
•
When you use the CREATE TABLE statement, for SQL/MX tables, note that it does
not provide the SIMILARITY CHECK clause used with SQL/MP because similarity
checking is always on for NonStop SQL/MX.
For more information about the CREATE TABLE statement, see the SQL/MX
Reference Manual.
For information about CREATE TABLE attributes for SQL/MP that are not supported by
NonStop SQL/MX, see the SQL/MX Comparison Guide for SQL/MP Users.