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

Managing an SQL/MX Distributed Database

HP NonStop SQL/MX Installation and Management Guide—544536-007

13-21

Distributing SQL/MX Database Objects

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:

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:

1. Make sure that the catalog for the table and index is registered on the remote node

\B: