SQL/MX Database Distribution White Paper

ManualsBrandsHP ManualsServerHP NonStop G-Series

NonStop SQL/MX Database Distribution White Paper 22 November 2004

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

Title: HP NonStop™ SQL/MX Database Distribution White Paper

Author: Vilmar Olsen, NonStop SQL/MX Development

Date: 22 November 2004

Version: 2.0

Abstract: This white paper addresses some situations where common use of the Guardian default and

name-substitution mechanisms cannot be directly mapped to ANSI names.

1. A High-Level Description of SQL/MX Database Distribution

An SQL/MX database can be distributed over multiple nodes in an Expand network. However, unlike HP

NonStop SQL/MP tables and Guardian files, which are automatically accessible from all nodes in the

network, SQL/MX database objects are accessible only from those nodes where their catalog has been

explicitly registered.

When a SQL/MX catalog is created using the CREATE CATALOG command, it is known only to the

local Expand node. For other Expand nodes to access the catalog and the schemas and database objects in

it, the user must explicitly register the catalog on those Expand nodes that need access by using the

1.1. Visibility Rules

In a distributed environment, certain rules guide the use of ANSI names. Note that these rules are not

security mechanisms. ANSI authorization applies in both single-node environments and in distributed

environments, and users need remote passwords to access database objects on remote nodes.

• A catalog, and the schemas and database objects in it, can be accessed from all nodes where the

catalog is visible.

• A catalog, and its schemas and database objects, cannot be accessed from nodes where the catalog

is not visible.

• A database object can be distributed to all nodes where its catalog is visible.

• A database object cannot be distributed to nodes where its catalog is not visible.

• The ANSI name uniquely identifies a database object on all nodes where the object is visible,

regardless of the actual location and distribution of the object. As a result, database distribution is

transparent to the user, except for commands that actually manage the distribution (such as

partition-management commands).

Summary of content (12 pages)

PAGE 1
NonStop SQL/MX Database Distribution White Paper 22 November 2004 Title: HP NonStop™ SQL/MX Database Distribution White Paper Author: Vilmar Olsen, NonStop SQL/MX Development Date: 22 November 2004 Version: 2.0 Abstract: This white paper addresses some situations where common use of the Guardian default and name-substitution mechanisms cannot be directly mapped to ANSI names. 1.
PAGE 2
NonStop SQL/MX Database Distribution White Paper 1.2. 22 November 2004 Visibility Example Suppose that an Expand network has two nodes, \EAST and \WEST. On \EAST, this series of commands is executed: 1. CREATE CATALOG CATX; 2. CREATE SCHEMA CATX.SALES; CREATE SCHEMA CATX.PERSNL; ... 3. CREATE TABLE CATX.SALES.ORDERS (....); CREATE TABLE CATX.PERSNL.EMPLOYEE (...) LOCATION \EAST.$DB0045; ... All schemas and database objects in catalog CATX are visible from the \EAST node.
PAGE 3
NonStop SQL/MX Database Distribution White Paper 1.3. 22 November 2004 Nonobvious Aspects of Visibility A database object is globally visible on all nodes where its catalog has been registered. However, catalog registration is not automatic, and no global monitoring of catalog names occurs on individual Expand nodes. 1.3.1.
PAGE 4
NonStop SQL/MX Database Distribution White Paper 22 November 2004 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.
PAGE 5
NonStop SQL/MX Database Distribution White Paper 22 November 2004 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.
PAGE 6
NonStop SQL/MX Database Distribution White Paper 22 November 2004 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 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.

PAGE 7

NonStop SQL/MX Database Distribution White Paper 22 November 2004 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.

to refer to other tables. On all nodes, the -d catalog=PRODCAT option is used at SQL compile time.

PAGE 8

NonStop SQL/MX Database Distribution White Paper 22 November 2004 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.

PAGE 9

NonStop SQL/MX Database Distribution White Paper 22 November 2004 Pros: • Applications need not manage table names programmatically. • Application modules can be moved between nodes without SQL compilation. Cons: • Ad-hoc queries must filter out replicated rows within the single table. • Loading or updating read-only tables is complicated because each table now contains multiple copies of the business data. • Logical database design needs to consider replication issues.

PAGE 10

NonStop SQL/MX Database Distribution White Paper 22 November 2004 Table Naming Changes These alternatives require changes in table naming: • Alternative 1: Application-Managed Node-Specific Names • Alternative 2: Node-Specific Default Schema Names • Alternative 3: Individual Catalogs Compared to a nondistributed environment, Alternative 1 does not require any additional catalogs or schemas. The other two alternatives do require additional catalogs or schemas.

PAGE 11

NonStop SQL/MX Database Distribution White Paper 22 November 2004 Here’s another way to compare alternatives: Alternative 1: Application-Managed Node-Specific Names Alternative 2: Node-Specific Default Schema Names Alternative 3: Individual Catalogs Alternative 4: Replication Through Partitioning Apart from table names in queries, can source code changes be avoided? No Yes Yes No Can database design changes be avoided? Yes Yes Yes No Can module compilation on each node be avoided? Yes No

PAGE 12

NonStop SQL/MX Database Distribution White Paper 22 November 2004 b) A partitioned read-only table with replicated rows would be complicated to use from SQL/MX conversational interface (MXCI) and other ad-hoc queries. This issue could be overcome by using a suitable view. However, Alternative 4 is the only alternative that offers a single, globally visible ANSI name for a replicated table.