6.25.1 Estimating the number of pages in the global buffer for global work tables
This section explains how to estimate the number of pages in the global buffer for global work tables.
You need to specify the value determined here for the adb_dbbuff_wrktbl_glb_blk_num operand in the server definition. For details about the adb_dbbuff_wrktbl_glb_blk_num operand, see the explanation of the adb_dbbuff_wrktbl_glb_blk_num operand in 7.2.2 Operands related to performance (set format).
The method of estimating the number of pages in the global buffer for global work tables follows.
Estimation method
-
Determine the information related to the columns comprising a work table.
See Work tables created when SQL statements are executed under Considerations when executing an SQL statement that creates work tables in Designs Related to Improvement of Application Program Performance in the HADB Application Development Guide. When coding SQL statements for which work tables are created, determine for the following cases the data types and data sizes of the columns comprising a work table and the number of rows stored in the work table:
-
When multiple table references are specified in the FROM clause
-
When a derived table is specified
-
When a viewed table is specified
-
When the WITH clause is specified
-
When a table-function derived table is specified
-
When a joined table is specified
-
When a subquery is specified
-
When a recursive query is specified
-
-
Determine the number of pages in the global buffer for global work tables.
Use the formula in 5.9.1 Determining the total number of pages in the work table DB area to determine the number of pages in the global buffer for global work tables.
Use the values determined in step 1 as the variables to be substituted in the formula.
If the number of pages in the work tables exceeds the size of the work table buffer, those work tables that do not fit will be output to the work table DB area file, resulting in processing performance degradation. For this reason, it is important to estimate accurately the size of the work table buffer.