Enterprise Tech Journal Winter 2015-16 : Page 44

If I Have a DB2 Analytics Accelerator, Do I Still Need zIIPs? By Willie Favero A few days ago I was asked a very interesting question, something that I had never really given much consideration to before: “If I have the DB2 Analytics Accelerator, do I still need all of those zIIPs? After all, my queries are running on the Accelerator now, correct?” At first, I thought this was a straightforward and easy question to answer, with a, “Sure you do.” However, as I thought about how to respond, I realized the answer isn’t actually that simple. If ever there was a question deserving one of our, “It depends” answers, this had to be one of them. In my opinion (and remember, everything I write about is JUST my opinion and only my opinion), there can sometimes be confusion about exactly what the purpose of these two components are, when they are utilized and do you need them both. After all, they have both been around for a while now, with zIIPs being introduced way back in 2006 and the current iteration of the DB2 Analytics Accelerator arriving in 2011 (the first DB2 Analytics Accelerator actually made its first appearance in 2010). Since the basics of both are not explained that often anymore, it may be possible that in some situations the end goal of using these two technologies may on occasion get interchanged. My intent today is rather than jumping into a simple explanation, I’d like to review them both to remove the possibility of any ambiguity or confusion. What makes this question interesting, and giving a simple answer challenging, are the number of caveats that exist around any answer from me. Definitions of Terms and Acronyms • A PU (processor unit) chip can be characterized as CP or central processor (sometimes referred to as a general purpose or GP processor). • A zIIP , or an IBM z Systems Integrated Information Processor, is an IBM specialty engine (I always have to define those acronyms). Specialty engines are the same hardware as general purpose processor (GP) with the same characteristics and speed as a full capacity CP. However, specialty engines can only run specific workloads such as I/O on a specialty assist processor and Linux on an Integrated Facility for Linux (IFL). Since only specific workloads are run on those processors, IBM can price those workloads very efficiently. Thousands of applications can share a general processor, which makes general processors very cost-effective for virtualized workloads. A processor 4 4  •  E n te rp r i s e Te c h J o u rn a l  • W in t e r 2 01 5 / 2 01 6

If I Have a DB2 Analytics Accelerator, Do I Still Need zIIPs?

Willie Favero

A few days ago I was asked a very interesting question, something that I had never really given much consideration to before: “If I have the DB2 Analytics Accelerator, do I still need all of those zIIPs? After all, my queries are running on the Accelerator now, correct?”

At first, I thought this was a straightforward and easy question to answer, with a, “Sure you do.” However, as I thought about how to respond, I realized the answer isn’t actually that simple. If ever there was a question deserving one of our, “It depends” answers, this had to be one of them.

In my opinion (and remember, everything I write about is JUST my opinion and only my opinion), there can sometimes be confusion about exactly what the purpose of these two components are, when they are utilized and do you need them both. After all, they have both been around for a while now, with zIIPs being introduced way back in 2006 and the current iteration of the DB2 Analytics Accelerator arriving in 2011 (the first DB2 Analytics Accelerator actually made its first appearance in 2010). Since the basics of both are not explained that often anymore, it may be possible that in some situations the end goal of using these two technologies may on occasion get interchanged. My intent today is rather than jumping into a simple explanation, I’d like to review them both to remove the possibility of any ambiguity or confusion. What makes this question interesting, and giving a simple answer challenging, are the number of caveats that exist around any answer from me.

Definitions of Terms and Acronyms

• A PU (processor unit) chip can be characterized as CP or central processor (sometimes referred to as a general purpose or GP processor).

• A zIIP, or an IBM z Systems Integrated Information Processor, is an IBM specialty engine (I always have to define those acronyms). Specialty engines are the same hardware as general purpose processor (GP) with the same characteristics and speed as a full capacity CP. However, specialty engines can only run specific workloads such as I/O on a specialty assist processor and Linux on an Integrated Facility for Linux (IFL). Since only specific workloads are run on those processors, IBM can price those workloads very efficiently. Thousands of applications can share a general processor, which makes general processors very cost-effective for virtualized workloads. A processor repurposed as a zIIP will only run specific workloads, some of which are outlined below.


On a z13, a zIIP will run at the same speed as a full-capacity processor on a 2964-701.

• A zAAP, or an IBM z Systems Application Assist Processor, is another IBM specialty engine. zAAPs will be going away in the near future with zAAP eligible workload now being redirected to zIIPs.

• Accelerator refers to the IBM DB2 Analytics Accelerator for z/OS.

• LSPR: IBM Large System Performance Reference

• MSU: (Millions of Service Units per hour) used as a capacity metric only

• MIPS: Millions of instructions per second

• ITR: internal throughput rate

• A complete listing of IBM z Systems LSPR ITR ratios current as of January 2015.

Since the zIIP specialty engine arrived first, let’s start with it.

The primary purpose of a zIIP is to help reduce your z Systems software cost. Software charges for many mainframe products are based on CPU consumption. The more CPU used, the greater the increase in your software cost. However, by installing one or more zIIP specialty engines, some of the MIPS (or maybe it should be BIPS or billions of instructions per second now) that would have been used with the CP, can be redirected to the zIIPs. When the software is redirected to run on a zIIP rather than CP, the processors used by that redirected software are not included in the total MSU rating nor does it affect the machine model designation. When the MSU or model doesn’t change, the software charges do not increase.

Note: In a perfect world, as much as 60 percent of the distributed SQL work and up to 80 percent of all parallel child SQL activity could be redirected to a zIIP.

On the other hand, a DB2 Analytics Accelerator is designed to run SQL queries. Queries are offloaded to an Accelerator to improve the performance of those queries by reducing their elapsed times; they run faster. In fact, a query could realize up to a 2000X improvement in processing time if run on the Accelerator. As an example, a query that may have taken over an hour to complete may potentially complete in about 2 seconds if that query ran on the Accelerator.

In the simplest and most basic comparison, the DB2 Analytics Accelerator improves a query’s performance or time to completion, while a zIIP specialty engine reduces software cost. Although the previous comparison is technically accurate, that comparison doesn’t actually describe all the potential ways both may improve performance, save you CPU cycles and reduce CPU cost. I hope to explain in the next few paragraphs how both cross design lines by examining some of the other “benefits” of both.

Let’s forget about the basic comparison and move onto the, “It depends” scenarios.

You don’t add a zIIP to improve performance. It’s a cost thing, correct? Not always. The cost to add a zIIP into a processor configuration is considerably less than the cost to add a CP. If a less costly zIIP is added, it is possible that the increase in physical processors may improve your overall performance. As an example, let’s look at just the MIPS available. On a 2964-401, the smallest z13 sub-capacity configuration, there is one processor with approximately 250 MIPS available. If a second processor is added, making our machine a 2964-402, we will have about 478 MIPS. Not only will going from a 401 to a 402 be costly, it also will increase the MSU value, thus increasing the cost of most software running on that machine. However, if a zIIP was added to the 401 machine’s sub-capacity configuration, MIPS are increased to approximately 1,837 with NO change to the MSU value. The result is more processing power without affecting software cost. More MIPS could result in improved performance. More processors could also improve performance. In the case of DB2’s parallel processing where more processors can improve parallel processing performance, there are now two processors for DB2 to use. With the single processor configuration, parallelism would not even be considered.

In fact, in today’s setups, two zIIPs can be configured for every one CP. Using the same sub-capacity example given above, a 401 plus two zIIPs would be about 3,328 MIPS with the same MSU as a 401. However, a model 403 (three CPs) would only be 697 MIPS with almost triple the MSU rating and a considerably higher software cost impact.

Note: All MIPS quotes are estimations being used for comparison purposes only, not as statements of performance.

Note: All of my MIPS calculations above are based on 100 percent zIIP utilization, a utilization value much too high and a bit risky for an actual practical zIIP implementation.

That describes the potential two sides of a zIIP. What about the Accelerator? Is it just for performance? Again, this is not always true. Long-running, complex SQL queries can have quite the impact on CPU usage. However, if that complex SQL query is offloaded to the Accelerator, it is no longer running in DB2 on the z/OS side. Any of the MIPS that might have been necessary to process that SQL are moved to the Accelerator, off of the mainframe. So, although the intent is to improve a query’s performance, by moving the query to the Accelerator, you are, in fact, reducing the CPU impact on your mainframe.

Things get even fuzzier when you consider that the complex and expensive SQL that’s being offloaded to the Accelerator was also probably a great candidate for redirection to a zIIP. This brings us back to our original question, “Do we still need all of those zIIPs if an Accelerator is being utilized?”

In my opinion (notice the word “opinion” again), the answer is probably closer to a “yes.” Fortunately for zIIPs, they can be used for a lot more than just a place to redirect query processing. Between DB2 and z/OS, there are quite a few other functions that can also take advantage of a zIIP specialty engine. There could still be ample places for you to save MIPS by having zIIPs installed, even in addition to an Accelerator.

When SQL is discussed, not all SQL will get offloaded to the Accelerator. Yes, you expect complex SQL to be offloaded, but what happens to those queries that are still complex but are not read-only, that have a FROM clause that references a FINAL TABLE or OLD TABLE, the query contains a correlated table expression, the query references a wrong data type, the query is recursive, it references a special register that’s not allowed and the list goes on and on? There are lots of SQL choices that can be made that will exclude that query from being offloaded to an Accelerator. However, even if the query doesn’t qualify for the Accelerator, it may still be eligible for redirection to a zIIP, assuming a zIIP is available and has the capacity to process the query.

Let’s not forget the simplest case where a zIIP would be handy because the Accelerator can’t help out. No matter how complex, how slow, how time-consuming or how much of a CPU hog a query might be, if one or more of the tables that query requires to execute doesn’t exist on the Accelerator, there is absolutely no way that query can run there. In that case, if the query can use parallelism or has arrived from a distributed source, a zIIP would come in real handy.

Regardless of your Accelerator or zIIP usage, zIIPs are used for far more than redirecting the cost of running SQL. Here’s a list of some functions that could potentially be redirected to a zIIP that would never run (or not be eligible to run) on the Accelerator:

• zIIP eligible DB2 for z/OS work:



the utility BUILD and REBUILD phases for index maintenance



UNLOAD phase of REORG



most index, COLCARD, FREQVAL, HISTOGRAM statistics collection by RUNSTATS



inline stats for LOAD, REBUILD INDEX and REORG (about 80 percent)



100 percent delete processing for LOAD REPLACE with dummy input



100 percent of all prefetch (dynamic, list and sequential)



inadequate zIIP capacity could make this particular redirect real interesting



100 percent GBP write, castout, p-lock, notify/exit processing



100 percent index pseudo delete cleanup



Log reads and writes



call, commit and result-set processing for remote stored procedures


XML parsing.

• The non DB2 (or z/OS) activities that could use a zIIP that is sometimes overlooked:



IPSec—Encryption processing, header processing and crypto validation (93 percent for bulk data movement)



HiperSockets for Large messages



SMT (simultaneous multithreading) mode processing is only available for zIIPs implemented on a z13 and above with z/OS v2.1 and above.



With SMT enabled, zIIPs could potentially have 140 percent or more capacity.



zAAP on zIIP—anything that would have (could have) run on a zAAP is not eligible to run on a zIIP.



SDSF in z/OS v2.2 is designed to offload a portion of certain kinds of processing.



DFSMS SDM of z/OS (processing associated with zGM/XRC)



IBM SDK for z/OS, Java Technology Edition



z/OS XML System Services



System Data Mover (SDM)



DFSORT (Sorting of fixed length rows—10–40 percent for utilities)



DB2 Sort (10–20 percent potentially eligible for redirect)


Global Mirror for z/OS (formerly Extended Remote Copy)—Most System Data Mover processing.

If you were to remove one or more zIIPs, all of the above could no longer be redirected, meaning those MIPS would end up running back on your more expensive CPs. Be cautious about making any decision to reduce the number of zIIPs you have employed. In fact, there are a couple of functions like DB2 prefetch that if not enough zIIP processing is available, it could get you into performance trouble.

Willie Favero is currently the DB2 for z/OS SME working with the z Analytics Swat Team at IBM’s Silicon Valley Lab. He has spent 40 years working on mainframes with more than 30 years of that working with DB2. He is a frequent speaker at major conferences worldwide. Email: wfavero@acm.org

Read the full article at http://ourdigitalmags.com/article/If+I+Have+a+DB2+Analytics+Accelerator%2C+Do+I+Still+Need+zIIPs%3F/2367538/287162/article.html.

Previous Page  Next Page


Publication List
Using a screen reader? Click Here