SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Example—Unregistering a Catalog From a Node
The UNREGISTER CATALOG command causes an empty catalog reference to be removed from
the specified node. As a result, the catalog is no longer visible on that node.
UNREGISTER CATALOG samdbcat FROM \nodey.$data47;
Distributing SQL/MX Database Objects
SQL/MX objects can be distributed individually or distributed as partitions of tables or indexes.
Objects can be distributed at creation time by the CREATE statement or at a later time by using
the MODIFY utility. You use the GRANT command to assign access privileges for objects to specified
users. These access privileges extend to remote nodes provided the users have remote network
access to those nodes.
You can use the LOCATION clause in a DDL statement to specify different nodes or disks on which
to create an object.
Example—Creating Local Views on Local and Remote Tables
1. From the local node \A, create the catalog CAT1:
CREATE CATALOG CAT1;
2. From the local node, create the schema SCH1:
CREATE SCHEMA CAT1.SCH1;
3. From the local node, register the catalog CAT1 on the remote node \B:
REGISTER CATALOG CAT1 ON \b.$data02;
4. From the local node, create the table TABLOC, which resides on the local node:
CREATE TABLE CAT1.SCH1.TABLOC
( ORDERITEM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
ORDERNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
PARTNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
UNIT_PRICE NUMERIC (8,2) UNSIGNED NO DEFAULT NOT NULL,
QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,)
STORE BY (ordernum, partnum);
5. From the local node, create the table TABREM, which resides on the remote node:
CREATE TABLE CAT1.SCH1.TABREM
( ORDERITEM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
ORDERNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,)
LOCATION \b.$.data02;
6. From the local node, create the local view VIEWLOC on the local table TABLOC:
CREATE VIEW CAT1.SCH1.VIEWLOC AS
SELECT ORDERNUM, QTY_ORDERED FROM CAT1.SCH1.TABLOC;
7. From the local node, create the local view VIEWREM on the remote table TABREM:
CREATE VIEW CAT1.SCH1.VIEWREM AS
SELECT ORDERITEM, QTY_ORDERED FROM CAT1.SCH1.TABREM;
Example—Creating a Remote Table and a Local Index
To create a remote table and a local index for that table from the local node \A:
1. Make sure that the catalog for the table and index is registered on the remote node \B:
REGISTER CATALOG CAT1 ON \B.$DATA02;
2. From the local node \A, create the table TABREM, which will reside on the remote node \B:
CREATE TABLE CAT1.SCH1.TABREM
( ORDERITEM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
ORDERNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
Creating a Distributed SQL/MX Database 279