SQL/MP Installation and Management Guide

Managing a Distributed Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
12-7
Creating a Distributed Database
Remote Node Availability
Unavailable remote nodes can prevent programs that require data from those nodes
from obtaining needed data. You can, however, distribute data in an SQL/MP database
so that local data is stored locally and is available locally regardless of remote node
availability.
To ensure automatic recompilation for programs when access paths become
unavailable, you should explicitly SQL compile programs in a distributed environment
with the RECOMPILE compiler option.
Automatic recompilation can decrease performance for the amount of time required to
recompile the program or statement for the first time. When the same program is again
executed after the node has been restored to the network, the program is not
automatically recompiled but uses the original plan determined by the SQL compiler
when the program was explicitly compiled. A running process, however, does not
revert to the original query execution plan; only a newly started process would attempt
to use that plan.
Creating a Distributed Database
Objects can be distributed individually or distributed as partitions of tables or indexes.
Objects are distributed at creation by fully qualifying the names in the CREATE
statement or DEFINE. If you have the authority, you can create objects on a remote
node or create local objects that refer to remote objects.
All nodes referred to in a CREATE statement must be available to create an object.
This example creates a local shorthand view on both a local and a remote table:
>> CREATE VIEW \LOCAL.$VOL1.SALES.REPORDS
+> AS SELECT A.SALESREP, A.ORDERNUM, A.DELIV_DATE,
+> B.CUSTNUM, B.CUSTNAME
+> FROM \LOCAL.$VOL1.SALES.ORDERS A,
+> \REMOTE.$VOL4.SALES.CUSTOMER B
+> WHERE A.CUSTNUM = B.CUSTNUM
+> CATALOG \LOCAL.$VOL1.SALES;
--- SQL operation complete.
This example creates a remote table and a local index on the table. The table and
index are registered in catalogs on the nodes on which they reside.
>> CREATE TABLE \REMOTE.$VOL4.SALES.PARTS
+> (PARTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
+> PARTDESC CHARACTER (18) NO DEFAULT NOT NULL,
+> PRICE NUMERIC (8,2) NO DEFAULT NOT NULL,
+> QTY_AVAILABLE NUMERIC (7) DEFAULT SYSTEM NOT NULL,
+> PRIMARY KEY PARTNUM)
+> CATALOG \REMOTE.$VOL4.SALES
+> SECURE "NNOC";
--- SQL operation complete.
>> CREATE INDEX \LOCAL.$VOL1.SALES.XPARTDES
+> ON \REMOTE.$VOL4.SALES.PARTS (PARTDESC)