SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Using Co-located Indexes in a Distributed Database
Co-locating indexes with their parent table is an effective way of using NonStop SQL/MX to
improve performance and allow for better data distribution, both in a standalone and distributed
environment.
For example, assume the EMPLOYEES table contains an EMPLNAME and EMPLCITY column, and
each employee resides in San Francisco, Chicago, or New York City. The table is partitioned on
the EMPLCITY column and each partition resides on a different node (for example, \SF, \CHI, and
\NYC). In addition, the EMPLOYEES table has an index on the EMPLNAME column. This index
can be partitioned on the EMPLCITY column even though the indexing column is on the EMPLNAME
column. This way, all the San Francisco employees are stored on the \SF node and can be retrieved
efficiently by EMPLNAME.
For more information about co-locating an index on the same disk volume as its underlying table,
see “Using Co-located Indexes” (page 305).
Using Remote Application Processes
When you use a network-distributed database, you often control whether remote data is updated
directly by a local application process or indirectly by an application process running on a remote
note. 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 on a remote node, you can send a message
containing an update request to an application on that remote node. Ultimately, this issue might
be one of performance and processing power distribution across nodes.
A key advantages of using an application process on a remote node for distributed processing is
to reduce the amount of data sent across communication lines. One message makes the request
of the remote application process. Then that application process 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 change, and the needs of
the node with respect to the database or application can change.
Many of these changes do not affect the SQL/MX database or environment and need not concern
you if you are a system manager. Certain changes, however, can cause problems or affect the
SQL/MX environment and should be anticipated.
Consider these changes on a case-by-case basis:
• A new node is added to the network. This addition does not affect the existing database
scheme. To access this node and incorporate it into the overall environment, however, network
passwords and security must be added to all other nodes. Catalogs on existing nodes must
be registered on the new node to make the catalogs and their underlying objects visible to
the node. After NonStop SQL/MX is initialized on this node, register the catalogs from other
nodes on the new node, and then you can place new objects that you create in those catalogs
on this new node.
• An existing node is permanently removed from the network. Move all object data on this node
to other nodes before dropping the objects and removing the node. If the objects are table
or index partitions, use the MODIFY utility to move the data to another node before dropping
the objects and removing the node.
• A node needs to be renamed. Changing a node name can be difficult or impossible to do
because the node name is embedded in the file labels and stored in the metadata.
Changing Network Environments 281