SQL/MX Database Distribution White Paper

NonStop SQL/MX Database Distribution White Paper 22 November 2004
Hewlett-Packard Company – 529627-001 Page 8 of 12
Alternative 3 introduces an additional catalog, PRODCAT_READONLY. An individual instance
of this catalog exists on each of the three nodes. In each such instance, there is a schema PERSNL
and a table PERSNL.JOB.
In queries that read from the JOB table, application programs simply specify
PRODCAT_READONLY.PERSNL.JOB.
Pros:
• Applications need not manage table names programmatically.
• Application modules can be moved between nodes without SQL compilation. Run-time visibility
checks and late name resolution ensure that they access the correct copy of the table.
• Each node has metadata for the read-only tables, which improves SQL compile time on individual
nodes.
Cons:
• Table copies are visible only on the node where they reside. Loading or updating read-only tables
must occur on each involved node, rather than from some central location.
• The first time a query is executed during the life of a process, run-time visibility checks and late
name resolution add processing overhead. This outcome can be partially avoided if modules are
SQL compiled on each node.
• Because of the identical visibility rule (see Section 1.3.2), read-only tables cannot be used by
views, triggers, or referential integrity constraints that also reference globally visible tables.
2.2.4. Alternative 4: Replication Through Partitioning
Alternative 4 modifies the design of each read-only table to include a node name column, which is the
leftmost part of the partitioning key. Read-only tables are then partitioned on the node name column, with
one partition on each involved node and with the “first key” being the corresponding node name. Each
partition contains a copy of the business data.
Application programs must obtain the name of the local node and specify that name, in addition to other
look-up criteria, when selecting from a read-only table.
Using the previous example:
The JOB table includes a NODE_NAME column as the leftmost part of the primary key.
On all SELECTs from the JOB table, application programs specify:
... AND JOB.NODE_NAME = :local_node ...