The Magic of Keeping DB2 in Order

4 Pages

Sort invokes many thoughts and emotions—most in the “necessary evil” category. A discussion of sort also can head off into many different directions; it’s a subject you could discuss forever. For this article, the scope will be on only the internal sort for DB2 for z/OS, which is the process invoked to satisfy ordering in SQL statements; this is sometimes referred to as Relational Data Services (RDS) sort.

As for SQL, sort is one of those things you do whatever you can to avoid, though that’s an almost impossible task. You can create indexes in the hope DB2 will choose the index over a sort, but you can’t create indexes on everything. Indexes, even though they can do some wonderful things, also come with overhead. Sorts are simply sometimes required. For that reason, it could be to your advantage to understand some basics about sort so you can attempt to make them perform with minimal overhead.

Here, we’ll discuss three DB2 sort concepts that seem to generate the most questions: the sort pool size (SRTPOOL), defining the sort buffer pool database (DSNDB07 when not using data sharing), and the allocation of the physical Virtual Storage Access Method (VSAM) page sets that support the sort workfiles. This article has nothing to do with DFSORT or any other external sorts; we’re only examining the RDS sort, DB2’s internal sort process, so please don’t try to draw conclusions about the behavior or performance implications of an external sort from this article.

A Historical Perspective

Many years ago, we used to teach the details behind how sort worked. We’d explain how runs were created on input and written to work files, how they were later merged, the sort tree, the leaf nodes that compose a sort tree, and how a tournament sort came into play. Back then, we thought that was all important because you’d sometimes need to “tune” a sort to get it to perform adequately.

In 1991, DB2 Sort Assist arrived as part of DB2 Version 2 Release 3, which ushered in some significant software improvements as well as a couple of algorithm improvements and pathlength reductions. Other sort enhancements saw the work file page density increased, prefetch quantity increased, and restrictions on the number of available work files removed. All this translated into less need for sort tuning and the elimination of sort processing details from most DB2-related publications and teaching materials.

IFCID 0096, once used to determine if hardware sort assist was being used, still contains valuable information about each sort process that occurs in DB2. A 96 record is recorded at the end of every sort (an IFCID 0095 is recorded at sort start). In fact, IFCID 0096 is still one of the best places to look up sort key and sort field length information.

It remains advantageous to understand DB2 sort concepts, including sort pool size, so let’s consider the internal sort work area defined by the DSZPARM keyword SRTPOOL.

SRTPOOL

The size of the internal sort work area is defined by the subsystem parameter, SRTPOOL, a keyword on the DSN6SPRM macro if directly modifying the data-only subsystem parameter module DSNZPARMs. The value of 2MB is supplied (the panel says calculated) for the sort pool size field on the DSNTIPC installation panel. This is the default sort pool size; you can change it on panel DSNTIPC during installation.  What value should be chosen for the sort pool size is the real question. Should the value be calculated, should you simply take the default, or is there some other recommended value?

4 Pages