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

Using REGISTER CATALOG to Access Objects on Remote Nodes
Statements in an embedded SQL program can refer to SQL/MX objects on a remote node without
changes to the database object names in the source code. However, the catalog that contains the
objects must be visible from the local node before you can process and run the program. Use the
REGISTER CATALOG statement to create an empty catalog reference on the local node that enables
local programs to query the catalog’s objects on a remote node or nodes. Use the UNREGISTER
CATALOG statement to remove the empty catalog reference from the local node.
From an SQL program, you can create, alter, or drop an SQL/MX object that resides entirely on
one remote node or is partitioned across several remote nodes. To create, alter, or drop an entire
object located on one or more remote nodes, the catalog for the object must be registered on the
node from which the SQL program runs.
Before issuing a REGISTER CATALOG or UNREGISTER CATALOG statement, consider these
requirements:
• Make sure that a catalog with an identical name does not already exist on the node from
which you run the program.
• To execute the REGISTER CATALOG or UNREGISTER CATALOG statement from the local
node, you must be the owner of the catalog or the super ID user.
• Before executing REGISTER CATALOG and UNREGISTER CATALOG for a remote node, you
must establish remote password access from the local node to the local node and from the
remote node to the local node. For more information about this and other aspects of security
in a distributed SQL/MX database environment, see “Security Guidelines” (page 269).
For more information, see “Managing an SQL/MX Distributed Database” (page 266), SQL/MX
Reference Manual and the SQL/MX Programming Manual for C and COBOL.
Maintaining Local Autonomy for Programmatic Queries
The ability of local programs to successfully query objects on remote nodes in a distributed SQL/MX
database depends on the operations they perform and the availability of remote nodes that contain
the queried object data and its metadata.
• Local programs that perform DDL or dynamic DML operations must have access to the node
that contains the metadata for each referenced object. If the node where object metadata is
located becomes unavailable, local programs cannot perform a DDL or dynamically compiled
DML operations on those objects, even if the objects reside on available nodes.
• For local programs that perform late name resolution of ANSI names or automatic recompilation
of static DML operations, object metadata must be on an available node. However, local
programs with static DML statements that do not use ANSI names, late name resolution of
ANSI names, or automatic recompilation can execute successfully, even when the object
metadata node is unavailable, provided that the node or nodes that contain the objects remain
available.
• In a distributed SQL/MX database environment, statically compiled queries in SQL programs
can execute successfully when:
◦ Either the node that stores a given query object’s metadata is available or the query does
not use late name resolution of ANSI names or automatic recompilation.
◦ The data that a given program query attempts to access is stored on available disks on
the local node or on available remote nodes.
For more information, see “Managing an SQL/MX Distributed Database” (page 266).
Distributing Programs Across Nodes 219