GLYPH<SM590000> Up to 50% reduction in CPU and elapsed time for queries that benefitted from increasing  the SRTPOOL size ZPARM in Db2 12. The largest improvements were for queries that can  now complete their group by or distinct processing in one sort pass because of the larger  sort tree size.  Note:  The domain of queries that benefit from this Db2 12 enhancement can be increased  by increasing the size of the sort pool (SRTPOOL ZPARM). The next sort enhancement is related to reducing the length of sort rows for internal sorts.  Reducing the sort row length includes the benefit that it also reduces the total amount of  memory and work file space needed for sort. It is common that predicates that are coded in the WHERE clause are also (often  redundantly) included in the SELECT list. Any redundancy in the sort key columns or data  row columns (SELECT list) has a negative effect on sort performance and resource  consumption.  ORDER BY sort already removes columns from the sort key if they are covered by equal  predicates in the WHERE clause. DISTINCT or GROUP BY already remove redundant  columns to allow for sort avoidance.  However, if a sort cannot be avoided for DISTINCT or GROUP BY, such redundant columns  remain as part of the row to be sorted in Db2 versions before Db2 12. Starting with Db2 12,  these redundant columns are removed from the sort key, as shown in Figure 8-16. Because C1 has an equal predicate in the WHERE clause, all sorted rows are ensured to  contain that same value; therefore, only column C2 is needed for the (DISTINCT) sort. Similarly, sort avoids duplicating the sort key from the data portion in Db2 12 if they match  each other.  Sort operates on a fixed-length concatenated key of the columns that are required to support  the ORDER BY, GROUP BY, DISTINCT sort, or sort for a join operation. The requested order  of the sort key might not match the order of the columns in the SQL statement’s SELECT list.  When the sequence of columns in the sort match the leading columns in the SELECT list and  contains only fixed-length columns in Db2, sort does not replicate the key. Instead, it uses the  data portion for the sort, which can result in a dramatic reduction in the length of the sort row. 276  IBM Db2 12 for z/OS Performance Topics Figure 8-16 Removal of redundant columns from the sort key 8.5.2 Reducing the length of the sort rows