SQL/MX Database Distribution White Paper

NonStop SQL/MX Database Distribution White Paper 22 November 2004
Hewlett-Packard Company – 529627-001 Page 4 of 12
Suppose that the user wants to create the following view on \NORTH:
CREATE VIEW CATX.PERSNL.Z_VIEW AS
select ...
from catx.persnl.employee,
catz.z_schema.t1
where ...;
The view CATX.PERSNL.Z_VIEW would be visible from \NORTH, \EAST, and \WEST, as is
the underlying table CATX.PERSNL.EMPLOYEE. However, the other underlying table,
CATZ.Z_SCHEMA.T1, is visible only from \NORTH. Therefore, if the view could be created,
this query
select * from catx.persnl.z_view;
would compile and execute fine from \NORTH, but would receive an error from \EAST or
\WEST, even though the involved database object, catx.persnl.z_view, is visible from
\EAST and \WEST. Therefore, SQL/MX will reject the CREATE VIEW command with an error.
To avoid anomalies like this one, SQL/MX requires that related catalogs have identical visibility.
2. Problem Scenario: Application-Managed Replication
Many application databases have “read-only” tables. (Note: Subsequent sections of this document use the
term read-only to denote tables that are less likely to change than other tables in the same database.) When
such an application is employed in a distributed environment, it is often beneficial to replicate these read-
only tables manually to all involved Expand nodes, and then configure the application copy on each node to
access the local copy of the replicated read-only tables.
For example, suppose that a JOB table contains job codes and job descriptions, neither of which are likely
to change. You would want to treat such a table as a read-only and replicate it manually to the involved
nodes in a distributed environment.
Subsequent sections of this white paper use this JOB table example in combination with the \WEST,
\EAST, and \NORTH Expand network.
2.1. SQL/MP and Application-Managed Replication
SQL/MP tables have location-specifying Guardian names, which are always globally visible within an
Expand network. As a result, you need not explicitly register or otherwise configure parts of the SQL/MP
database for remote nodes to access it. And there are no requirements on related SQL/MP catalogs.
With SQL/MP databases, application-managed replication is easy to design and implement. The application
can use partially qualified table names and set appropriate Guardian defaults, or it can use Guardian
DEFINEs for table references (recommended). Either way, the local application can access the local read-
only tables with no code changes or recompilations, assuming similarity check is enabled.