SQL/MP Programming Manual for COBOL

Maximizing Local Autonomy
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
C-2
Using TACL DEFINEs
This example shows the concept of maximizing local autonomy. The PARTS table is a
partitioned table that resides on the \NEWYORK and \PARIS nodes:
A program declares an SQL cursor as follows:
EXEC SQL DECLARE GET_PART_CURSOR CURSOR FOR
SELECT PARTNUM, PARTDESC, PRICE, QTY_AVAILABLE
FROM =PARTS
WHERE PARTS.PARTNUM < 5000
AND PARTS.PARTDESC = "V8 DISK OPTION"
END-EXEC.
The program running on \NEWYORK uses a DEFINE to associate the PARTS table
with the first partition located at \NEWYORK:
SET DEFINE CLASS MAP
ADD DEFINE =parts, FILE \NEWYORK.$VOL1.SALES.PARTS
If \PARIS is unavailable at compile time, the program can still compile because enough
information is available in the catalogs on \NEWYORK, where the first partition is
registered.
Suppose that the compiler uses the index on \PARIS in the optimized execution plan. If
\PARIS is still unavailable at run time, the executor invokes the compiler to
automatically recompile the statement. The compiler determines an execution plan that
does not use the index IXPART but will sequentially scan the rows in the first partition
to find all parts that have “V8 DISK OPTION” in the PARTDESC column.
Using TACL DEFINEs
Use class MAP DEFINEs in a program to refer to tables. By associating DEFINEs with
local partitions rather than remote partitions, you can increase the number of
successful compilations of the programs that access a distributed database. All SQL
compilations are affected, including both explicit compilations and automatic
recompilations.
Using Current Statistics
For a partitioned table to have local autonomy, the UPDATE STATISTICS statement
must be executed on the table at least once. If the SQL catalog in which a table is
registered does not have any statistics for the table, the SQL optimizer does a catalog
look-up operation for each partition of the table to estimate the aggregate number of
nonempty blocks and records. Also, if the statistics for an unavailable partitioned table
have not been updated, you will receive an SQL warning and file-system error even if
\NEWYORK The first partition contains all rows in which PARTS.PARTNUM (the
primary key) is less than 5000.
\PARIS The second partition contains all rows in which PARTS.PARTNUM is
5000 or greater. An index on the PARTDESC column of table
PARTS, is named IXPART.