SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Creating an SQL/MX Database
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
7-21
Additional Guidelines for Creating Tables
Example for Creating an SQL/MX Table With Multiple Hash Partitions
CREATE TABLE cat1.sch1.ordersh
(location char(16) not null not droppable,
ordernumber integer unsigned not null not droppable,
ordertime timestamp,
primary key (location, ordernumber) not droppable)
location $DATA01
hash partition(
add location $DATA02,
add location $DATA03);
Additional Guidelines for Creating Tables

When moving a table from a development environment to a production
environment, changes made to the partitioning scheme of the table from one
environment to the next can cause similarity check failures in embedded SQL
programs that query the table. These would include:

Changing the columns that compose the table’s partitioning key.

Changing from range partitioning to hash partitioning.

Changing the number of partitions in the table.
For more information, see similarity check criteria in the SQL/MX Programming
Manual for C and COBOL.

Specify table attributes that are best for the performance, access, size, and
protection of the data in the table:

Use EXTENTS, MAXEXTENTS, and ALLOCATE, if applicable, for controlling
the size of the table.

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