SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Example for Creating an SQL/MX Table With a Single Hash Partition
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;
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 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 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.
Creating SQL/MX Tables 87