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 in work files that span more than one table space, define multiple table spaces in the work file database that have the preferred attributes.

  The following table shows how Db2 chooses the preferred attributes:

  Work file operation	Preferred table space attributes
  DGTT work	A Db2-managed (STOGROUP) partition-by-growth (PBG) table space (with any SECQTY value) A segmented (non-UTS) table space with a non-zero SECQTY value
  Sort work	A Db2-managed segmented (non-UTS) table spaces with a zero SECQTY value A user-managed table space, regardless of secondary space allocation

  For processing that can span more than one table space, Db2 prefers to allocate space for work files in table spaces that have the preferred attributes. By creating multiple work file table spaces that have these attributes, you can support efficient concurrent read and write I/Os to the work files.

  When a table space in the work file database is stored in user-managed data sets, Db2 does not detect whether any secondary allocation exists. So, such table spaces are given the same preference as table spaces that have the preferred attributes, even when the table space has a secondary allocation.

  Processing that uses work files and can span more than one table space includes objects and operations such as:

  • Large concurrent sorts and single large sorts
  • Created temporary tables
  • Some merge, star, and outer joins
  • Non-correlated subqueries
  • Materialized views
  • Materialized nested table expressions
  • Triggers with transition variables
• 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.