5.4.7 sort statement (specification of sort work directory information)

When loading data in the batch index creation mode for B-tree indexes, you can use the sort statement to specify the directory for a sort work file.

Criterion
Specify the sort statement, if possible, so as to avoid a space shortage in the /tmp directory.
Rules
  1. When the sort statement is omitted, the utility assumes the /tmp directory in the server where the index storage RDAREAs are located.
  2. If only a plug-in index is defined for the table, the sort statement, if specified, is ignored.
  3. 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.
  4. If no index has been defined for the target table, do not specify the 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 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

[Figure]<pathname>

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

(c) buffer-size-for-sorting

[Figure]<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 8 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.
y
2 in Linux; 1 otherwise.
z
c[Figure] 4 for a variable-length multicolumn index; 0 otherwise.
K
k + c + 8 for a variable-length multicolumn index; k + 12 otherwise.
N
(c[Figure] 2) + y for a variable-length multicolumn index; 3 + y otherwise.
R
k + x + 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
2092/(N[Figure] 32) + (K + 8) for 32-bit mode HiRDB; 2112/(N[Figure] 32) + (K + 8 for 64-bit mode HiRDB.

(3) Note

Do not allocate NFS to the directory that is specified in the sort statement. If NFS is so allocated, the size of the work file for sorting becomes twice as large as when local files are used. Other problems that arise include the fact that work files for sorting remain.