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

PSDB
SAMDBCAT
\FIGARO
\FIGARO
6742156871006226409
6742156872955712923
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';
LOCATION
-------------
CAT_NAME
-------------------
LOCALNONSTOP_SQLMX_AZTEC
LOCAL
REMOTE
CAT
FIGARO_CAT
Displaying all Schemas in a Catalog
This example displays a list of all schemas in a catalog:
>> set schema nonstop_sqlmx_<system name>.system_schema;
--- SQL operation complete.
>> select substring(schema_name, 1, 30) as schema_name,
schema_uid
from schemata s, catsys c
where cat_name = 'SAMDBCAT'
and s.cat_uid = c.cat_uid
Displaying Catalog Information 119