SQL/MP Installation and Management Guide

Managing a Distributed Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
12-8
Altering Distributed Objects
+> CATALOG \LOCAL.$VOL1.SALES;
--- SQL operation complete.
This example creates a partitioned table with partitions on both a local node and a
remote node:
>> CREATE TABLE \LOCAL.$VOL1.INVENT.PARTLOC
+> (LOC_CODE CHARACTER (3) NO DEFAULT NOT NULL,
+> PARTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
+> QTY_ON_HAND NUMERIC (7) NO DEFAULT NOT NULL,
+> PRIMARY KEY (LOC_CODE, PARTNUM ))
+> CATALOG \LOCAL.$VOL1.INVENT
+> ORGANIZATION KEY SEQUENCED
+> PARTITION (\REMOTE1.$VOL2.INVENT.PARTLOC
+> CATALOG \REMOTE1.$VOL2.INVENT
+> FIRST KEY "G00",
+> \REMOTE2.$VOL3.INVENT.PARTLOC
+> CATALOG \REMOTE2.$VOL3.INVENT
+> FIRST KEY "P00")
+> SECURE "NNOO";
--- SQL operation complete.
When creating a table on a remote system, the local system default multibyte character
set is used.
Altering Distributed Objects
You can perform alter operations on distributed databases, as described under Altering
Database Objects on page 7-13.
The ALTER statement allows these operations to be performed on partitions
independently of the other partitions: you can allocate or deallocate extents or specify
a different MAXEXTENTS value for each partition. To alter these attributes, use the
PARTONLY option of the ALTER statement. PARTONLY applies to tables or indexes.
Alterations that do not allow the PARTONLY option affect the entire table or index (all
distributed partitions of a table or index).
This example demonstrates altering the maximum extents. In the example,
$VOL1.SALES.ORDERS is a secondary partition of a partitioned table.
>> ALTER TABLE $VOL1.SALES.ORDERS PARTONLY MAXEXTENTS 124;
--- SQL operation complete.
You can also add, split, or drop partitions of tables or indexes, as explained in
Section 7, Adding, Altering, Removing, and Renaming Database Objects.
Note that ALTER operations are subject to versioning requirements. For example, you
cannot use the WITH SHARED ACCESS option with a split, merge, or move boundary
request unless each source object and each target object reside on a node running
version 315 or later of SQL/MP software. You can only perform DML or DDL operations
on tables with extended partition arrays from nodes running version 320 or later of
SQL/MP software.