SQL/MP Installation and Management Guide

Managing a Distributed Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
12-6
Maintaining Local Autonomy in a Network
•
If a query uses Index A as the access path but \NODE3 is down, the query is
recompiled to attempt to access the data by using Index B or by using the primary
key residing on \NODE1.
•
If an INSERT statement tries to insert a row into Table X with values for all the
columns, the insert fails if \NODE3 is down, because Index A cannot be updated.
•
If an UPDATE statement tries to update Columns C1 and C2 of Table X, the update
completes although \NODE3 is unavailable, because Index A on \NODE3 is not
required for the update.
Local Autonomy and DDL Operations
Local autonomy does not usually apply to DDL operations, which usually require the
availability of all dependent objects affected by the operation. For example, the
CREATE INDEX statement requires that all partitions and protection views of an
underlying table be available. Certain DDL statements, however, such as ALTER
TABLE PARTONLY, can be performed successfully on the partition involved.
Local Autonomy and SQL Compilations
Autonomy is also not completely supported at SQL compile time. A program is
compiled with the best query execution plan only if all the local and remote catalogs of
the tables, views, and associated indexes are available. If all the required information
is available and the compilation is successful, the program is entered in the
PROGRAMS and USAGES tables and marked valid in the catalog and in the program
file label.
SQL compilations that occur when nodes are unavailable can still register the compiled
programs in the PROGRAMS and USAGES catalog tables and mark the programs as
valid if the FORCE option was chosen. Those statements that could not be compiled
with the best query execution plan are marked invalid on a statement-by-statement
basis. The invalid statements will be automatically recompiled at run time.
You should SQL compile programs used in a network only when all referenced nodes
are available so that the compiler can create the best query execution plan. You should
also use the RECOMPILE option of the SQL compiler so that the automatic
recompilation can occur at run time if access paths become unavailable.
For more information about the access strategies of programs, see Section 10,
Managing Database Applications.
Network Availability and Use
Network lines have a direct impact on the performance of a distributed database. The
line speed, network routing, and network message traffic use significantly affect
response time. The setup and management of a network should be thoroughly studied
by the system or network manager. For more information, see the Communications
Management Interface (CMI) Operator Reference Manual.