13.1.11 Changing the file configuration of the work table DB area
This subsection explains how to prevent degradation in the processing performance of SQL statements caused by automatic extension of the work table DB area.
If a work table DB area file consists of regular files, a work table DB area with the smallest size is created when the adbinit command is executed. After that, the necessary size is allocated to the work table DB area through DB area automatic extension when an SQL statement that creates work tables is executed.
If automatic extension of the work table DB area occurs, it impacts the processing performance of an SQL statement that creates work tables. To prevent automatic extension from degrading processing performance of SQL statements, consider using block special files to configure the work table DB area files.
Check the following item in the HADB server statistical information that is output when the adbstat command is executed:
-
Wrktbl_page_use_max (maximum number of work table pages allocated)
Substitute the value that is output to Wrktbl_page_use_max for the WRK_PAGE_NUM variable in the formula provided in 5.9 Estimating the size of the work table DB area to estimate the size of the work table DB area. Based on the estimated size, prepare a block special file with the necessary size allocated.
After preparing a block special file of the necessary size, change the work table DB area file to a block special file. For details about how to change the work table DB area file to a block special file, see 11.11.1 Changing the storage location of a work table DB area file.