SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

In a distributed database, statically-compiled queries can execute even if some of the nodes in the
database are not available, provided these two requirements are met:
• Either the node that stores the query objects’ metadata is available, or the query does not use
late name resolution of ANSI names or automatic recompilation.
• 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 configurations with respect
to their ability to minimize the consequences of node loss on surviving network nodes.
Network Outage Failure Scenario
Suppose that you have an SQL/MX database that is distributed across three nodes (\A, \B, and
\C). The database includes two user catalogs, CAT_1 and CAT_2, which were created on node
\B and have been registered for nodes \A and \C.
272 Managing an SQL/MX Distributed Database