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 that has the following attributes:
  • Is a segmented (non-universal) table space.
  • Has no secondary allocation (SECQTY = 0), or is a user-managed table space, regardless of secondary space allocation.

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.

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.