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]](figure/zu050050.gif)
- 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.