Sunday, March 4, 2012

Faster, More Efficient, More Resilient DB2 Utility Sorting with DB2 Sort

If you're a mainframe DB2 person, there's a good chance that you've heard of an IBM software offering called DB2 Sort. If your knowledge of DB2 Sort doesn't extend much  beyond "I've heard of it," you may have this vague idea that the product speeds up IBM DB2 for z/OS utility sort processing. You'd be on target there, but utility speed-up is only part of the DB2 Sort story. On top of that, your thinking as to how DB2 Sort accelerates DB2 utility execution may be off the mark. With this blog entry I want to clear up a few misconceptions about DB2 Sort that I think exist to some extent within the DB2 for z/OS user community. Additionally, I'll review recent developments with respect to DB2 Sort in particular, and IBM DB2 utility performance in general.

On to clarifications.

Misconception #1: DB2 Sort reduces utility elapsed and CPU times by optimizing DB2's use of DFSORT. You probably know that, starting with Version 8, DB2 for z/OS exclusively uses IBM's DFSORT as the base for accomplishing utility-related sorts. Over time, DB2's use of DFSORT has been enhanced, but DB2 Sort is not part of that story. Instead, it is an alternative sorting tool for DB2 -- something that DB2 can call instead of DFSORT to execute sorts as needed for utility processing (the DB2 utilities that leverage DB2 Sort are the most sort-intensive in the DB2 Utilities Suite: REORG, LOAD, REBUILD INDEX, RUNSTATS, and CHECK DATA/INDEX/LOB). While DFSORT is an outstanding general-purpose sort product, DB2 Sort is truly specialized software -- highly tuned to the unique requirements of DB2 utility sort operations. When an organization's demands with regard to DB2 utility performance present a challenging target, DB2 Sort can provide the means of clearing the bar.

A little backstory: not too long ago, IBM decided to team with a long-established leader in sort technology to develop a product that would conform especially closely to the particular characteristics of sort processing in a DB2 utility context. Developers from this partner company worked with IBM DB2 utility developers to turn concept into reality, and DB2 Sort is the result of that collaboration -- a collaboration, by the way, that is ongoing.

Misconception #2: The efficiency impact of DB2 Sort is strictly a CPU thing. DB2 Sort does indeed have a positive impact on DB2 utility CPU consumption, but hardware resource utilization optimization goes beyond that effect. Take disk space, for example: having enough of this for sort work is important if a DB2 utility is to perform well (and indeed, if it is to run to completion), but as disk vendors don't give their wares away you'd prefer not to have to over-allocate space to get the results you want. DB2 Sort has an advanced dynamic allocation feature that delivers a "space on demand" capability, providing a right-sized disk resource to get a utility going, and compensating -- if needs be -- for extra requirements by allocating additional space as needed to keep utility processing on track.

Then there's system memory. When DB2 Sort is performing work for a utility, it is aware not only of what it's doing for that particular utility, but of what other concurrently executing utilities are doing -- and are planning on doing -- in terms of sort processing. DB2 Sort will balance sort-related utilization of system memory across batch address spaces to help avoid (for example) excessive paging activity that could otherwise negatively impact the performance of DB2 utilities (and application programs).

And while I'm on the topic of hardware utilization optimization, I'll remind you that boosting CPU efficiency is not just a matter of reducing overall CPU utilization (though DB2 Sort does that) -- it's also about shifting work from general-purpose mainframe processors to less-expensive zIIP engines. Use DB2 Sort with your DB2 utilities, and you'll see a greater degree of work-offload to zIIPs (assuming that your system has zIIP engines).

Misconception #3: DB2 Sort is just about DB2 utility performance. You want your DB2 utilities to finish faster, but you also want them to finish, period. DB2 Sort can boost the resiliency of DB2 utilities, allowing more utility jobs to run to completion. One way in which DB2 Sort delivers here is through retries of dynamic disk space allocation actions: if an allocation request fails, DB2 sort can wait a while (a couple of minutes, for example) and then retry the allocation action. If the initial allocation request failed due to a temporary tie-up of resources, the retried allocation could succeed. When more DB2 utility jobs complete successfully, the organization benefits, and DBAs on "pager duty" get more restful nights.

Recent developments on the DB2 Sort front: Over time, the list of DB2 tools that can utilize DB2 Sort for improved performance has expanded beyond the IBM DB2 Utilities Suite. Among the latest additions are DB2 High Performance Unload 4.1 (with the fix for APAR PM41087), the DB2 Utilities Enhancements Tool 2.2 (specifically, the LOAD presort feature), and DB2 Log Analysis Tool 3.3. What tools will be next in gaining DB2 Sort exploitability? Stay tuned.

DB2 Sort adds speed and CPU savings to DB2 utilities that were already getting faster and more efficient. DB2 9 for z/OS provided big-time performance improvements for a number of DB2 utilities, especially those that process index keys: CPU consumption for some RUNSTATS INDEX, REORG INDEX, and CHECK INDEX jobs could be 50% less in a DB2 9 versus a DB2 Version 8 environment. Other utilities such as LOAD, RECOVER INDEX, and REBUILD INDEX can also execute with substantially better CPU efficiency in a DB2 9 system (organizations going from DB2 V8 directly to DB2 10 will see these utility performance benefits in the DB2 10 environment). Considering these performance gains along with the boost that DB2 Sort can deliver might get you to thinking about some things.

Have you looked at the IBM DB2 utilities lately? You ought to. While you're at it, check out DB2 Sort. Could be a nice fit for your organization.

No comments:

Post a Comment