How sort work files are allocated

The work files that are used in sort are logical work files, which reside in work file table spaces in your work file database (which is DSNDB07 in a non data-sharing environment).

The sort begins with the input phase, when ordered sets of rows are written to work files. At the end of the input phase, when all the rows have been sorted and inserted into the work files, the work files are merged together, if necessary, into a single work file that contains the sorted data. The merge phase is skipped if only one work file exists at the end of the input phase. In some cases, intermediate merging might be needed if the maximum number of sort work files has been allocated.

Db2 uses the buffer pool when writing to the logical work file. Only the buffer pool size limits the number of work files that can be used for sorting.

A sort can complete in the buffer pool without I/Os operations. This ideal situation might be unlikely, especially if the amount of data being sorted is large. The sort row size is actually made up of the columns being sorted (the length of the sort key) and the columns that the user selects (the length of the sort data). A large buffer pool for sort activity can help you to avoid disk I/O operations.

When your application needs to sort data, Db2 tries to allocate each sort work file on a table space with specific attributes. Start of changeFL 508 The order of preference used is based on the WFDBSEP subsystem parameter value:End of change

NO
Db2 attempts to direct work file operations based on the preferred table space allocation attributes for the type of work file operation, as described in the following table. Start of changeFL 508 A partition-by-growth (PBG) table space that was created with FOR SORT is never used for DGTT processing.End of change
Work file operation Preferred table space attributes
DGTT work
  1. Start of changeFL 508 PBG table space created with or without FOR DGTTEnd of change
  2. Segmented (non-UTS) table space with a non-zero SECQTY value
  3. Db2-managed segmented (non-UTS) table space with SECQTY 0
  4. User-managed table space with any SECQTY value
Sort work
  1. Optimal least-used table space with any of the following attributes:
    • Start of changeFL 508 PBG table space created with FOR SORTEnd of change
    • Segmented (non-UTS) table space with SECQTY 0
    • User-managed table space with any SECQTY value
  2. If no table spaces with preferred attributes for sort operations are available, Db2 reverts to using a table space that is preferred for DGTT operations.
YES

Db2 attempts to direct work file operations only to table spaces with the preferred allocation attributes in the following table. When YES is specified and no table space is available with the preferred attributes, Db2 issues an error message, a negative SQLCODE, or both.

Work file operation Preferred table space attributes
DGTT work
  1. Start of changeFL 508 PBG table space created with or without FOR DGTTEnd of change
  2. Segmented (non-UTS) table space with a non-zero SECQTY value
Sort work

  1. Optimal least-used table space with any of the following attributes:

    • PBG table space that was created with FOR SORT
    • Segmented (non-UTS) table space with SECQTY 0
    • User-managed table space with any SECQTY value

When table spaces that have the preferred attributes are not available, the action taken depends on the value of the WFDBSEP subsystem parameter. If the value is YES, the sort operation fails. If the value is NO, another available table space is selected. For more information, see SEPARATE WORK FILES field (WFDBSEP subsystem parameter).

After the selection based on the table space attributes, Db2 allocates the work files based on the overall record length. When the record length (data + key + prefix) is greater than 100 bytes, Db2 attempts to create the work file in a table space with 32 KB page size. If the record length is 100 bytes or less, Db2 prefers a table space with the 4 KB page size.

Finally, the least recently used table space that has the preferred attributes is selected.