Operands related to work tables

6) pd_work_buff_mode = each | pool

Specifies the method of allocating buffers when HiRDB creates tables.

each: Allocate a buffer for each work table.

pool: Allocate a buffer pool for each server process.

Specification guidelines

Normally, pool is specified. pool is the appropriate specification when a large volume of data is to be retrieved and when manipulations such as join, ORDER BY, and GROUP BY are to be performed.
When the size of the process private area that can be used for work table buffers is predetermined, pool should be specified. When pool is specified, HiRDB efficiently allocates work table buffers to work tables.
In such a case, the process private area is occupied on the basis of the value specified in pd_work_buff_size, and input/output operations on work tables are buffered in that pool. Therefore, the process private memory is occupied only to the extent of the value specified in pd_work_buff_size.

7) pd_work_buff_size = work-table-buffer-size

<unsigned integer> (KB)

32-bit mode: ((128-1000000))
64-bit mode: ((128-4000000000))

Specifies in kilobytes the size of buffers for work tables to be created by HiRDB.

Item	pd_work_buff_mode=each is specified	pd_work_buff_mode=pool is specified
Advantage	A large work table buffer size reduces the number of I/O operations associated with data manipulation, which means that the execution time of SQLs that use work tables is also reduced. However, because each server's process private memory is used, specification of this option should take into account the overall size of the system memory (real memory and virtual memory). If pd_work_buff_mode = each is specified, the memory size to be allocated is value of pd_work_buff_size required number of work tables. Therefore, specifying an unnecessarily large value may cause a virtual memory shortage for other processes.
Application criterion	pd_work_buff_mode = pool should be specified when a large volume of data is to be retrieved and when manipulations such as join, ORDER BY, and GROUP BY are to be performed.
Specification guidelines	Specify the size of the buffer to be allocated for one work table. If a value greater than the work table memory capacity is specified for the work table buffer size, input/output to the work table file during work table creation is eliminated, thus reducing the time necessary for work tables. The following formula can be used to determine the work table memory capacity: Work table memory capacity = Applicable work table size^* 2	Specify the size of the buffer pool to be allocated for the entire server process. Specify a value between 4352 and 5120 when a large volume of data is to be retrieved or when manipulations such as join, ORDER BY, and GROUP BY are to be performed. Specifying such a value increases the unit of sorting input/output, thus reducing the sort time. If a large value is specified for the work table buffer size, with the total work table memory capacity for each SQL statement as the upper limit, input/output operations on the work table file during work table creation is eliminated, thus reducing the time necessary for work tables. The following formula can be used to determine the total work table memory capacity for each SQL statement: Total work table memory capacity per SQL statement = a b + c d
Notes	When multiple users execute processes concurrently or when an SQL statement that uses multiple work tables is executed, the specified size buffer is allocated for each work table. Consequently, specifying a large value may result in a memory shortage.	If the value specified for the work table buffer size is smaller than the number of work tables to be used by each SQL statement, the processing time may become longer than when each is specified. Specifically, specify a value that is at least equal to maximum number of work tables for each SQL statement 128. The following formula can be used to determine the maximum number of work tables for each SQL statement: Maximum number of work tables for each SQL statement = b + d
Operand rule	Specify a multiple of 128. If the value is not a multiple of 128, it is rounded up to the next multiple of 128.	Specify a multiple of 128. If the value is not a multiple of 128, it is rounded up to the next multiple of 128. Specify at least 384. If a value that is smaller than 384 is specified, it is rounded up to 384.

a:: {Capacity of work table (for storing column information)^* (KB) 2} 128 128

b:: Maximum number of work tables (for storing column information)^*

c:: {Capacity of work table (for storing positional information)^* (KB) 2} 128 128

d:: Maximum number of work tables (for storing positional information)^*

^* For details about how to determine these values, see the HiRDB Version 8 Installation and Design Guide.

8) pd_work_buff_expand_limit = work-table-buffer-expansion-limit

<unsigned integer> (KB)

32-bit mode: ((128-1000000))
64-bit mode: ((128-4000000000))

The size of the work table buffer to be created by HiRDB is specified by the pd_work_buff_size operand. Specify the pd_work_buff_expand_limit operand if you want to automatically expand a work table buffer when the space in this buffer becomes insufficient. The work table buffer is expanded up to the size specified by this operand.

For example, when the following values are specified for the operands, a 1,024-KB work table buffer is normally allocated. When this size becomes insufficient, the work table buffer is expanded up to 2,048 KB.

pd_work_buff_size = 1024
pd_work_buff_expand_limit = 2048

HiRDB expands a work table buffer in the following cases:

The necessary work table buffer cannot be allocated when hash execution is applied to an execution method that uses hash join or subquery hash as the joining method.
A 128-KB work table buffer allocated to each work table becomes insufficient when multiple work tables are concurrently used.

Condition: The pd_work_buff_mode operand must be omitted or pool must be specified for it.

Advantage: You can prevent a work table buffer shortage (too small a value specified for the pd_work_buff_size operand) from causing UAP errors.

Notes

A work table buffer is not expanded when either of the following conditions is satisfied:

The pd_work_buff_expand_limit operand is not specified.
pd_work_buff_expand_limit operand value pd_work_buff_size operand value

Operand rule: Specify a multiple of 128. If a value other than a multiple of 128 is specified, it is automatically rounded up to a multiple of 128.

Relationship to other operands: When a work table buffer is expanded for the first time in a back-end server process, the KFPH29008-I message is output. Note that you can use the pd_work_table_option operand to suppress this message output.

Note

After a work table buffer has been expanded, when the number of work tables being used by the applicable server process goes to zero, the expanded work table buffer is released. The number of work tables being used can go to zero in the following cases:

All cursors that were being used are closed. (In this case, the number of work tables being used may not go to zero.)
A transaction is normally terminated or cancelled when a holdable cursor is not being used.
A UAP is disconnected from HiRDB when a holdable cursor is being used.

Remarks

Hash join, subquery hash execution is applied in the following cases:

Application of optimizing mode 2 based on cost and hash join, subquery hash execution are specified in the pd_additional_optimize_level operand, the PDADDITIONALOPTLVL operand of the client environment definition, or the ADD OPTIMIZE LEVEL operand of the SQL compile option.
HASH is specified for the SQL optimization specification of the joining method inside an SQL statement.
HASH is specified for the SQL optimization specification of the subquery execution method inside an SQL statement.

8.3.2 Operands related to work tables