10.3.5 sort statement (specification of work directory for sorting)

The sort statement specifies information about a work file for sorting that is used when an index is created in the batch index creation mode (-i c).

Criterion
If you have specified neither the pd_tmp_directory operand in the system definition nor the TMP environment variable, you must specify the sort statement, if possible, in order to avoid a shortage of space in the %PDDIR%\tmp directory.
Rules
  1. If the sort statement is omitted, the utility assumes the directory shown in Table 10-2 Directory to which pdrbal outputs files as specified by the server on the server where the index storage RDAREA is located.
  2. The work file for sorting is deleted automatically when pdrbal processing terminates.
  3. For a HiRDB/Single Server, specify only one sort statement.
    For a HiRDB/Parallel Server, specify as many sort statements as there are servers that contain the index storage RDAREAs added by ALTER TABLE.
Organization of this subsection
(1) Format
(2) Explanation

(1) Format

sort [server-name] directory-name[,buffer-size-for-sorting]

(2) Explanation

(a) server-name

~<identifier> ((1-8))

Specify the name of the server used to create the work file for sorting.

For a HiRDB/Single Server, do not specify the server name.

(b) directory-name

~<pathname> ((1-255))

Specify the absolute pathname of the directory in which the sort work file is to be created.

(c) buffer-size-for-sorting

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

Specify, in KB, the size of memory that is to be used as the buffer.

The system allocates this buffer at the single server for a HiRDB/Single Server and at the back-end server for a HiRDB/Parallel Server.

The sort process creates a temporary work file in a specified directory. You can use the following formula to determine a buffer size that minimizes the file size. This is just a guideline. If there is not enough memory, avoid using a large value.

[Figure]

n: Number of data items to be rebalanced. For a repetition column, the number of data items means the number of elements, not the number of rows.

k: Key length (calculated as a maximum value). For details about how to determine the key length, see the example of calculating the number of index storage pages in the HiRDB Version 9 Installation and Design Guide.

x: 10 if all key component columns have a fixed length; 12 if at least one of the key component columns has a variable length.

c:
Number of columns composing the index.
z:
For a variable-length multicolumn index, c[Figure] 4; otherwise, 0.
K:
For a variable-length multicolumn index, k + c + 8; otherwise, k + 12.
N:
For a variable-length multicolumn index, (c[Figure] 2) + 2; otherwise, 5.
R:
k + x + z
A:
For 32-bit mode HiRDB, R + (K + 8) + 28; for 64-bit mode HiRDB, R + (K + 8) + 56.
B:
For 32-bit mode HiRDB, R + (K + 8) + 56; for 64-bit mode HiRDB, R + (K + 8) + 104.
C:
For 32-bit mode HiRDB, 2,092 + (N[Figure] 32) + (K + 8); for 64-bit mode HiRDB, 2,112 + (N[Figure] 32) + (K + 8).