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

Adding, Altering, and Dropping SQL/MX Database
Objects
HP NonStop SQL/MX Installation and Management Guide—544536-007
9-13
Adding Schemas
Example of Adding a Hash Partition to an SQL/MX 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-10.
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
name must match the subvolume name indicated in the system schema column
SCHEMA_SUBVOLUME, described in the system schema table SCHEMATA.
NonStop SQL/MX does not prevent you from specifying a subvolume name that is
already in use by another schema. However, HP strongly recommends that each
schema use a different subvolume. This avoids problems when RDF and other
features are needed.