FastSort Manual

Sorting From NonStop SQL/MP
FastSort Manual—429834-003
8-17
Configuring a LOAD Statement
DEFINE. For more information about partitioned scratch files, see Using a
Partitioned Scratch File on page 9-8.
If you specify the SORTED option, you can omit the SCRATCH option.
Loading Large Tables
Use the following sort workspace guidelines to load data into a large table.
Setting MAX Number of Records
LOAD uses the MAX parameter to estimate file and extent size for an initial scratch file.
By default, FastSort creates an initial scratch file large enough for only 50,000 records.
If VLM is on, the default MAX value is 1,000,000 records.
To ensure efficient use of sort workspace, specify an accurate value for MAX in the
LOAD command. To estimate the number of records in the base table, divide file size
by record length.
Using VLM With LOAD
VLM shortens the elapsed time of most nonparallel load operations. To use VLM with
the SQLCI LOAD command, set VLM ON in a =_SORT_DEFAULTS DEFINE. For
more information about VLM, see Using VLM on page 9-10. To learn how to set up a
=_SORT_DEFAULTS DEFINE, see Configuring Your SQL/MP Sort Environment on
page 8-2.
Do not use VLM for parallel load operations.
Resizing Primary Extent
Large extents can cause problems with sort workspace when you load data from a
table into an index.
For large tables, space on the destination disk might be too fragmented to hold the
table or index extents. In this case, SORTPROG returns error 29 (A WRITE HAS
FAILED TO THE TO FILE) and the load operation fails. Before you load data into a
large table, ensure that table extent sizes fit on the destination disk. If extents are too
large for the disk, re-create the index and specify a smaller extent size.
Specifying a Partitioned Scratch File
If you load data into a large table, FastSort might require an initial scratch file that is
too large to fit on one disk. To estimate initial scratch file size for an SQL/MP load
operation, use the formula in Using the Default Configuration
on page 8-9.
If not enough continuous disk space exists on your node for an initial scratch file, you
can create and use a partitioned scratch file. While the maximum size of a
nonpartitioned scratch file is 1 TB if it is created by the user and up to 2 GB otherwise,
a partitioned scratch file can be greater than 1 TB. For more information, see Using a
Partitioned Scratch File on page 9-8.