SQL/MX Database Distribution White Paper

NonStop SQL/MX Database Distribution White Paper 22 November 2004
Hewlett-Packard Company – 529627-001 Page 7 of 12
Using the previous example:
Alternative 2 introduces three additional schemas, PERSNL_WEST, PERSNL_EAST, and
PERSNL_NORTH, each containing a copy of the JOB table. Application programs use the name
JOB to refer to the JOB table and use the names PERSNL.<table> to refer to other tables.
On all nodes, the -d catalog=PRODCAT option is used at SQL compile time. Additionally, these
options are used on individual nodes:
• On \WEST, use -d schema=PERSNL_WEST.
• On \EAST, use -d schema=PERSNL_EAST.
• On \NORTH, use -d schema=PERSNL_NORTH.
As a result, the unqualified reference to JOB will resolve to PRODCAT.PERSNL_WEST.JOB on
\WEST and to PRODCAT.PERSNL_NORTH.JOB on \NORTH.
Pros:
• Applications need not manage table names programmatically.
• All copies of the table can be visible on all the nodes involved and can, therefore, be updated
from a single central location as needed.
Cons:
• There can be only one default schema for a session or module compilation. Applications that, for
security reasons, require multiple schemas owned by different user IDs cannot easily use this
strategy.
• Application modules need SQL compilation on all nodes.
• The applications must be developed using a mix of fully qualified and partially qualified ANSI
names. This design decision might prevent other forms of table naming later, such as the possible
future ANSI Name DEFINEs.
2.2.3. Alternative 3: Individual Catalogs
Alternative 3 places all read-only tables in a separately named catalog. On each of the involved nodes, you
create a catalog with that name and with the required schemas and database objects in it. These catalogs are
identically named individual catalogs (see Section 1.3.1) and are, therefore, not globally visible. The
remaining objects in the database reside in a single or in multiple distributed catalogs.
The application then references the read-only tables by their fully qualified ANSI names, including the
separate catalog name.
Using the previous example: