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

11 Managing an SQL/MX Distributed Database
SQL/MX databases can be distributed over disk volumes on a single system (node) or in a network
of nodes. Likewise, application programs that access NonStop SQL/MX can be distributed across
CPUs in a single node or in a network.
When managing a database distributed across volumes or nodes, you can usually use the same
SQL statements you would use with a nondistributed database. The major exceptions are statements
used only in distributed environments (REGISTER CATALOG) and statements whose syntax is
influenced by distribution (for example, the LOCATION clause in CREATE TABLE). When accessing
a distributed SQLMX object, some SQL/MX statements allow you to use distinct file names or
partition names that refer to individual partitions of the object. For most statements, however, an
object name refers to the entire object rather than to the individual partition.
The distribution issues discussed in this section are divided into the general areas of locally
distributed databases (distributed over two or more disk volumes on the same node) and
network-distributed databases.
This section addresses these topics:
• “Managing a Locally Distributed SQL/MX Database” (page 266)
• “Managing a Network-Distributed SQL/MX Database” (page 267)
• “Creating a Distributed SQL/MX Database” (page 278)
• “Altering Distributed Objects” (page 280)
• “Dropping Distributed Objects” (page 280)
• “Enhancing the Performance of a Distributed Database” (page 280)
• “Changing Network Environments” (page 281)
• “Managing Mixed Versions of NonStop SQL/MX” (page 282)
For more information about managing an SQL/MP distributed database, see the SQL/MP Installation
and Management Guide.
Managing a Locally Distributed SQL/MX Database
An SQL/MX database is locally distributed if tables or indexes are partitioned over two or more
disk volumes on one node. The goals for managing a locally distributed database are:
• Using the total available processing power of the system while balancing the workload
• Enabling very large database objects to physically spread across multiple disk volumes while
accessed as a single object
The impact of unavailable disks on an SQL/MX query of a table partitioned across multiple local
disks depends on whether the unavailable disks are needed for the query. If they are not, the query
can complete successfully. A local query can be compiled despite the unavailability of some of
the table’s partitions if both of these conditions are true:
• Either the query is dynamic or late name resolution of ANSI names or automatic recompilation
is required for a static query.
• All the accessed table’s metadata and the current system schema are available.
For example, suppose that the table PARTS is range-partitioned by part number across $VOL1 and
$VOL2, and the first key value for the partition on $VOL2 is 5000. If the $VOL2 disk is unavailable,
and a query attempts to select part number 1035, the query succeeds because it needs only to
access $VOL1.
266 Managing an SQL/MX Distributed Database