SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Querying SQL/MX Metadata

HP NonStop SQL/MX Installation and Management Guide—523723-004

8-23

Displaying All Catalogs Visible on a Node

The CATSYS table contains one (and only one) row for each catalog that is visible on

that node. This example displays a list of all the visible catalogs on a local node (where

node-name is the name of the local node):

>> select cat_name from nonstop_sqlmx

_node-name.system_schema.catsys;

CAT_NAME

--------------------

CAT

DISTRIB_CAT

NONSTOP_SQLMX_AZTEC

Determining Whether a Catalog is Local or Remote

The CAT_REFERENCES table contains at least one row for each catalog that is visible

on the local node. It can contain more than one row for each such catalog, but only the

row that has the value 'A' in the REPLICATION_RULE column contains the name of

the node where the metadata for the catalog resides.

If the NODE_NAME in the row where REPLICATION_RULE = 'A' is the local node,

then the catalog is local; otherwise, it is remote.

In this example, the local node is \AZTEC and catalog FIGARO_CAT was created on

\FIGARO and then registered on \AZTEC:

>> select substring(cat_name,1,20) as cat_name,

case cr.replication_rule

when 'A' then 'LOCAL'

else 'REMOTE'

end as LOCATION

from catsys c, cat_references cr

where c.cat_uid=cr.cat_uid

and cr.node_name = '\AZTEC';

CAT_NAME LOCATION

------------------- -------------

NONSTOP_SQLMX_AZTEC LOCAL

CAT LOCAL

FIGARO_CAT REMOTE