DB2 Workfile scan

Question

We just migrate from LUW to zos and we're finding that in z/os the accesspath explains show new node named WFSCAN (Workfile scan) and this rises when there is a join, group by or any kind of sort. The question is, Is any performance issue with WFSCAN. Should I be worry about the size of the pool it uses or any other costs in production environment? What should I do for better performance of Workfile Scans?

Answer 1

Any sort operation would use a work file and, obviously, a subsequent work file scan. You can try to avoid sorts by adding appropriate indexes. If you cannot avoid sorts, try to allocate work file database buffer pool such that it can fit the entire work file, thus eliminating physical I/O, as suggested in the manual.