13.2.3 Reducing the execution time of an SQL statement that creates a global work table

When you execute an SQL statement that creates a global work table, you can sometimes reduce its execution time by increasing the number of pages in the global buffer for global work tables.

For details about SQL statements that create global work tables, 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.

The tuning method to use depends on the following information, output by the adbstat command:

Global buffer statistical information
SQL statement statistical information

First, check the global buffer statistical information and perform tuning. Afterward, if you want to reduce the execution time of a specific SQL statement, check the SQL statement's statistical information and perform tuning again.

Organization of this subsection

(1) Using the global buffer statistical information to reduce the execution time of an SQL statement that creates a global work table
(2) Using the SQL statement statistical information to reduce the execution time of an SQL statement that creates a global work table

(1) Using the global buffer statistical information to reduce the execution time of an SQL statement that creates a global work table

Procedure

Check the number of times files are written from the global buffer.

In the global buffer statistical information that is output when the adbstat command is executed, check the following types of information first:
- DBbuff_name (global buffer name)
For the global buffer whose DBbuff_name is ADBWRK, check the following information:
- DBbuff_page_write_cnt (number of times files are written from the global buffer)
If the value that is output to DBbuff_page_write_cnt is at least 1, data has been written from the global buffer to files. If this case applies, go to step 2.
Terminate the HADB server.

Terminate the HADB server by executing the adbstop command.
Change the server definition.

Increase the value specified for the adb_dbbuff_wrktbl_glb_blk_num operand in the server definition.
Start the HADB server.

Execute the adbstart command to start the HADB server.
Re-execute the SQL statement that creates a global work table.

After re-executing the SQL statement that creates a global work table, perform step 1 to check whether the value that was output to DBbuff_page_write_cnt has decreased.
Repeat steps 1 to 5.

If you make adjustments by repeating steps 1 to 5 until the value that is output to DBbuff_page_write_cnt becomes 0, you can sometimes reduce the execution time of the SQL statement that creates a global work table.

Note: For details about the adb_dbbuff_wrktbl_glb_blk_num operand in the server definition, see the adb_dbbuff_wrktbl_glb_blk_num operand in 7.2.2 Operands related to performance (set format).

To Page Top

(2) Using the SQL statement statistical information to reduce the execution time of an SQL statement that creates a global work table

Procedure

Check the number of times files are written from the global buffer.

Check the values of the following items in the SQL statement statistical information that was output when the adbstat command was executed:
- DBbuff_name (global buffer name)
For the global buffer whose DBbuff_name is ADBWRK, check the following information:
- DBbuff_page_write_cnt (number of files written from the global buffer)
- DBbuff_page_put_cnt (number of files written to the global buffer)
Check whether a write from the global buffer to a file has occurred frequently.

If the relationship between the DBbuff_page_write_cnt value and DBbuff_page_put_cnt value, which you checked in step 1, satisfies the following formula, it is possible that files are being written from the global buffer frequently.

Formula
```
value output to DBbuff_page_write_cnt > value output to DBbuff_page_put_cnt ÷ 2
```
If the preceding formula is satisfied, go to step 3.
Terminate the HADB server.

Terminate the HADB server by executing the adbstop command.
Change the server definition.

Increase the value specified for the adb_dbbuff_wrktbl_glb_blk_num operand in the server definition.
Start the HADB server.

Execute the adbstart command to start the HADB server.
Re-execute the SQL statement that creates a global work table.

After re-executing the SQL statement that creates a global work table, perform step 1 to confirm that the value that was output to DBbuff_page_write_cnt does not satisfy the formula.
Repeat steps 1 to 6.

If you make adjustments by repeating steps 1 to 6 until the value that is output to DBbuff_page_write_cnt no longer satisfies the formula, you can sometimes reduce the execution time of the SQL statement that creates a global work table.

Note: For details about the adb_dbbuff_wrktbl_glb_blk_num operand in the server definition, see the adb_dbbuff_wrktbl_glb_blk_num operand in 7.2.2 Operands related to performance (set format).

To Page Top