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

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—523723-004
9-13
Adding Schemas
the SQL/MX Programming Manual for C and COBOL and the SQL/MX
Programming Manual for Java.
8. Make new TMF online dumps of all partitions of the table or index.
For information about redistributing rows across partitions, see Section 10,
Reorganizing SQL/MX Tables and Maintaining Data.
Example of Adding a Hash Partition to a Table
For this example, suppose that the table ORDERSH has been created in this way:
>> CREATE TABLE cat3.sch3.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);
--- SQL operation complete.
The table ORDERSH is partitioned three ways. When business orders increase, you
decide to rebalance the orders data over four disks. Use this MODIFY TABLE
command to add the fourth partition:
>> MODIFY TABLE cat3.sch3.ordersh add partition
+> location $DATA04;
--- SQL operation complete.
In response to this command, a portion of the data in each of the original three
partitions is moved automatically to the fourth partition, rebalancing the data evenly
across the four partitions.
For more detailed examples of adding, modifying, and deleting range-partitioned and
hash-partitioned tables and indexes, see Using MODIFY to Manage Table and Index
Partitions on page 10-9.
Adding Schemas
To add a schema to a catalog, use the CREATE SCHEMA statement. If you specify an
optional subvolume name in the CREATE SCHEMA statement, follow these
guidelines:
•
The name must begin with the letters ZSD, followed by a letter, not a digit (for
example, ZSDa, not ZSD2).
•
The name must be exactly eight characters long.
•
All Guardian files representing data in a particular schema must have the same
subvolume name regardless of the volume on which they reside. This subvolume