SQL/MX Database Distribution White Paper

NonStop SQL/MX Database Distribution White Paper 22 November 2004

Hewlett-Packard Company – 529627-001 Page 6 of 12

In addition to the “master” JOB table, replicated copies of the job table named JOB_WEST,

JOB_EAST, and JOB_NORTH, are located on each corresponding node. Application programs

use queries of the form

SELECT <column list> FROM :job_table PROTOTYPE

‘PRODCAT.PERSNL.JOB’

At run time, application programs must construct the fully qualified table name in a host variable

(:job_table in this example) based on the node on which they are running. For example, on

\EAST, the application program constructs the name PRODCAT.PERSNL.JOB_EAST in the

job_table host variable. On \NORTH, it constructs the name PRODCAT.PERSNL.JOB_NORTH.

Pros:

• All copies of the table can be visible on all the involved nodes and can, therefore, be updated

from a single, central location as needed.

• Application modules can be moved between nodes without recompiling the plans. Late name

resolution ensures that they access the correct copy.

• If the local copy is unavailable, the application can be instrumented to attempt to access one of the

remote copies.

Cons:

• The application must manage some table names programmatically. This design decision might

prevent other forms of table naming later.

Also, the PROTOTYPE feature is not standard ANSI.

• The prototype name (PRODCAT.PERSNL.JOB in the example) must be available at SQL

compile time.

• Late name resolution adds processing overhead the first time a query is executed during the life of

a process.

2.2.2. Alternative 2: Node-Specific Default Schema Names

Alternative 2 creates a read-only schema on each involved Expand node and places the local copies of read-

only tables in corresponding schemas.

To refer to the read-only tables, the application must use partially qualified names. To refer to other tables,

the application must at least use schema-qualified names. The modules must be SQL compiled on each

node, using the compile-time specification of the default catalog and schema.

One such form would be the possible future ANSI Name DEFINEs, a mechanism that would allow the

actual ANSI name to be specified at run time.