SQL/MP Programming Manual for COBOL85

Maximizing Local Autonomy
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
C-2
Using TACL DEFINEs
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
your query does not try to retrieve any rows from the unavailable partition. Executing
the UPDATE STATISTICS statement can eliminate both these problems.
Skipping Unavailable Partitions
Use the SKIP UNAVAILABLE PARTITION option of the CONTROL TABLE directive to
cause SQL/MP to skip a partition that is not available and to open the next available
partition that satisfies the search condition of a query. (SQL/MP also returns warning
message 8239 to the SQLCA structure.) The SKIP UNAVAILABLE PARTITION option