SQL/MX 3.x Installation and Management Guide (H06.22+, J06.11+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—640325-001
8-25
Displaying All Schemas in a Catalog
Displaying All Schemas in a Catalog
This example displays a list of all schemas in a catalog:
>> set schema nonstop_sqlmx_figaro.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_NAME SCHEMA_UID
------------------------------ --------------------
DEFINITION_SCHEMA_VERSION_3000 1950548695223685006
INVENT 1950548695240365422
PERSNL 1950548695235454934
SALES 1950548695238563835
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_figaro.system_schema;
--- SQL operation complete.
>> select substring(schema_name, 1, 30) as schema_name,
local_smd_volume, schema_subvolume
from schemata s, catsys c
where s.schema_name = 'DEFINITION_SCHEMA_VERSION_3000'
and c.cat_name = 'SAMDBCAT'
and s.cat_uid = c.cat_uid
order by schema_name
for read uncommitted access;
SCHEMA_NAME LOCAL_SMD_VOLUME SCHEMA_SUBVOLUME
------------------------------ ---------------- ----------------
DEFINITION_SCHEMA_VERSION_3000 $SYSTEM ZSD8F9M1
--- 1 row(s) selected.
•
This example uses the FILES command from the TACL prompt to display the list of
files in the definition schema. The table names are hard-coded names.
1> files $data08.zsdaj6mx
$DATA08.ZSDAJ6MX
ACCPTH00 ACCPTH01 ACPTHC00 ACPTHC01 CKCOLU00 CKCOLU01
CKTBLU00 CKTBLU01 COLPRI00 COLPRI01 COLUMN00 COLUMN01