Help

 

DSNITLPE

DSNITLPE is part of the Data Manager component of Db2 and is involved in locating temporary workspace data pages.

Hints

Module DSNITLPE appears with large sorts and view materialization. Db2 uses temporary work files 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.

Use the EXPLAIN facility to evaluate the SQL, and try to eliminate the need for sorting or joins performed 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 if 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.

  • Match the sequencing columns and predicates with the indexing 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 the index must include all columns in the sequencing clause.

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 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, use OPTIMIZE FOR n ROWS to encourage the use of a nested loop join. Do so 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, 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 many fit on a single page.

System-wide sort efficiency depends on the size and placement of work files. All sort work files should be associated with a single buffer pool. Consider avoiding competition with Db2 catalog tables by using a buffer pool 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, Administration Guide, SC26-8957