sort statement (specification of sort work directory information)

When data is loaded in the batch index creation mode for B-tree indexes, you can use the sort statement to specify information about a work file for sorting to be used (information such as the file creation directory and the number of buffer sectors for sorting).

Criterion: If you have specified neither the pd_tmp_directory operand in the system definition nor the TMP environment variable, specify the sort statement, if possible, so as to avoid a space shortage in the %PDDIR%\tmp directory.

Rules

When the sort statement is omitted, the utility creates a work file for sorting in the directory shown in Table 5-57 Directories to which pdload outputs files on the server where the index storage RDAREAs are located.
If only a plug-in index is defined for the table, the sort statement, if specified, is ignored.
The work file for sorting is deleted automatically upon completion of pdload processing.
You can specify as many sort statements as follows:
HiRDB/Single Server:
Specify only one sort statement.
HiRDB/Parallel Server:
Specify as many sort statements as there are servers in which the partitioned indexes are stored for a row partitioned table. Even with the indexes of a row partitioned table, if data loading is executed in units of RDAREAs, specify only one sort statement.

Organization of this subsection: (1) Format; (2) Explanation

(a) server-name

~<identifier> ((1-8))

Specifies the name of the server in which the sort work file is to be created.

HiRDB/Single Server: Do not specify this operand for a HiRDB/Single Server. A single server name is ignored, if specified.

HiRDB/Parallel Server: Specify the name of the server in which the sort work file is to be created.

(b) directory-name

~<pathname>

Specifies the absolute pathname of the directory under which the sort work file is to be created.

(c) buffer-size-for-sorting

~<unsigned integer> ((128-2097152)) <<1024>>

Specifies in KB the amount of memory that is to be used as the buffer.

For a HiRDB/Single Server, the system reserves this buffer at the single server; for a HiRDB/Parallel Server, the system reserves it at a back-end server.

The sort processing creates a temporary work file in a specified directory. You can use the formula shown below to minimize the size of this file. Note that this is just a guideline. If there is not enough memory or most data is sorted in the order of index key values, do not specify a value that is larger than necessary. This formula provides the value that minimizes the file size; it does not minimize the sorting time. If there is enough memory, specify a buffer whose size is several megabytes to several dozens of megabytes.

[Figure]

n: Number of data items (sum of existing data items in the table and the data items to be added). For a repetition column, this is the number of elements, not the number of rows.

k: Key length (calculated based on the maximum value). For the formula for determining the key length, see a sample calculation of index storage pages in the HiRDB Version 9 Installation and Design Guide.

x: 10, if all key structure columns are fixed length; 12, if some of the key structure columns are variable length.

c: Number of columns comprising the index.

z: c 4 for a variable-length multicolumn index; 0 otherwise.

K: k + c + 8 for a variable-length multicolumn index; k + 12 otherwise.

N: (c 2) + 2 for a variable-length multicolumn index; 5 otherwise.

R: k + + z

A: R + (K + 8) + 28 for 32-bit mode HiRDB; R + (K + 8) + 56 for 64-bit mode HiRDB.

B: R + (K + 8) + 56 for 32-bit mode HiRDB; R + (K + 8) + 104 for 64-bit mode HiRDB.

C: 2,092/(N 32) + (K + 8) for 32-bit mode HiRDB; 2,112/(N 32) + (K + 8 for 64-bit mode HiRDB.

5.4.6 sort statement (specification of sort work directory information)

(1) Format

(2) Explanation

(a) server-name

(b) directory-name

(c) buffer-size-for-sorting