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-24
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_1200 6742156872957211894
INVENT 6742156872985380066
PERSNL 6742156872975234658
SALES 6742156872982451617
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_1200'
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_1200 $DATA08 ZSD6J6MX
•
This example uses the FILES command from the TACL prompt to display the list of
files in the definition schema. The table names are hardcoded names.
1> files $data08.zsd6j6mx
$DATA08.ZSD6J6MX
ACCPTH00 ACCPTH01 ACPTHC00 ACPTHC01 CKCOLU00 CKCOLU01
CKTBLU00 CKTBLU01 COLPRI00 COLPRI01 COLUMN00 COLUMN01
DDLKS000 DDLKS001 DDPLKS00 DDPLKS01 KYCLU000 KYCLU001
MODULE00 MODULE01 MPPART00 MPPART01 MVRGRP00 MVRGRP01