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-20
Distributing the SQL/MX Database Objects
for objects to specified users. These access privileges extend to remote nodes
provided the users have remote network access to those nodes.
You can use the LOCATION clause in a DDL statement to specify different nodes or
disks on which to create an object.
Example—Creating Local Views on Local and Remote
Tables
1. From the local node \A, create the catalog CAT1:
CREATE CATALOG CAT1;
2. From the local node, create the schema SCH1:
CREATE SCHEMA CAT1.SCH1;
3. From the local node, register the catalog CAT1 on the remote node \B:
REGISTER CATALOG CAT1 ON \b.$data02;
4. From the local node, create the table TABLOC, which resides on the local node:
CREATE TABLE CAT1.SCH1.TABLOC
( ORDERITEM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
ORDERNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
PARTNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
UNIT_PRICE NUMERIC (8,2) UNSIGNED NO DEFAULT NOT NULL,
QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,)
STORE BY (ordernum, partnum);
5. From the local node, create the table TABREM, which resides on the remote node:
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;
6. From the local node, create the local view VIEWLOC on the local table TABLOC:
CREATE VIEW CAT1.SCH1.VIEWLOC AS
SELECT ORDERNUM, QTY_ORDERED FROM CAT1.SCH1.TABLOC;
7. From the local node, create the local view VIEWREM on the remote table
TABREM:
CREATE VIEW CAT1.SCH1.VIEWREM AS
SELECT ORDERITEM, QTY_ORDERED FROM CAT1.SCH1.TABREM;
Example—Creating a Remote Table and a Local Index
To create a remote table and a local index for that table from the local node \A: