Q and A

Asked and Answered

I've been looking into DB2 Query Parallelism and have tested I/O, CP, and Sysplex QP with the following results:

• Elapsed time reduced 50 to 60 percent.
• Increasing the size of parallel and assisting parallel buffers had little effect. Decreasing the size eventually caused DB2 to turn off QP.
• After a certain point, the number of parallel tasks gave a diminishing return.

My problem: When I traced the Sysplex Parallel queries using a third party monitor, it reported the CPU time correctly for the assisting members, but was way off the mark for the coordinating member (no roll up record). I calculated the correct CPU time by way of IFCIDs, so I know the information is out there. Does DB2 Performance Monitor accurately report this field? Also, I've heard "advice" saying that you shouldn't set MAX DEGREE greater than the number of CPs. I don't agree, but I'm wondering whether setting MAX DEGREE high will have an adverse effect on other users. Any advice?

Robert Catterall responds:

We don't use sysplex query parallelism at CheckFree, so I can't give you an experience-based answer; however, the DB2PM report reference (http://publib.boulder.ibm. com/cgi-bin/bookmgr/ BOOKS/FPERRA13/ CCONTENTS) does indicate that CPU time from parallel tasks running on assisting members is included in the one "logical" accounting trace record. Note that this CPU time appears to be included only when using DB2PM in batch reporting mode — if you're using DB2PM's online monitoring capability, I believe you will see CPU time only for tasks running on the data sharing member to which you're attached.

I personally wouldn't want to restrict the degree of query parallelism to the number of CPs unless I had to. I might have to if I have a number of concurrently executing queries that all get split by DB2, but even in that case, you figure that the down side is having a more-than-optimal number of dispatchable tasks active on the system. My feeling is that OS/390 does a very good job of managing a large number of concurrently active tasks — performance might degrade somewhat, but I wouldn't expect it to drop through the floor. Perhaps the problem with lots of tasks would be buffer pool thrashing. This might be indicated by a fairly high number of synchronous reads driven by tasks that should be prefetching (SYNCHRONOUS READS - SEQUENTIAL in the buffer pool activity section of a statistics report). In that case, if you can't enlarge your buffer pool configuration, you might want to put a cap on the degree of parallelism.

See a complete archive of reader/author Q&As.