SQL/MX Database Distribution White Paper

NonStop SQL/MX Database Distribution White Paper 22 November 2004

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

1.2. 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. None are

visible from the \WEST node. Also, you cannot distribute the database objects in catalog CATX

from \EAST to \WEST.

4. REGISTER CATALOG CATX ON \WEST.$DATA07

;

All schemas and database objects in catalog CATX are now visible from both \EAST and \WEST.

Queries like this one

select count(*) from catx.persnl.employee;

access the same database objects regardless if they are executed from \EAST or \WEST. You can

now also distribute database objects in catalog CATX to \WEST. For example:

set schema catx.persnl;

create index empname

on employee (last_name, first_name)

location \west.$data21;

Suppose that a third node, \NORTH, is added to the Expand network. None of the database objects in

catalog CATX will be accessible from \NORTH until CATX is registered on that node:

5. REGISTER CATALOG CATX ON \NORTH.$DB0001;

The volume part of the target location is not used in SQL/MX Release 2.0. However, it must be specified

and must be an existing, audited volume on the target node.