DB2 for z/OS Query Parallelism: It's About More Than Response Time

Query CPU parallelism -- the ability to split a query into multiple parts that can be executed in parallel -- made its debut as a mainframe DB2 feature with Version 4 of the product, back in the mid-1990s. To this day, lots of mainframers think of query parallelism as they did when it was brand new: it's a nice way to reduce the elapsed time of what would otherwise be long-running queries in a data warehouse environment. That view is on the mark, but it's overly narrow. I'm here to tell you that query parallelism can be a means of significantly reducing the cost of computing for an operational DB2 workload (by which I mean one associated with run-the-business versus decision-support applications).

What I'm talking about became possible when IBM introduced zIIP processors for its System z servers. zIIPs -- more formally known as System z Integrated Information Processors -- are "specialty" engines that can be used in the execution of certain types of work on a mainframe system. System z users like zIIPs because they lower the cost of computing on the mainframe platform in a double-barreled way: first, they cost less than general-purpose mainframe processors; second, they do not factor into the pricing of software that runs on System z servers (add a general-purpose engine, and your software costs will go up; add a zIIP, and they won't).

At lots of mainframe sites, the utilization of zIIP engines (if they are configured on the System z servers) is driven almost entirely by client-server DB2 work (i.e., by SQL statements that are issued from off-mainframe application servers and routed to the DB2 for z/OS data server through the DB2 distributed data facility, aka DDF). These SQL statements execute under what are called enclave SRBs (SRBs -- short for service request blocks -- are a type of control block that can represent a dispatchable piece of work in a z/OS system). Enclave SRBs, in turn, can be dispatched to zIIP engines (and the zIIP-friendliness of DB2 client-server computing becomes even more pronounced in a DB2 9 environment, thanks to native SQL procedures, which can utilize zIIP resources when invoked through the DB2 DDF).

This is all well and good, but at more than a few sites the client-server DB2 for z/OS workload goes way down when the bulk of the online users go home at the end of the working day. What's left to do at night is a sometimes huge amount of batch work. These jobs may drive the general-purpose engines on a mainframe server to a utilization rate of 90% or more for hours at a time, while zIIP engines on the same box sit there are two or three percent busy. Wouldn't it be great if those zIIPs, with their large reserves of processing capacity, could be put to work running some of the nightly batch programs? WELL THEY CAN, and your ticket for doing that is -- you guessed it -- query parallelism.

You see, when DB2 splits a query, the different parts of the query execute under enclave SRBs, and that means that they can use zIIP MIPS. Queries in batch jobs can be good candidates for parallelization because they often scan lots of data. Making queries in a batch job eligible for parallelization is easy: assuming we're talking static SQL, you just rebind the program's package with a specification of DEGREE(ANY) versus the default of DEGREE(1) -- there's no need to change program code (for dynamic queries, parallelization is enabled by setting the value of the DB2 special register CURRENT DEGREE to 'ANY'). And, parallelization doesn't change the query's result: you'll get the same data back, at a reduced cost because lower-cost zIIP engines helped get the work done.

Want to get started with putting zIIPs to work with your nightly batch load? Here's what I'd do:

1. Identify your most expensive batch programs with respect to in-DB2 CPU time (also known as "class 2" CPU time). Tools from IBM and other vendors can help you here (an example of an IBM tool that could be used for this purpose is IBM Tivoli Omegamon XE Performance Monitor on DB2 for z/OS -- check out the accounting report and the TOP subcommand).
2. Pick one of these expensive programs, and run a test. First, see if DB2 would parallelize any of the program's queries if DEGREE(ANY) were in effect. One really easy way to do this is to use the free and downloadable IBM Optimization Service Center for DB2 for z/OS -- for a given query to be analyzed, you click on "Specify Query Context" and select ANY from the drop-down menu for "Current Degree" under "Application Environment." Keep this in mind: DB2 can parallelize parts of a query's execution even if no partitioned tables are accessed (while table partitioning does tend to be a driver of query parallelism, partitioning is not necessarily a prerequisite for parallelism).
3. If the access plan information obtained in step 2 above shows that DB2 would parallelize one or more queries in the batch program, run a test in a test environment (ideally one with data volumes approximating those in production, but if not that, at least one with catalog statistics that closely match those in production): execute the program as-is, then rebind it with DEGREE(ANY) and run it again. Use your DB2 performance monitor to see how much of the job's CPU time has shifted to a zIIP engine, and how much the job's general-purpose CPU consumption has decreased. Also see for yourself that the program's query results were not changed by parallelization (for example, FETCH counts should be identical if the program processes all rows in the query result sets).
4. Assuming a positive result from step 3 above, implement the change in production. Keep in mind that this may require modifying your standard program bind procedure, which may specify DEGREE(1) by default -- you don't want a subsequent rebind to inadvertently change DEGREE for the program's package back to 1.
5. Repeat steps 2 through 4 with another of the identified high-cost batch programs.

A few other things to keep in mind:

• In a DB2 subsystem that is not dedicated to a data warehouse workload, you may want to set the value the PARAMDEG parameter in the DB2 ZPARM module to a value other than the default of 0, which lets DB2 choose the degree of parallelization for a queries (i.e., the number of pieces into which a given query can be split). A value in the low-to-mid single digits (e.g., 4 or 5) might be a good choice -- you don't want too large a number of split queries in the system when you're running a high-volume batch workload.
• Keep an eye on the utilization of space in the DB2 EDM pool -- a package bound with DEGREE(ANY) will be larger than the same package bound with DEGREE(1).
• DON'T rebind ALL batch packages with DEGREE(ANY) in one fell swoop. Be systematic about this. Do a few at a time, and maybe pause when you get the level of zIIP engine utilization on the system where you want it to be.

If you have zIIPs on your mainframe(s), I hope you'll look into the parallelization possibilities to get more out of those lower-cost engines. If you don't have zIIPs because you think you have a workload that can't exploit them -- think again.

People who think of query parallelization only in terms of its potential to reduce query run times are missing another important benefit of the technology: it's a zIIP-utilizer that can save your organization money, with no need to change program code. Gotta like that.