SQL/MP Installation and Management Guide

Managing a Distributed Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
12-10
Supporting Replicated Data Through Indexes
This example creates a local index on a remote table:
>> CREATE TABLE \REMOTE.$VOL1.SALES.CUSTOMER (
+> (CUSTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
+> CUSTNAME CHARACTER (18) NO DEFAULT NOT NULL,
+> STREET CHARACTER (22) NO DEFAULT NOT NULL,
+> CITY CHARACTER (14) NO DEFAULT NOT NULL,
+> STATE CHARACTER (12) DEFAULT SYSTEM NOT NULL,
+> POSTCODE CHARACTER (10) NO DEFAULT NOT NULL,
+> CREDIT CHARACTER (2) DEFAULT "C1" NOT NULL,
+> PRIMARY KEY CUSTNUM)
+> CATALOG SALES
+> ORGANIZATION KEY SEQUENCED
--- SQL operation complete.
>> CREATE INDEX \LOCAL.$DATA1.SALES.XCUSTNAM
+> ON SALES.CUSTOMER (CUSTNAME)
+> CATALOG \LOCAL.$DATA1.SALES;
--- SQL operation complete.
This query can be satisfied by information in the local index. The query should be able
to be completed without retrieving data from the remote underlying table.
>> SELECT CUSTNAME
+> FROM \REMOTE.$VOL1.SALES.XCUSTNAM;
Supporting Replicated Data Through Indexes
SQL/MP does not specifically support replicated data except through indexes. You can
create an index on a remote node with all the columns in the table as keys in the index,
provided the index row length does not exceed the maximum length. This technique
effectively provides system support for replication.
SQL/MP supports replication through indexes as follows:
•
First, create an index on a remote node that specifies all the columns except the
primary key columns in a local table; the primary key columns are included in the
index automatically.
•
When you update the local table, SQL/MP automatically updates the index at the
remote node. Because the index contains all columns in the table, this approach is
just the same as updating a replica of the table at the remote node.
•
When you run a query at the remote node to select data from the table, SQL/MP
selects the data from the index because the index is local to the query.
•
When you run a query at the local node to select data from the table, SQL/MP
selects the data from the table because the table is local to the query.