Monitoring work file data sets

You should monitor how work files use devices, both in terms of space use and I/O response times.

About this task

Work file data sets are used for sorting, for materializing views and nested table expressions, for temporary tables, and for other activities. Db2 does not distinguish or place priorities on these uses of the work file data sets. Excessive activity from one type of use can detract from the performance of others.

When a temporary table is populated using an INSERT statement, it uses work file space.

No other process can use the same work file space as that temporary work file table until the table goes away. The space is reclaimed when the application process commits or rolls back, or when it is deallocated, depending which RELEASE option was used when the plan or package was bound.

Procedure

  • To monitor storage usage by work files:
    • Set subsystem parameter WFSTGUSE_AGENT_THRESHOLD to alert you when an agent has consumed more than a given percentage of space in the work file database. Set WFSTGUSE_SYSTEM_THRESHOLD to alert you when all agents on a Db2 subsystem or data sharing member have consumed more than a given percentage of space in the work file database.

      If you separate work file data sets for declared temporary tables from work file data sets for other uses by setting subsystem parameter WFDBSEP to YES, you can monitor space usage separately for work file data sets for declared temporary tables and work file data sets for other uses. In this case, Db2 issues separate alerts for space usage by declared temporary table work files and for other work files.

      Performance trace for IFCIDs 0342 and 0343 data can be used to monitor the workfile space usage.

    • Run a statistics class 1 trace. IFCID 0002 data contains information about the amount of storage that is being used in the work file data base, and how that storage is being used.
  • To monitor work file usage by temporary tables:
    • Keep work files in a separate buffer pool.
    • Run a performance class 8 trace. IFCID 0311 data can be used to monitor the use of declared temporary tables.