5.7.1 Hash grouping

(1) Local hash grouping

First, a hash table is created for each SQL processing real thread and grouping is performed. Next, the results grouped by SQL processing real threads are collected, and then the entire data is grouped. This processing method is called local hash grouping.

A hash table is created for each SQL processing real thread in the hash grouping area. The size of a hash grouping area per hash table is specified in the adb_sql_exe_hashgrp_area_size operand in the server definition or the client definition.

To Page Top

(2) Global hash grouping

A hash table to be shared among multiple SQL processing real threads is created, and then grouping is performed. This processing method is called global hash grouping.

A hash table is created in the hash table area. The size of the hash table area is specified in the adb_sql_exe_hashtbl_area_size operand in the server definition or the client definition. Note that when 0 is specified in the adb_sql_exe_hashtbl_area_size operand, global hash grouping is not applied.

If an SQL statement containing the DISTINCT set function is executed, global hash grouping might be applied to eliminate duplicate retrieval results.

■ Action to take when the hash table area has insufficient space

When the hash table area has insufficient space, the data stored in the hash table is spread over multiple work tables. This results in SQL statements taking longer to process. To remedy a situation in which the hash table area has insufficient space, increase the value specified for the adb_sql_exe_hashtbl_area_size operand in the server definition or the client definition. This operand specifies the size of the hash table area.

When a work table is created due to insufficient hash table area, the KFAA51130-W message is output to the server message log file.

■ Flow of processing when hash table area has insufficient space

The following explains the flow of processing when the hash table area has insufficient space to create the hash table.

1. If there is insufficient space in the hash table area when creating the hash table, HADB creates multiple work tables. The data stored in the hash table is spread across these work tables.

   

2. HADB performs grouping while creating the hash table for work table 1.

   

   When this grouping has finished, HADB performs grouping while creating the hash table for work table 2. It then performs the same processing for work table 3.

   If the hash table area runs out of space when creating the hash table for a work table, HADB creates a new work table. The data that did not fit in the hash table area is stored in the new work table. In this situation, additional grouping using the newly created work table takes place.

   

To Page Top