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

ORDERS
PARTS
SALES
SALES
Displaying all Tables in a Schema
This example displays all table objects in the schema SALES and catalog SAMDBCAT. Table objects
include base tables, views, MP aliases, and SPJs:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring (object_name, 1, 25) as table_name,
object_name_space as name_space,
object_type as type,
case object_security_class
when 'UM' then 'User Metadata'
when 'UT' then 'User Table'
when 'SM' then 'System Metadata'
else 'OTHER'
end as sec_class
from nonstop_sqlmx_<system name>.system_schema.catsys c,
nonstop_sqlmx_<system name>.system_schema.schemata s, objects o
where c.cat_uid = s.cat_uid
and c.cat_name = 'SAMDBCAT'
and s.schema_uid = o.schema_uid
and s.schema_name = 'SALES'
and o.object_name_space = 'TA'
order by object_name_space, table_name
for read uncommitted access;
SEC_CLASS
---------------
TYPE
----
NAME_SPACE
----------
TABLE_NAME
-------------------------
User Table
User Table
VI
BT
TA
TA
CUSTLIST
CUSTOMER
User MetadataBTTAHISTOGRAMS
User MetadataBTTAHISTOGRAMS_FREQ_VALS
User MetadataBTTAHISTOGRAM_INTERVALS
OTHERBTTAMVS_TABLE_INFO_UMD
OTHERBTTAMVS_UMD
OTHERBTTAMVS_USED_UMD
User TableBTTAODETAIL
User TableBTTAORDERS
User Table
User Table
VI
BT
TA
TA
ORDREP
PARTS
Displaying all DDL Locks on a Table
This example (when run on the node on which the metadata is located) displays the DDL locks on
the table SAMDBCAT.SALES.CUSTOMER:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring (o1.object_name from 1 for 25) as lock_name,
l.operation as util,
l.status,
substring (l.process_id from 1 for 25)
from objects o, objects o1, ddl_locks l
where o.object_name = 'CUSTOMER'
and o.object_uid = l.base_object_uid
and l.object_uid = o1.object_uid
and o.schema_uid = (select schema_uid
from nonstop_sqlmx_<system name>.system_schema.schemata
where
schema_name = 'SALES' and
Displaying Table Information 127