Help

 

DSNIGWAC

Module DSNIGWAC obtains an available pageset (tablespace) from the temporary Workfiles (Sort Work - DSN4K... or DSN32K..). An application that requires temporary workspace or space for sorting will initially acquire one or more physical tablespaces from the system specified DSN4Kxx pool. DSNIGWAC is part of the Data Manager code within the Data Manager address space.

Hints

Module DSNIGWAC appears with large sorts and view materialization. Temporary workfiles 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 coded 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. These predicates, however, must be in an indexable format.

  • 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 which 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 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, do this only 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. Since there will be some duplication, sort only the required columns, since these keys appear twice, increasing the sort row size. Avoid selecting VARCHAR columns, because 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 work files. All sortwork files should be associated with a single buffer pool. Consider avoiding competition with Db2 catalog tables by using buffer pools other than BP0. 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, Installation Guide, GC26-8970