Monitoring hash access (deprecated)

You can optimize the performance of hash access in Db2 by monitoring and tuning the storage space that is used by hash access. Hash-organized table spaces are deprecated and likely to be unsupported in the future.

To optimize the use of hash space and improve single-row access on tables that are organized by hash:

• Compare the values for SYSINDEXSPACESTATS.TOTALENTRIES and SYSTABLESPACESTATS.TOTALROWS.
  If the TOTALENTRIES value is greater than 10 or 15 percent of the value of TOTALROWS, the data might need to be reorganized, or the size of the hash space might need to be increased. However, careful analysis of the row size, page size, and PCTFREE values might be needed to identify when to reorganize a particular hash-organized table.
• Compare the values for SYSTABLESPACESTATS.DATASIZE and SYSTABLESPACE.HASHSPACE.
  If DATASIZE is greater than HASHSPACE, increase the size of the hash space. The recommended size for hash spaces depends on the row size and page size. For more information about choosing a size of the hash space, see Managing space and page size for hash-organized tables (deprecated).
• Monitor SYSTABLESPACESTATS.REORGHASHACCESS to ensure that applications are using hash access paths on tables that are organized by hash.
  This value represents the number of times that a hash home page is accessed to locate a record for operations such as the following, regardless of whether a qualifying record is found:

  • SELECT
  • FETCH
  • Searched UPDATE
  • Searched DELETE
  • Enforcement of referential integrity constraints

  For partition-by-growth table spaces, the value is only increased for partitions that contain hash home pages. This value is always zero for partitions in a partition-by-growth table space that do not have hash home pages, even though pages in these partitions might be accessed through the hash overflow index.

  If the REORGHASHACCESS value does not increase after several queries have accessed the table, the hash access path is not being used regularly. Consider removing hash organization from such tables to conserve storage space. The value of REORGHASHACCESS resets to zero after the REORG TABLESPACE or LOAD REPLACE utility is run.

• Monitor SYSTABLESPACESTATS.HASHLASTUSED to ensure that applications have used hash access paths on the table recently. If the Db2 has not recently used a hash access path on the table, consider removing hash organization from the table to conserve storage space.