Tuesday, May 13, 2014

Some zIIP Things of Which DB2 for z/OS People Should be Aware

zIIP is short for System z Integrated Information Processor. It's a so-called specialty engine for an IBM mainframe server. The zIIP raison d'etre is reduced cost of computing on the System z platform, accomplished in two ways: 1) zIIP engines cost less than general-purpose processors (often referred to as CPs -- short for central processors), and 2) zIIP engines do not factor into the cost of software that runs in z/OS LPARs. zIIP engines aren't new (they were introduced in 2006), but there are some relatively recent zIIP-related developments that are important from a DB2 for z/OS perspective. I hope that this blog entry will help mainframe DB2 people to keep their zIIP knowledge up to date.

The circle of zIIP-eligible work continues to expand

At many System z sites, the number one driver of zIIP utilization is execution of SQL statements that get to DB2 via the distributed data facility, or DDF (i.e., statements that come from DRDA requesters). Two factors emerged in recent years to boost zIIP offload for DDF workloads. First, there was an increase in zIIP eligibility for work executing under enclave SRBs in the DDF address space: a DB2 9 PTF took this zIIP offload percentage from about 55% to about 60%. Second, native SQL PL routines were introduced, also with DB2 9. SQL PL -- short for SQL procedure language -- is the language in which DB2 native SQL procedures are written. A native SQL procedure executes under the task of the application process that calls it. When the calling process is a DDF-connected application, the task is an enclave SRB in the DDF address space. In that case, a native SQL procedure is zIIP-offloadable to the tune of about 60%, as is true of SQL statements in general that are issued by DRDA requesters (in contrast, an external stored procedure always runs under its own TCB in a WLM-managed stored procedure address space, and so is not zIIP-eligible). A number of organizations have been engaged for some time in converting external DB2 stored procedures called by DRDA requesters to native SQL procedures, so as to boost redirection of work to zIIP engines.

This SQL PL-related zIIP offload booster was expanded in scope when, with DB2 10, user-defined functions as well as stored procedures could be written in SQL PL. UDFs coded in SQL PL, along with UDFs that have a RETURN statement that includes a scalar fullselect, are referred to as compiled SQL scalar UDFs, or, sometimes, as non-inline SQL scalar UDFs. Compiled SQL scalar UDFs, like inline SQL scalar UDFs and SQL table UDFs, run under the task of the UDF-invoking application process, just as do native SQL procedures. As noted previously, when the application process is a DRDA requester, the task in the z/OS system is a DDF enclave SRB, and that makes any invoked SQL UDF -- whether compiled scalar, inline scalar, or table -- about 60% zIIP-eligible.

Some IBM DB2 utility operations contribute to zIIP engine utilization. In a DB2 9 system, it's the index maintenance activity associated with LOAD, REORG, and REBUILD execution. DB2 10 added RUNSTATS processing to this mix. DB2 11 delivers still greater zIIP offload for RUNSTATS, plus zIIP offload for inline statistics generation performed as an option of LOAD, REBUILD INDEX, and REORG.

Other DB2-related zIIP utilization drivers include query parallelization (DB2 V8), prefetch read and database write operations (DB2 10), XML schema validation (DB2 10), log reads and writes (DB2 11), and clean-up of pseudo-deleted index entries (DB2 11).

And don't think that DB2 alone exercises zIIP engines. I'm seeing more and more Java programs running in z/OS systems -- certainly in WebSphere Application Server for z/OS, but also in the form of DB2 for z/OS Java stored procedures, and as batch processes and started tasks that utilize IBM's JZOS Toolkit. Java code executing in a z/OS system can be redirected to another type of specialty engine called a zAAP, but on a mainframe server with zIIPs and no zAAPs, Java programs will drive utilization of zIIPs by way of what's called zAAP-on-zIIP functionality.

The bottom-line message is this: DB2 itself, with each succeeding release, provides more and more opportunities for shifting work from general-purpose mainframe engines to zIIPs. Java programs, increasingly prevalent in z/OS systems, also boost zIIP utilization. That's good news from a System z cost-of-computing perspective, as it means that you can grow a mainframe workload without having to add general-purpose engines to a system; however, it also means that you need to keep an eye on zIIP engine utilization, because running those engines too hot could have a negative impact on application performance, as explained below.

Don't over-utilize zIIP engines

One thing that organizations have long liked about mainframe computers is the fact that you can run general-purpose engines at very high levels of utilization -- like 90% or more -- while still getting excellent application performance and throughput. The story is different for zIIP engines, and here's why: if a zIIP engine is not available when zIIP-eligible work is ready to be dispatched, that work can be directed instead to a general-purpose engine, but such redirection introduces a degree of delay. This delay can affect performance noticeably in a DB2 10 (or later) system, because (as previously noted) starting with DB2 10 prefetch processing became zIIP eligible. If prefetch reads are slowed because of overly high zIIP engine utilization, throughput for prefetch-intensive workloads (think batch, and decision support applications) can be negatively impacted. In a case of that nature, a slowdown in prefetch processing would show up in a DB2 monitor accounting long report (or an online display of thread detail data) as elevated "wait for other read" time (that is one of the so-called class 3 wait time categories).

Frankly, I'd start thinking about adding more zIIP capacity to a System z server if I saw zIIP utilization regularly reaching 60% or more.

July 2013: a zIIP capacity boost

With more and more zIIP-eligible work in z/OS systems, and given the importance of not over-utilizing zIIP engines, you'll want to make sure that zIIP capacity on your mainframe servers stays well ahead of demand for same. That task became easier with an important announcement that IBM made in July of 2013: on a zEC12 or zBC12 server, the number of zIIP engines can be up to two times the number of general-purpose engines (prior to that announcement, the number of zIIP engines on a server could not exceed the number of general-purpose engines). The more heavily weighted your DB2 workload is towards DDF-connected applications (and the more Java code you have running in z/OS LPARs), the more heavily weighted your processing capacity should be towards zIIP engines versus general-purpose engines.

zIIP engines are enabling organizations to grow z/OS system workloads in an ever more cost-effective manner. That said, you need to keep an eye on zIIP engine utilization. Yes, take actions to drive zIIP capacity utilization (so as to shift work off of general-purpose engines), but take actions as well to ensure that zIIP resources on your system are adequate to handle zIIP-eligible work in an optimal fashion.

11 comments:

  1. Can I offload to zIIP in the following scenario:
    Process P1 running in LPAR1 issues EXEC SQL CONNECT to a LOC2 on LPAR2. P1 calls a native stored procedure on LOC2. Does the native stored procedure use zIIP?

    ReplyDelete
    Replies
    1. Yes, and the reason for that, as I pointed out in another blog entry (http://robertsdb2blog.blogspot.com/2013/11/db2-for-zos-work-tasks-thing.html), would be the fact that the native SQL procedure in your scenario would execute under an enclave SRB in the DB2 DDF address space (aka the DIST address space). If that native SQL procedure were not called by a DRDA requester, it would run under a TCB in the address space of the caller (e.g., a local-to-DB2 batch initiator address space) and would not be zIIP-eligible. Similarly, if your DRDA requester called an external stored procedure, that stored procedure's execution would not be zIIP-eligible because an external stored procedure runs under its own TCB in a stored procedure address space.

      Robert

      Delete
  2. Thank you, Robert. I could learn some new things in this post, about zIIP.

    ReplyDelete
  3. I'm learning some new "Performance tuning" things about ziip and zaap "as we speak" and Java-zOS or zJava is a "new thing" to me in and of itself. I knew you could run zLinux in a zOS LPAR, but I'm understanding that you can now run zJava executables under zOS without zLinux?? I'm going to google that for some additional reading/study material. The "DB2 zOS [PTS] Performance Assessment and Recommendations" report, that I produced for U.S. Postal Service in 2010 included ziip and zaap but not all of the extended ziip/zaap performance considerations mentioned by Robert above. I may have to call them and see if they need to squeeze a little more performance out of their "Christmas Rush" and "Income Tax Rush" "mail overload" seasons. They were also running zLinux (with Java or zJava?) in an LPAR but I don't believe that had anything to do with the DB2 overloads. That's been 5 years ago and I haven't heard anything more about their seasonal overloads, but that doesn't mean it isn't a concern.

    ReplyDelete
    Replies
    1. Technically speaking, there is nothing called zJava (nor is there anything called zLinux - it's just Linux on z Systems). Yes, Java code can run natively in a z/OS system - this has been true for quite some years now. A growing number of organizations run high-volume, mission-critical Java application workloads in z/OS systems.

      Robert

      Delete
    2. In our mainframe system, ZIIP processor is only 10% utilized eventhough DRDA usage GP processor is close to 40%. Also we have conducted a test where one of the batch db2 program issuing millions of prefetch but nothing offloaded to ZIIP processor. Please advise.

      Processor is 2828-X03
      3 ZIIP

      Delete
    3. When a DRDA workload gets little zIIP offload even though zIIP engine utilization is low, in my experience this is usually the result of DRDA requesters calling external DB2 stored procedures. An external stored procedure will get next to nothing in the way of zIIP offload, even when called by a DRDA requester, because it executes under a TCB in a stored procedure address space, and SQL that executes under TCBs is not zIIP-eligible.

      If a batch job drives a lot of prefetch activity, that will not be reflected in the zIIP CPU time shown for the batch job in DB2 accounting trace data. The reason: the CPU cost of prefetch read activity (and for database write activity) is charged to the DB2 database services address space (aka DBM1) - not to the task of the application process that drove the prefetch requests.

      Robert

      Delete
  4. Thanks Robert. this helps. one more question, in our shop we are using GOLDEN GATE product which SYNCs data from Oracle Database on Linux to DB2 on ZOS. There are millions of SYNC calls happens every hour and as per the documentation this is ODBC connection and this should be zIIP eligible. But the SYNC task (GOLDPROD - Golden Gate Task) shows zero zIIP time and Zero ZIIPONCP. So why this ODBC work is not zIIP eligible?

    DB2 Version 10
    Oracle 12c
    Oracle Golden Gate

    ReplyDelete
    Replies
    1. The fact that a process uses ODBC in accessing DB2 for z/OS does NOT make SQL issued by the process zIIP-eligible. zIIP eligibility of SQL does not depend on the data interface used to access DB2. It depends on the nature of the task under which the process is executing, as explained in my blog entry view-able at http://robertsdb2blog.blogspot.com/2013/11/db2-for-zos-work-tasks-thing.html.

      Robert

      Delete
    2. Robert is this still true with zIIP Performance
      Don't over-utilize zIIP engines

      One thing that organizations have long liked about mainframe computers is the fact that you can run general-purpose engines at very high levels of utilization -- like 90% or more -- while still getting excellent application performance and throughput. The story is different for zIIP engines, and here's why: if a zIIP engine is not available when zIIP-eligible work is ready to be dispatched, that work can be directed instead to a general-purpose engine, but such redirection introduces a degree of delay. This delay can affect performance noticeably in a DB2 10 (or later) system, because (as previously noted) starting with DB2 10 prefetch processing became zIIP eligible. If prefetch reads are slowed because of overly high zIIP engine utilization, throughput for prefetch-intensive workloads (think batch, and decision support applications) can be negatively impacted. In a case of that nature, a slowdown in prefetch processing would show up in a DB2 monitor accounting long report (or an online display of thread detail data) as elevated "wait for other read" time (that is one of the so-called class 3 wait time categories).

      Frankly, I'd start thinking about adding more zIIP capacity to a System z server if I saw zIIP utilization regularly reaching 60% or more.

      Delete
    3. Hello, Dave.

      It's still true that you want to avoid zIIP engine contention, both to optimize application performance and to minimize the degree to which zIIP-eligible work "spills over" to general-purpose engines. In terms of my messaging on this topic, what I've generally stopped doing is telling people to keep zIIP engine utilization below some percentage - the fact is, the more zIIP engines an LPAR has, the higher the utilization rate at which they can be run while still minimizing spill-over to general-purpose engines.

      In an entry I posted a few months after this one (https://robertsdb2blog.blogspot.com/2014/09/db2-for-zos-avoiding-ziip-engine.html), I describe how to use information in a Db2 monitor-generated accounting long report to measure the percentage of zIIP-eligible work that is spilling over to general-purpose engines. My goal would be to keep that spill-over ratio below 1% (great), or at least below 5% (OK). If the zIIP spill-over rate were getting higher than desired on my system, I'd want to address that situation by adding zIIP capacity (if I could not add zIIP engines to an LPAR, I might opt to run zIIPs in SMT2 mode - see https://robertsdb2blog.blogspot.com/2018/02/db2-for-zos-ddf-ziip-engines-and-smt2.html).

      Robert

      Delete