SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—523725-004
10-61
Sequence Functions
specified by the SCRATCH_DISKS_EXCLUDED control are removed. If the
SCRATCH_DISKS control is specified, the disks that are not specified in the
SCRATCH_DISKS control are removed from the pool. From this disk pool, a disk is
selected based on this criteria:
•
The amount of used space on the disk. (rank * 30)
•
The number of scratch files on the disk. (rank * 70)
•
The number of fragments on the disk. (rank * 20)
•
The biggest available fragment on the disk. (inverted rank * 80)
•
Is the disk a preferred disk? (10000)
•
Is the disk the primary disk of the CPU of this process? (100000)
The value in parentheses indicates the weighting of that criterion. The rank is the
ordinal rank of that disk among all the disks in the pool based on the criterion. The
inverted rank is the inverted ordinal rank. In the case of the biggest available fragment
criterion, if the pool contains 20 disks, the disk with the biggest available fragment
would have an inverted rank of 20. The weights are summed for all the disks in the
pool, and the disk with the biggest weight is selected. As can be seen, the primary disk
of the current CPU is given a large weight.
In NonStop SQL/MX, a scratch file can overflow to another disk. So, if a scratch file
becomes full or if the disk becomes full, the operation does not necessarily fail. An
additional scratch file on another disk is selected (using the criterion procedure). As a
result, there is no 2 GB limit on scratch space.
In NonStop SQL/MX, the operations that can create scratch files are sort, hash join,
and hash groupby. They all use the criterion procedure to determine which scratch disk
to use.
NonStop SQL/MX does not manage swap file space directly. Instead, SQL/MX
processes rely on the Kernel-Managed Swap Facility (KMSF), which is set up in the
NonStop operating system with the NSKCOM tool. Each CPU has an associated swap
file.
Sequence Functions
This attribute enables NonStop SQL/MX to optimize the execution of sequence
functions:
Attribute Setting
DEF_MAX_HISTORY_
ROWS
Number of rows the SEQUENCE BY operator keeps in its
history buffer. This value affects sequence functions that
examine a maximum number of rows and overrides any larger
maximum specified as a sequence function argument.
The default is 1024.