I worked recently with some IT people who were themselves engaged in the development and enhancement of a high-volume application that accesses DB2 for z/OS data by way of stored procedures (the client side of the application communicates with the target DB2 system via the distributed data facility, also known as DDF). Various actions implemented and plans formulated by this IT team serve to effectively illustrate some important points regarding the nesting of DB2 stored procedures ("nesting," in this context, refers to a situation in which stored procedures call other stored procedures). Through this blog entry, I want to bring these points to light.
Why nest in the first place?
This basically comes down to what you want in terms of the granularity of the functionality provided by your DB2 for z/OS stored procedures. Suppose that you have a stored procedure that performs a variety of data-related actions -- perhaps inserting some rows into one table, updating rows in another table, and generating a result set that will be consumed by the calling process. If these actions are always to be accomplished every time the stored procedure is called, keeping them in the one stored procedure is probably the way to go for optimal performance and for the sake of simplicity (simple is generally good); however, if it is likely that a typical CALL will end up exercising just one of the stored procedure's data processing functions, separating those functions in several different, smaller stored procedures could be advantageous. Of course, such a break-up doesn't necessarily imply nesting -- client applications could just call the individual stored procedures directly. The question then, is this: at what level do you want small-grain (referring to scope of functionality) stored procedures to be grouped so as to provide larger-grain database services? If you want this grouping to be done by client-side application programs, have those programs call the small-grain stored procedures directly. If, on the other hand, you want the grouping of small-grain stored procedures for larger-grain processing to be handled by higher-level "orchestrating" stored procedures, go the nesting route.
In the case of the application I mentioned in the opening paragraph of this entry, the IT team wanted the flexibility provided by small-grain stored procedures, but they didn't want client-side developers to have to do the work of "stringing together" small-grain stored procedures to accomplish more-comprehensive data processing tasks. Given that situation, the decision to implement nested stored procedures is understandable.
When one stored procedure invokes another: the CALL statement
Because the application to which I've referred is quite dynamic with respect to changes in client-required data processing, the IT team in charge of stored procedure design and development went with a maximally flexible implementation: they created a "parent" stored procedure that would invoke nested, "child" stored procedures with calls of this form (here ":hvar" refers to a host variable):
CALL :hvar (:hvar, :hvar, :hvar, :hvar)
That CALL-coding decision, in turn, dictated the use of an external stored procedure for the "parent" routine, versus a native SQL procedure. Why? Because a CALL in the body of a native SQL procedure must be of the form CALL procedure-name. In other words, a nested stored procedure invoked by a native SQL procedure must be explicitly named (i.e., "hard-coded") in the CALL statement issued by the native SQL procedure.
COBOL was chosen by the IT team as the coding language for the "parent" stored procedure. It was further decided that the nested stored procedures would be native SQL procedures.
"Hey, where's my zIIP offload?"
When the stored procedure set-up went live, the supporting IT folks were surprised to see that the native SQL procedures were getting very little in the way of zIIP engine offload. Aren't native SQL procedures supposed to be majorly zIIP-eligible when they are called by DRDA requesters (i.e., through DDF)? Yes, but that is only true when a native SQL procedure is directly called by a DRDA requester (as I pointed out in an entry I posted to this blog a few months ago). See, a native SQL procedure always runs under the task of its caller. When the caller is a DRDA requester, that z/OS task is an enclave SRB in the DB2 DDF address space, and that makes the called native SQL procedure zIIP-eligible (to the tune of 55-60%). If a native SQL procedure is called by an external DB2 stored procedure, the native SQL procedure will execute under the task of the external stored procedure -- a TCB in a stored procedure address space -- and so will get little, if anything, in the way of zIIP offload.
Making a result set generated by a nested stored procedure available to a program that is more than "one level up" from the nested procedure
The aforementioned lack of zIIP offload seen for the nested native SQL procedures was exacerbated by the mechanism used to make result set rows generated by a cursor declared and opened in a nested stored procedure available to a "top-level" calling program (i.e., to a client-side program that initiated a chain of nested stored procedure calls): the result set rows were inserted by the nested stored procedure into a temporary table, and the top-level calling program would then retrieve the rows from that temporary table. Not an uncommon approach, but also not great from a performance perspective, and more complex than you'd like. DB2 10 for z/OS gave us a much better way of getting this job done: ditch the temporary table, and have the nested stored procedure declare its cursor WITH RETURN TO CLIENT (versus WITH RETURN TO CALLER, the formerly only valid specification by which a cursor's result set can be directly fetched only by the direct caller of the result-set-generating stored procedure). When a cursor in a stored procedure is declared WITH RETURN TO CLIENT, the result set rows can be directly fetched by the top-level calling program, no matter how far down the result-set-generating stored procedure is in a chain of nested calls.
Rethinking things: there's more than one kind of CALL
Even WITH RETURN TO CLIENT cursors might not have delivered the performance that was desired for the stored procedures of which I've been writing, particularly in light of the fact that, as mentioned, the nested, native SQL procedures were getting very little zIIP engine offload. Of course, the parent stored procedure could have been changed to a native SQL procedure from an external stored procedure, so as to maximize zIIP offload (and minimize general-purpose CPU consumption), but that would have required going with CALL procedure-name invocation of the nested stored procedures, and the IT team really wanted to stay with the CALL :hvar approach. Ultimately, a decision was made to go with an option made possible by the fact that the parent stored procedure was written in COBOL: the nested routines would be changed from native SQL procedures to COBOL subroutines, and they would be invoked as such from the parent stored procedure. That makes sense in this case: if zIIP offload isn't your ticket to minimizing consumption of general-purpose engine capacity, get CPU efficiency by reducing path length -- a COBOL subroutine called by a COBOL stored procedure program should be more CPU-efficient than an equivalent-function nested stored procedure called by that same COBOL stored procedure program (I say "should" because I haven't yet seen the results of the IT team's change to the COBOL subroutine approach, but I expect efficiency gains).
In conclusion...
You have plenty of options when you're looking to implement stored procedure-based DB2 database services -- external and native, nested and non-nested, WITH RETURN TO CALLER and WITH RETURN TO CLIENT cursors. There's not a one-size-fits all "right way" that will always be the best way. Consider the particulars of your situation, and make the choices that deliver the flexibility, performance, and functionality that YOU want.
Friday, May 30, 2014
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.
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.
Subscribe to:
Posts (Atom)