8.9.8 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.

Criterion
If possible, always specify the sort statement in order to avoid a shortage of space in the /tmp directory.
Rules
  1. When the sort statement is omitted, the utility assumes the /tmp directory at the server that contains index storage RDAREAs.
  2. You can specify as many sort statements as follows:
    HiRDB/Single Server
    Specify only one sort statement.
    HiRDB/Parallel Server
    For an index of a partitioned table, specify as many sort statements as there are servers that contain the table partitions. For a non-partitioned table or when reorganizing a partitioned table in units of RDAREAs, specify only one sort statement.
Organization of this subsection
(1) Format
(2) Explanation
(3) Note

(1) Format

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

(2) Explanation

(a) server-name

[Figure]<identifier> ((1-8))

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

HiRDB/Single Server
Do not specify this option. A single server name will be ignored, if specified.
HiRDB/Parallel Server
Specify the name of the server used to create the work file for sorting.
(b) directory-name

[Figure]<pathname> ((1-255))

Specifies the absolute path name of the directory in which sort work file is to be created.

(c) buffer-size-for-sorting

[Figure]<unsigned integer> ((128-2097152)) <<1024>>

Specifies 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 for sorting 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; avoid using a large value. Note that this formula provides a value that minimizes the file size, not a value that minimizes the sorting time. If there is enough memory, specify a buffer size of about several megabytes to several tens of megabytes.

[Figure]

n: Number of unloaded data items. For a repetition column, this is 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 8 Installation and Design Guide.

x: 10, if all key component columns are fixed length; 12, if at least one of the key component columns is variable length.

c:
Number of columns composing the index
y:
For Linux version, 2; otherwise, 1.
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) + y; otherwise, 3 + y.
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, 2092 + (N[Figure] 32) + (K + 8); for 64-bit mode HiRDB, 2112 + (N[Figure] 32) + (K + 8).

(3) Note

Do not allocate NFS to the directory that is specified in the sort statement. If NFS is allocated, twice as much space is required for the work file for sorting as when a local file is used. Also, problems occur related to retention of work files for sorting.