Help

 

DSNIWKFL

Module DSNIWKFL creates temporary work files or logical sort work files. Temporary work files are used when the amount of data to be sorted will not fit into the memory sortwork space and the buffers in the bufferpool used by the sortwork files (DSN4Kxx).

Hints

Module DSNIWKFL appears with large sorts and view materialization. Temporary work files are used when the amount of data to be sorted will not fit into the memory sortwork space and the buffers in the buffer pool used by the sortwork files (DSN4Kxx). Sorts are caused by coding ORDER BY, GROUP BY, and UNION, as well as by merge scan joins and hybrid joins. The SORTN and SORTC columns of the PLAN_TABLE show the cause of the sort.

Evaluate the SQL using the EXPLAIN facility, and try to eliminate the requirement for sorting or joins done without proper indices

  • Recode the UNION as UNION ALL if duplicate rows are acceptable. A UNION clause invokes a sort to group and eliminates duplicate rows. UNION ALL does not require a sort.

  • Replace DISTINCT with GROUP BY to allow DB2 to use a non-unique index without invoking a sort. A DISTINCT clause invokes a sort unless a unique index exists for the columns against which the DISTINCT is applied.

  • Ensure that all local predicates are coded in an indexable format (see the Db2 Application and Programming Guide). Db2 will apply these predicates before performing the sort thereby reducing the number of pages on the sortwork file. If the first column in an index is included in the predicate or any column in the index has an equal predicate, that index may be used to avoid a sort.

  • Ensure that any existing index with a clustering sequence close to the sequence of the sort has been properly maintained. Db2 will attempt to use an index with a cluster ratio greater than 80% to avoid a sort.

  • Be sure to match the sequencing columns and predicates with the indexed columns. For example, the following statement will use a sort even though there is a clustering index on DEPT for TABLEX that could have been used to avoid the sort.

 

SELECT B.DEPT, B.DEPT_NAME

FROM TABLEX A, TABLEY B

WHERE A.DEPT = B.DEPT

ORDER BY B.DEPT

·   Consider adding columns to an existing index because all of the columns in the sequencing clause must be included in the index. For example

 

SELECT A, B, C FROM TABLEX

WHERE COL1 = :HV

ORDER BY A, B, C

This statement will not use an index to avoid the sort if only columns A and B are included in the index.

·   Specify only those columns needed to produce the desired ordered set, given the restraints supplied by column definitions. For example, given a unique index on DEPT, the following query will not use the index to avoid a sort, since not all of the columns included in the ORDER BY clause are included in the index.

 

SELECT DEPT, DEPT_NAME

FROM DEPT_TABLE

ORDER BY DEPT, DEPT_NAME

The column DEPT_NAME could be removed from the ORDER BY clause and the result set would be retrieved in the desired sequence using the index to avoid a sort since only one DEPT_NAME can occur within a department.

·   In the case of merge scan and hybrid joins, OPTIMIZE FOR n rows can be used to encourage the use of a nested loop join. However, only do so when the user does not expect to retrieve the entire result set.

·   Consider adding an index matching the sequence of the columns to be sorted.

When a large sort cannot be avoided, each row written to the work file includes all columns specified in the sort clause plus all columns selected. Because there will be some duplication the user should only sort those columns that are necessary as these keys appear twice increasing the sort row size. Avoid selecting VARCHAR columns since they are written to the work file at their maximum length. Large sort row sizes increase I/O requirements since the size of the sort row will determine how may fit on a single page.

System-wide sort efficiency depends on the size and placement of workfiles. All sortwork files should be associated with a single buffer pool. Consider using buffer pools other than BP0 to avoid competition with Db2 catalog tables. Spread these temporary tables across different volumes to distribute I/O across several devices.

Reference Sources

IBM Db2 for OS/390, Messages and Codes, GC26-8979

IBM Db2 for OS/390, Application Programming and SQL Guide, SC26-8958

IBM Db2 for OS/390, Administration Guide, SC26-8957