SQL/MP Installation and Management Guide

Querying SQL/MP Catalogs
HP NonStop SQL/MP Installation and Management Guide—523353-004
6-10
Joining Catalog Tables With UNION
This example displays all the partition information for the PARTLOC table. The query
uses the LIKE predicate with the wild-card character % to indicate that a string of 0, 1,
or more characters is acceptable in the wild-card position for a qualifying name.
>> VOLUME \SYS1.$VOL1.INVENT;
>> SELECT * FROM PARTNS
+> WHERE FILENAME LIKE "%PARTLOC%";
Joining Catalog Tables With UNION
The UNION operator in a SELECT statement can effectively join catalog tables of the
same type.
Suppose that you want to join the TABLES catalog tables from two catalogs, SALES
and INVENT, to produce a joined output display. These catalogs, in fact, reside on two
different nodes. This SELECT statement selects the TABLENAME, TABLECODE, and
SECURITYVECTOR columns but eliminates tables in which TABLECODE is greater
than or equal to 572:
>> VOLUME \SYS1.$VOL1.SALES;
>> SELECT TABLENAME, TABLECODE, SECURITYVECTOR
+> FROM \SYS1.$DATA2.SALES.TABLES
+> WHERE TABLECODE < 572
+> UNION SELECT TABLENAME, TABLECODE, SECURITYVECTOR
+> FROM \SYS2.$DATA7.INVENT.TABLES
+> WHERE TABLECODE < 572
+> ORDER BY TABLECODE;
Displaying File and Security Attributes
The FILEINFO utility displays file and security attributes for catalog tables, base tables,
indexes, views, collations, and Enscribe and OSS files. You can request a display for
one object or for each object specified in a qualified file set list.
You can request that the file information be displayed in brief format (a condensed,
one-line format) or in the detailed, standard format. You can also request statistical
information about the data bit map.
This command obtains a brief format listing for all objects described in the
$VOL1.INVENT catalog:
>> FILEINFO *.*.* FROM CATALOG $VOL1.INVENT;
This command obtains a brief format listing from the $VOL1.INVENT catalog for SQL
tables that have not been updated since January 10, 1989:
>> FILEINFO *.*.* FROM CATALOG $VOL1.INVENT
+> WHERE SQL AND MODTIME BEFORE JAN 10 1989;
If your files are managed by SMF, you typically create an SQL object with a logical file
name. You can find out the corresponding physical file name by using the detailed
format of the FILEINFO command and specifying the logical name.