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-23
Supporting Replicated Data Through Indexes
Supporting Replicated Data Through Indexes
Use indexes to replicate data. NonStop SQL/MX also supports the use of triggers to
replicate data from one table to another and the development of publish/subscribe
applications that replicate data.
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 and
index key length does not exceed 255 bytes. This approach effectively provides
system support for data replication.
NonStop SQL/MX supports data replication through indexes as follows:
•
Create an index on a remote node that specifies all the desired columns except the
clustering key columns in a local table. The clustering key columns are included in
the index automatically.
•
When you update the local table, NonStop SQL/MX 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.
Using Remote Servers
When you use a network-distributed database, you can often control whether remote
data is updated directly by a local server or indirectly by a remote server. Local
programs can update or retrieve remote data directly through NonStop SQL/MX.
NonStop SQL/MX uses the remote I/O features of the Guardian file system and disk
process.
Alternately, when you need to update data stored at a remote node, you can send a
message containing an update request to a server at that remote node. Ultimately, this
issue might be one of performance and processing power distribution across nodes.
One of the main advantages of using a remote server for distributed processing is to
reduce the amount of data sent across communication lines. One message makes the
request of the server at the remote node. Then that server manages all access to, and
updating of, the remote data. This approach reduces message traffic on slower
communication lines and increases performance.
Changing Network Environments
Network environments are often subject to change. Nodes can be added or removed,
system configurations at each node can change, NonStop operating systems can be
upgraded independently at each node, communication line speeds or types can
Note. To make sure that the SQL/MX compiler selects the desired access path, use the
SHOWSHAPE command and then, if necessary, force the compiler to select the desired path
with CONTROL QUERY SHAPE. For information about the SHOWSHAPE command and the
CONTROL QUERY SHAPE statement, see the SQL/MX Reference Manual.