SQL/MP Installation and Management Guide

Managing a Distributed Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
12-5
Maintaining Local Autonomy in a Network
Maintaining Local Autonomy in a Network
In the context of a network distributed database, local autonomy ensures that a user
can access local data regardless of the availability of remote dependent objects. For
example, if a table is partitioned with a portion on \SYS1 and another portion on \SYS2,
a local user of \SYS1 can access the local partition of the table when \SYS2 is not
available. This access is useful, of course, only if the needed rows reside in the
partition on \SYS1.
Each partition of a distributed table or index is described in a catalog on each local
node. This duplication of description allows for local autonomy. Access to the primary
partition of a distributed table or index is not required to access any other partition. For
index-only scans, however, the partition of the base table that corresponds to the
requested data range must be accessible.
In a distributed application, you can maximize local autonomy by referring to a local
partition as a table name in local programs.
When the program refers to a local partition, the SQL compiler checks for information
about the table in a local catalog. When a program refers to a remote partition, the
SQL compiler must check for information about the table in the remote catalog. If the
remote node is down, SQL compilation fails. When the local node is up and the data is
available locally, the local SQL compilation can succeed.
By using DEFINEs and a program to refer to tables, and by associating the DEFINEs
with local partitions, you increase the possibilities for successful SQL compilations for
programs that use distributed data. This advantage applies to explicit SQL
compilations, automatic SQL recompilations, and dynamic SQL statement
compilations.
Local Autonomy and DML Operations
Local autonomy applies to run time DML access. When a node required for an access
path is detected as unavailable at run time, the DML statement can be SQL recompiled
to find another access path using the available nodes. If there is another access path,
the statement is executed.
An INSERT, UPDATE, or DELETE statement cannot complete if the statement tries to
write or delete a row in an unavailable table, index, or partition of a table or index.
For example, a table and two indexes are located on two different nodes:
•
Table X (Columns A1, B1, C1, C2, C3, C4) resides on \NODE1
•
Index A, using Columns A1 and B1, resides on \NODE3
•
Index B, using Columns A1 and C4, resides on \NODE1
Using this table and these indexes, these scenarios illustrate some of the features and
restrictions of local autonomy for DML operations: