Creating additional work file table spaces to reduce contention

You can minimize I/O contention in certain situations by creating additional work file table spaces.

To fine-tune the combination of different types of tables in the work file database, use the following approaches:

• In query parallelism environments, ensure that the number of work file disk volumes is at least equal to the maximum number of parallel operation use for queries in a given workload, place these volumes on different channel or control unit paths, and monitor the I/O activity for the work file table spaces.
  1. Place the volumes on different channel or control unit paths.
  2. Monitor the I/O activity for the work file table spaces.
     You might need to further separate this work file activity to avoid contention.
  3. As the amount of work file activity increases, consider increasing the size of the buffer pool for work files to support concurrent activities more efficiently.
     The general recommendation for the work file buffer pool is to increase the size to tune the following buffer pool statistics:

     • MERGE PASSES DEGRADED, which should be less than 1% of MERGE PASS REQUESTED.
     • WORKFILE REQUESTS REJECTED, which should be less than 1% of WORKFILE REQUEST ALL MERGE PASSES.
     • Synchronous read I/O, which should be less than 1% of pages read by prefetch
     • The sequential prefetch quantity, which should be:
       • 8 for 4 KB work file buffer pools
       • 2 for 32KB work file buffer pools
       You can calculate the sequential prefetch quantity by dividing the number of pages read by sequential prefetch (QBSTSPP) by the number of sequential prefetch reads (QBSTPIO).
• If your applications require extensive use of temporary objects or operations that can be processed only in a single table space, define some table spaces in the work file database that have the following preferred attributes:
  • Partition-by-growth (regardless of the SECQTY value) or segmented (non-UTS) table spaces that are not partitioned and have a non-zero SECQTY value.
  • Stored in Db2-managed data sets.

  Db2 gives preference for processing that cannot span more than one table space to table spaces that have the preferred attributes. The table spaces that have the preferred attributes help to minimize contention for space between temporary objects or operations that can span multiple tables spaces, and those that cannot.

  Processing that uses work files and is limited to a single table space includes objects and operations such as:

  • Declared global temporary tables
  • Scrollable cursors
  • SQL MERGE statements
• Ensure that the work file database contains at least one 32-KB page size table space before you create declared global temporary tables.
  The rows of declared global temporary tables reside in a table space in the work file database, and Db2 does not create an implicit table space for the declared global temporary table.
• To further improve performance, consider allocating more 32-KB data sets.
  Db2 uses 32 KB work file data sets when the total work file record size, including record overhead, exceeds 100 bytes, resulting in better performance and reduced work file buffer pool and disk space usage for work files, in some cases.