SQL/MP Installation and Management Guide

Querying SQL/MP Catalogs
HP NonStop SQL/MP Installation and Management Guide—523353-004
6-4
Displaying Information About Catalogs
Displaying Information About Catalogs
Information about all the catalogs on the node appears in the system directory of
catalogs, which is the CATALOGS table in the system catalog.
The CATALOGS table must reside on the subvolume SQL on the same volume as the
system catalog. The default location is $SYSTEM.SQL.
This query displays all SQL catalogs on the node \SYS1:
>> SELECT * FROM \SYS1.$SYSTEM.SQL.CATALOGS;
Displaying Information About Tables
Catalogs have three tables that describe base tables:
•
BASETABS describes the attributes of a base table, such as whether any
constraints are defined on the table and the number of rows in the table.
•
TABLES describes file-label information, such as the security string, owner ID,
creation timestamp, and number of columns.
•
FILES describes file-label information, such as the file type (organization), extents,
audit flag, partitioned flag, address of the end of file, record size, and various other
flags.
You can query each catalog table separately, or you can create a joined view of the
tables.
This example displays BASETABS information about a base table:
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT * FROM BASETABS
+> WHERE TABLENAME = "\SYS1.$VOL1.SALES.CUSTOMER";
This example displays TABLES information about a table:
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT * FROM TABLES
+> WHERE TABLENAME = "\SYS1.$VOL1.SALES.CUSTOMER";
This example displays FILES information about a table. The query uses the LIKE
predicate with the wild-card character % to indicate that a string ranging from no
characters to many characters is acceptable in the wild-card position for a qualifying
file name. Also, notice that characters in LIKE predicates are case sensitive and that
references to data contained in tables are in uppercase letters.
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT * FROM FILES
+> WHERE FILENAME LIKE "%CUSTOMER%";