August 31, 2010

Sorting Keeps Getting Faster

Years ago when I was a DB2 database consultant, I went into a company that had turned off all ability to sort in DB2. The ORDER BY clause of the SQL statement couldn't be used because no DSNDB07 sort work was defined. I was told this was done because DB2 sort was so slow and resource-intensive.

Just imagine if this company still performed sorting this way -- and think of what they'd miss out on by maintaining an old, costly system incapable of taking advantage of new technology such as DB2 Sort for z/OS.

Sorting has come a long way, but IBM continues to enhance it, as evidenced by the recently introduced DB2 Sort for z/OS V1.1. The updated solution provides high-speed sort processing for data stored in DB2 for z/OS.

As IBM notes in the Features and Benefits section, DB2 Sort for z/OS V1.1 provides:
• The capability to offload work to zIIP engines to lower the billable CPU time associated with sorting. The offload may occur if the key length is greater than 80 percent of row length with file sizes of up to 2.5GB, or with key lengths less than or equal to 80 percent of row length and file sizes up to 5GB.
• Optimization on specific I/O devices to utilize the best I/O transfer technique. DB2 Sort monitors the I/O transfer rates and, if performance objectives are not being met, adjusts the use of devices to balance the I/O load and achieve the best elapsed time.
• Optimization of central storage use by determining the availability of system resources and selecting the appropriate amount of storage to allocate. DB2 Sort employs memory objects and data space to avoid DASD access when possible to reduce elapsed time.
• Dynamic allocation of intermediate work space to minimize the amount of DASD space used. DB2 Sort helps avoid sort capacity errors caused by an insufficient amount of work space and provides a better allocation of intermediate work space, thus conserving the resource as well as the CPU usage that would be spent in the allocation.
• Synergy with the IBM DB2 Utilities to determine the optimal number of sorts to run in parallel. This is referred to as "intra-regional" parallelism. DB2 Sort assesses the availability of system resources and communicates with the utility to optimize the allocation of resources for each sort based on the data characteristics. By choosing the most effective use of resources for each sort, DB2 Sort for z/OS can increase the number of sorts that can be run simultaneously. This higher degree of parallelism results in lower elapsed time for utility processing.

If you've used DB2 Sort for z/OS, please post your feedback in Comments.