DSNXSTM

Module DSNXSTM is the RDS Sort Storage Manager. It obtains, manages, and releases storage for sorting within the RDS area of the Data Manager address space. Memory sort space can be obtained for each user performing a sort, or as space is added to the RID Pool. Storage is obtained by issuing MVS Getmain instructions. These functions are quite resource intensive and should be avoided by reducing or eliminating sort requirements, or by adjusting base RID pool requirements by altering ZPARM options such as MINRBLKS, etc. This module is part of the code within the Data Manager address space.

Hints

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 EXPLAIN to evaluate the SQL, and try to eliminate the need 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 column 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 amount of data to be sorted. 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 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

· 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, do so only when the user does not expect to retrieve the entire result set.

· Consider adding columns to the 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.

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

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