Start of change

Altering tables to enable hash access

You can alter existing tables to take advantage of hash access organization and improve the performance of queries that access individual rows in a table.

Before you begin

Hash organization is only available on universal table spaces (UTS). If you want to enable hash access on a table space that is not already a UTS, you must first alter the table space to UTS.

About this task

Enabling hash access requires a table space reorganization, and disables some features such as index clustering.

Procedure

To alter an existing table to take advantage of hash organization:

  1. Specify ADD ORGANIZE BY HASH in the organization-clause of your ALTER TABLE statement.
    1. Specify UNIQUE followed by the column names for one or more columns that contain unique values in each row. You can specify more than one column-name as long as no two rows in the table have the same values in those columns. You can only specify columns that are defined as NOT NULL. You can specify a maximum of 64 columns to be used as unique identifiers for hash access. The sum of the column length attributes must not exceed 255. DB2® maintains the uniqueness of the hash key columns, and an index is not needed for this purpose.
    2. Specify HASH SPACE followed by an integer and a modifier that specifies the size of the hash space. You can specify the size of the hash space in kilobytes, megabytes, and gigabytes. Specify:
      • K for kilobytes
      • M for megabytes
      • G for gigabytes

      You can specify a size that is larger than your data to minimize the overhead of access to data that overflows the hash space. The size that you specify is most important if you do not intend to immediately reorganize the table space and specify the AUTOESTSPACE(YES) option, as is recommended below. In that case, for more information about choosing an appropriate size for the hash space, see Fine-tuning hash space and page size.

  2. Commit the ALTER TABLE statement.
  3. Run the REORG TABLESPACE utility on the table space where your altered table is located. If you specify AUTOESTSPACE(YES) in the REORG TABLESPACE statement,DB2 automatically estimates the best size for the hash space based on information from the real-time statistics tables. If you specify AUTOESTSPACE(NO) in the REORG TABLESPACE statement, DB2 uses the hash space that you specified.

Example

Begin general-use programming interface information.

Consider the following ALTER TABLE statement:

Start of change
ALTER TABLE EMP
ADD ORGANIZE BY HASH UNIQUE (EMPNO)
HASH SPACE 64 M;
End of change

In this example the user alters the EMP table, specifies to ADD ORGANIZE BY HASH, sets the EMPNO column as the unique identifier, and specifies a HASH SPACE of 64 with the modifier M for megabytes.

End general-use programming interface information.

What to do next

Monitor the real-time-statistics information about your table to verify that the hash access path is used regularly and to verify that the use of disk space is optimized.
End of change