SQL/MP Installation and Management Guide

Managing a Distributed Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
12-13
SQL Compilation and the CPU Usage DEFINE
You can use this feature together with the SMF product to manage resources in a
distributed environment. For example, to set up a system in which a specified subset of
processors performs both parallel query operations and data access for a particular
application:
1. Using SMF, create a storage pool and assign it physical volumes that are all
primaried to a specified set of processors.
2. Create partitioned tables and indexes for the application; use virtual volumes
associated with the storage pool defined in Step 1.
3. Set the _SQL_CMP_CPUS DEFINE to limit parallel queries (ESPs) to the specified
set of processors.
4. Compile the programs for the application.
These steps suggest in a general way how to use these two features together. Specific
uses will vary according to the requirements of your environment.
For an overview of the benefits of using SMF to manage disk volumes for partitioned
SQL tables, see Creating Partitions on a System That Uses SMF on page 5-32. For
more information about using SMF, see the Storage Management Foundation User’s
Guide.
SQL Compilation and the CPU Usage DEFINE
The _SQL_CMP_CPUS DEFINE affects the compilation of queries. In a parallel
execution plan, the compiler assigns ESPs to the processors specified as usable by
the DEFINE. If a processor is unavailable at compilation time, the compiler does not
assign any ESPs to that processor, although the DEFINE has specified it as usable.
If a processor that was specified as usable is unavailable at run time, the executor
reassigns its ESP to another processor. The substitute processor does not have to be
described as usable by the DEFINE.
You can reset the DEFINE after you compile (and run) a query. If you need to
recompile the query, or if it undergoes automatic recompilation, the compiler will use
the potentially different DEFINE values in effect at the time of recompilation. If you
want the compiler to use the original DEFINE values, use the STOREDDEFINES
option when you first compile the query. For more information about the
STOREDDEFINES option, see Using DEFINEs During Compilation on page 10-32.
Using the Processor Usage DEFINE
You specify which processors are usable in a given system by using the FILE clause of
the _SQL_CMP_CPUS DEFINE. The FILE clause has the format Xhhhh.The four
variables (hhhh) are hex characters that identify up to 16 processors in a system.
Each hex character identifies four processors, as follows:
First hex character: CPUs 0-3
Second hex character: CPUs 4-7