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-13
Maintaining Local Autonomy in a Network
•
The user data that the query attempts to access is stored on available disks on the
local node or some other available nodes.
When a query on a range-partitioned table uses the partitioning key to limit the table
scan to one row or a range of rows that can be located in a subset of the partitions,
only those necessary partitions are accessed.
For example, the table ORDERS is range-partitioned by the column ORDER
NUMBER. It contains four partitions, with begin key values of 0, 100, 200, and 300
respectively. Suppose that a query has selection predicates to select rows with
ORDER NUMBER in the range of 150 through 250. If the nodes and disks which store
the second and third partitions are available and the query has been statically compiled
and does not need late name resolution of an ANSI name or automatic recompilation,
the query can complete successfully even if the first and fourth partitions are
unavailable.
Network Availability and Use
Network lines have a direct impact on the performance of a distributed database. Line
speed, network routing, and network message traffic significantly affect response time.
The setup and management of the network should be thoroughly studied by the
system or network manager.
Remote Node Availability
Unavailable remote nodes can prevent programs that require data from those nodes
from obtaining the data. You can, however, distribute data in an SQL/MX database so
that data required by a process is stored on the node on which the process executes.
However, as previously stated, if the query objects’ metadata is located on a remote
node, the query to this local data must be static and not dynamic. Furthermore, the
query must not use automatic recompilation or late name resolution of an ANSI name.
By contrast, DDL operations must be able to access and update metadata.
For NonStop SQL/MX, automatic recompilation is enabled by default. The compiler
does not have a NO RECOMPILE option, but its CQD
AUTOMATIC_RECOMPILATION option can be turned off.
Automatic recompilation can decrease performance during the time required to
recompile the program or statement for the first time. When the same program is again
executed after the node has been restored to the network, the program is not
automatically recompiled but uses the original plan determined by the SQL compiler
when the program was explicitly compiled. A running process, however, does not
revert to the original query execution plan. Only a newly started process would attempt
to use that plan.
Reconfiguring a Network to Improve Local Autonomy
Usually, the loss of a network node is due to a planned outage scheduled to perform
software or hardware upgrades. This subsection examines several possible network