Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

DB2

Posted Jun 24, 2010

Adding some zIIP to IBM DB2: What Can Speciality Processors Do for You?

By Julian Stuhler

For some time now, IBM DB2 for z/OS has been the major user and benefactor of the zIIP engine: one of a number of System z technologies collectively known as “speciality processors”. Learn how IBM DB2 for z/OS uses zIIP and how it reduces the monthly software cost for customers on WLC charging models.

Introduction

For some time now, DB2 for z/OS has been the major user and benefactor of the zIIP engine: one of a number of System z technologies collectively known as “speciality processors”. This article provides an overview of the zIIP and how DB2 for z/OS can use it (including a recently announced enhancement). In a future column, I’ll be providing some practical advice on how to tell if acquiring a zIIP would benefit your specific DB2 workloads, and how to monitor your zIIP usage once you have one.

Some System z Fundamentals

So, what are these “speciality processors”, and what are they for? To properly answer that question, we first need to take a step back and understand a bit about other types of processors in a System z machine, and how they relate to IBM’s software charging schemes for that platform.

In the beginning, we had just one type of processor in System z machines, now known as a Central Processor, or CP. A CP is a general-purpose processor, able to run any workload the System z machine is able to handle. The number of CPs in a given machine depends on the model, with up to 64 CPs in the latest System z10 machines.

Most mainframe software products (both IBM products such as DB2 and those provided by other vendors such as CA or BMC) are charged based on a Monthly Licence Charge (MLC) model, with a monthly sum that includes both licence fees and support costs. There are several different flavours of MLC, but the most common is WLC or Workload Licence Charge, where the monthly price a System z customer pays depends on the amount of processor resource consumed during the previous period, measured in MSUs (Million Service Units). So, the monthly cost paid by most customers for their IBM software is directly related to the amount of work their CPs process: the higher the workload, the higher the monthly cost.

One notable variation on the WLC charging model is aimed at the increasing number of customers who are using the System z platform for running some specific “new workloads” such as Websphere Application Server, SAP and Siebel. Capturing and retaining this type of workload is of critical strategic importance to IBM, so a separate and very attractively priced MLC scheme called zNALC (System z New Application Licence Charges) has been created to encourage customers to use System z for these applications.

IBM’s System z Speciality Processors

Now that we understand those basics, let’s get back to the speciality processors.

Rumours of the death of the mainframe in recent years have obviously been greatly exaggerated, but there is no doubt that the platform is still under threat from cheaper (and ever more capable) alternatives from the mid-range world. IBM is not blind to that threat, and is constantly looking at ways of keeping System z competitive from a Total Cost of Ownership perspective.

One of the ways in which IBM is reducing the overall cost of mainframe workloads is to offer customers the option of installing additional “speciality processors” within their System z machines. These processors are capable of running only specific types of work, but in so doing they can reduce the load on the general-purpose CP processors and therefore the amount of MSUs consumed. This in turn reduces the monthly software cost for customers on WLC charging models.

System z customers are charged a one-off fee in order to initially enable a speciality processor, but there are no additional ongoing costs associated with it (and that fee is considerably cheaper than enabling a full CP). So, provided the one-off enabling cost can be recovered by the reduced MLC fees in a reasonable period, speciality processors can be very financially attractive.

There are currently four types of speciality processors supported by System z[1], but we’re going to concentrate on the one that has the biggest impact on DB2: the System z Integrated Information Processor (zIIP). The zIIP is the most recent addition to the speciality engine family, and has been available since 2006. It is designed to offload specific types of data and transaction processing workloads for business intelligence, ERP and CRM, and network encryption. No changes are required to the application in order to make use of a zIIP – once it has been enabled, eligible workloads can take advantage of it immediately.

The z/OS WLM (Workload Manager) is responsible for directing eligible work away from the CP and to the zIIP, according to some strictly defined rules (which we’ll look at later). Remember that you have no direct control over what is directed to the zIIP: WLM makes the decision based on the type of workload being submitted, and how busy the zIIP is. For example, zIIP-eligible workload could still be directed to a CP if all available zIIPs are busy processing other work.

zIIPs can be installed in any z9 and z10 server and are physically identical to a CP (only the microcode differs). Note that a maximum of one zIIP may be configured for each CP, so a fully-loaded z10 E26 with all 26 CPs enabled could also have up to 26 zIIPs available!

So, the bottom line is that zIIPs can reduce your monthly software fees and increase your overall computing capacity by redirecting eligible work away from those precious CPs. As a bonus, they can also improve performance in some situations (where your CPs are at or close to 100% busy, for example).

DB2 & zIIPs

Now that we understand the concepts behind the zIIP, let’s take a closer look at how DB2 can make use of them. Note that you need to be on DB2 for z/OS Version 8 or higher (in either Compatibility or New Function mode) to be able to exploit many of the following zIIP offload opportunities (which shouldn’t be a problem for most sites as V7 has been out of support for some time!).

The following types of DB2 workload are eligible for zIIP offload.

  • DDF server threads that process SQL requests from applications that access DB2 by TCP/IP[2]. This is typically the most significant workload to benefit from the zIIP, as many IT systems are architected to use an application server, which directs database requests to DB2 for z/OS via DRDA and TCP/IP. This includes ERP systems such as SAP and Siebel, some BI workloads and other bespoke applications using CLI, JDBC or ODBC access via products such as DB2 Connect. It’s worth noting that the “remote” application server or requester can actually be on another LPAR or partition on the same physical System z server as DB2 (in which case Hipersockets can provide some performance gains by bypassing some of the TCP/IP protocol stack). Previously up to 50% of the incoming DRDA work was eligible for zIIP offload, but some recent maintenance (IBM APAR PM12256) has increased this so the potential offload is now up to 60% for both DB2 V8 and V9 at the relevant fix level. The same APAR also introduces other performance improvements for DRDA workloads, and should be a no-brainer for any site with significant workload of this type.
  • Native SQL stored procedures that are run through DDF (V9 only). Stored procedures written entirely in SQL are becoming increasingly popular due to their familiarity to DB2 developers and superior performance and functionality compared to those written in more traditional programming languages. Version 9 gave them an additional boost, as it allowed them to be natively executed within the DB2 address space, and made part of the work done by the procedures eligible for zIIP offload.
  • Parallel Child Processes. This can include BI application query processing utilizing DB2 star-schema parallel query capabilities and other queries where the optimiser has chosen to split the access into multiple subtasks (CP parallelism). A portion of the child task processing will be redirected after a specific CPU usage threshold has been exceeded for each parallel group. Of course, if the query has originated from a DRDA request over TCP/IP, the main parent task is also eligible for offload. The enhancements made to star join dynamic index ANDing in V9 further increased the zIIP potential for this type of workload.
  • Some DB2 utility index build and maintenance processes for the LOAD, REORG, and REBUILD INDEX utilities. A portion of the index build/maintenance subtask can be redirected to a zIIP, helping to reduce the cost of running some routine housekeeping operations. The amount of offload potential depends on several factors, including the number of indexes on the table, the number of partitions and the number of column data types being processed. Generally, the higher these numbers are, the greater the potential for significant zIIP offload.
  • XML parsing (V9 only). DB2 9 for z/OS includes some sophisticated XML capabilities, in the shape of the pureXML feature. Some of the work required to parse XML documents can be offloaded to zIIP processors (or zAAP processors).

Although you can’t specifically tell WLM what workload to push to the zIIPs, you can tell after the event what it decided to do. There is some good instrumentation available within RMF and the DB2 accounting records, which can help in determining how well your zIIP(s) are being used (or, if you don’t yet have any, an estimate of how much work the zIIPs would have taken on had they been available).

Making It All Add Up

In summary, zIIPs can make a lot of financial sense if your DB2 systems are supporting the right type of workload. It’s not unusual to see the initial cost of implementing one or more zIIP engines being paid back in just a few months, with a modest performance boost as an additional bonus. In a future column, we’ll take a deeper look at the instrumentation available for zIIP offload, and some of the options for architecting your DB2 applications to make better use of them.

Additional Resources

IBM’s zIIP Home Page
Maximizing offload to zIIP processors with DB2 9 for z/OS native SQL stored procedures
Information APAR on DB2 for z/OS zIIP exploitation
APAR PM12256 (DRDA Performance Improvements)

[1] The other three speciality processors are:

  • System z Application Assist Processor (zAAP). This processor is aimed at Java workloads running under z/OS, and was first introduced in 2004. In recent years, IBM has enabled all of the major middleware components to embrace Java, including stalwarts such as DB2, CICS and IMS as well as the more obvious products such as Websphere Application Server (WAS). Any Java code running under z/OS (such as a Java DB2 stored procedure) is eligible for offload to the zAAP.
  • Internal Coupling Facility (ICF). These processors were first introduced back in 1997, and can be used in place of an external Coupling Facility to support parallel SYSPLEX workloads in a data sharing environment.
  • Integrated Facility for Linux (IFL). Introduced in 2000, IFLs are targeted at Linux workloads running on System z platform. One or more IFLs can be dedicated to an LPAR running zLinux, allowing customers to run Linux workloads on the System z with no impact to their MLC licence fees.

[2] Note that DDF requests received via an SNA link are NOT eligible for zIIP offload.

» See All Articles by Columnist Julian Stuhler



DB2 Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 



















Thanks for your registration, follow us on our social networks to keep up-to-date