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?
DB2 Workfile scan
db2db2-zosoptimizationperformanceperformance-tuning
Related Question
- DB2 zOS increasing DSSIZE vs adding partition
- SQL Server – Overcoming MERGE JOIN with Explicit Single KEY Value on FOREIGN KEY
- DB2 for z/OS – Using Named Parameter Markers
- DB2 – Why Translate Function Replaces Spaces
- DB2 – Handling Multiple Rows with Same Value
- Sql-server – Accurately simulate SQL Server work load with diskspd
Best Answer
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.