SQL Programming Manual for Pascal

Program Compilation and Execution
HP NonStop SQL Programming Manual for Pascal—528614-001
5-29
Using DEFINEs
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 \CHICAGO is unavailable at compile time, the SQL compiler can still compile the
program because enough information is available in the catalogs on \NEWYORK
where the first partition is registered.
Assume that the compiler uses the index on \CHICAGO in the optimized execution
plan. If \CHICAGO is still unavailable at run time, the SQL executor invokes the SQL
compiler to automatically recompile the statement. The SQL 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 DEFINEs
By using DEFINEs in a program to refer to tables and associating those DEFINEs with
local partitions, you increase the possibilities for successful compilations of programs
that access a distributed database. All SQL compilations are affected, including explicit
and dynamic 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 of these problems.
Skipping Unavailable Partitions
Use the SKIP UNAVAILABLE PARTITION option of the CONTROL TABLE directive to
cause NonStop SQL to skip a partition that is not available and to open the next
available partition that satisfies the search condition of a query. (NonStop SQL also
returns a warning message (8239) to the SQLCA.) The SKIP UNAVAILABLE
PARTITION option applies to static or dynamic SQL statements that refer to partitioned
tables and partitioned indexes of the tables.
Executing an SQL Program File
You execute an SQL program file as you would any program file by using the TACL
RUN (or RUND) command or a process-creation system procedure such as
NEWPROCESS [NOWAIT]. This subsection describes how to execute a program file