SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

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
order by schema_name
for read uncommitted access;
SCHEMA_UID
--------------------
SCHEMA_NAME
------------------------------
1950548695223685006
1950548695240365422
DEFINITION_SCHEMA_VERSION_3000
INVENT
1950548695235454934
1950548695238563835
PERSNL
SALES
Displaying the Guardian Location of the Metadata Tables for a Catalog
• This example displays the Guardian subvolumes where the system and user tables are located
for schemas in the catalog SAMDBCAT:
>> set schema nonstop_sqlmx_<system name>.system_schema;
--- SQL operation complete.
>> select substring(schema_name, 1, 30) as schema_name,
local_smd_volume, schema_subvolume
from schemata s, catsys c
Displaying Catalog Information 119