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

Managing an SQL/MX Distributed Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
13-21
Altering Distributed Objects
1. Make sure that the catalog for the table and index is registered on the remote node
\B:
REGISTER CATALOG CAT1 ON \B.$DATA02;
2. From the local node \A, create the table TABREM, which will reside on the remote
node \B:
CREATE TABLE CAT1.SCH1.TABREM
( ORDERITEM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
ORDERNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,)
LOCATION \B.$DATA02;
3. From the local node \A, create a local index for the table TABREM on the remote
node \B:
CREATE INDEX CAT1.SCH1.INDEXLOC
ON TABREM (ORDERITEM, ORDERNUM)
LOCATION \A.$DATA02;
Example—Creating a Table With Partitions on a Local and
Remote Node
To create a table with one partition on the local node \A and one partition on the remote
node \B:
1. Check that the catalog for the table and index is registered on the remote node \B:
REGISTER CATALOG CAT1 ON \B.$DATA02;
2. From the local node, create a table where the first partition, PARTLOCAL, is on the
local node and a second partition, PARTREMOTE, is on the remote node \B:
CREATE TABLE CAT1.SCH1.TABPART
(A INT NOT NULL NOT DROPPABLE,
B INT NOT NULL NOT DROPPABLE,
C INT NOT NULL NOT DROPPABLE,
PRIMARY KEY (A,B))
LOCATION $DATA11 NAME PARTLOCAL
RANGE PARTITION BY (A)
(ADD FIRST KEY (200) LOCATION \B.$DATA1 NAME PARTREMOTE);
Altering Distributed Objects
Use the ALTER TABLE and ALTER INDEX statements to alter SQL/MX tables and
indexes. If a table or index is partitioned across multiple nodes, changes caused by
ALTER can affect the attributes of partitions located on remote nodes.
You perform MODIFY operations to add, split, move, move boundaries, and drop
partitions on range-partitioned and hash-partitioned SQL/MX tables and indexes
For information about the MODIFY utility, see the SQL/MX Reference Manual.