SQL/MX Database Distribution White Paper

NonStop SQL/MX Database Distribution White Paper 22 November 2004
Hewlett-Packard Company – 529627-001 Page 5 of 12
For example, replicas of the JOB table can exist on each of the involved nodes with individual Guardian
file names like \WEST.$DATA01.PERSNL.JOB, \EAST.$DB0007.PERSNL.JOB and
\NORTH.$PROD57.PERSNL.JOB. A Guardian DEFINE named =JOB can be used to access the table.
Setting the DEFINE to point to the local JOB table replica ensures that the application accesses that replica.
Infrequent loads or updates of manually replicated, read-only SQL/MP tables are also fairly easy to set up
because of the global visibility of Guardian names. The application can load each replica in turn by using
sufficiently qualified Guardian names (or DEFINEs).
2.2. SQL/MX and Application-Managed Replication
With SQL/MX, application-managed replication is more complicated because of visibility rules and
because the ANSI name used to reference the object is independent of the object location. Partially
qualified names in themselves cannot specify an individual copy of a table, and Guardian DEFINEs do not
support ANSI names. For example:
Catalog PRODCAT is registered on \EAST, \WEST, and \NORTH. All tables, including the JOB
table, are in schema PERSNL.
However, the ANSI name PRODCAT.PERSNL.JOB is not related to the location of the table, and
only one table can have that name. An application running on \WEST, \EAST, or \NORTH
accesses that single table, whether it uses the fully qualified ANSI name, or a partially qualified
name, with default catalog and schema specifications.
As you can see, straightforward ANSI naming does not easily support manual replication of SQL/MX
tables. In a future release, SQL/MX might introduce logical ANSI Name DEFINEs with functionality
similar to Guardian DEFINES: run-time specification of target tables, external to the application.
What follows are alternative database naming or design strategies that allow individual nodes to hold local
copies of read-only SQL/MX tables and allow applications on these nodes to access the local copy of such
tables. None of these alternatives require source code changes to copy an executable from one node to be
run on another.
2.2.1. Alternative 1: Application-Managed Node-Specific Names
Alternative 1 exploits the use of the PROTOTYPE feature of SQL/MX. For a description of the
PROTOTYPE feature, see the SQL/MX Programming Manual for C and COBOL or Support Note S04098,
“USING SQL/MX NAMES WITH NO GUARDIAN DEFINE SUPPORT.”
For each involved Expand node, create one read-only table with an ANSI name that associates it with the
node. You can use any part of the ANSI name.
The application can use prototyped queries to access these read-only tables and construct the actual table
name at run time based on the local node name. Alternatively, table names can be provided as input
parameters to application programs in Pathway configurations or through application configuration files.
Using the previous example: