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.
Tuesday, April 29, 2014
A Tiger Changes His Stripes: I LIKE Java on z/OS
I well remember a phone conversation, from about six or seven years ago, during which a friend of mine and I discussed an application project at his site which involved use of DB2 for z/OS stored procedures. Me: "In what language will the stored procedure programs be written?" My friend (sounding like someone whose favorite sports team had just lost the championship game): "Java." Me (groaning): "Java? Why Java?" My friend: "Because some 'insultants' told our management that that was the right way to go." I probably winced at that point -- both because I felt his pain, and because I was working at the time as an independent DB2 insultant (er, consultant).
My disappointment on hearing my friend's news was genuine. Sometime later, I was asked to weigh in on an issue that people with another organization were trying to decide: should some new DB2 for z/OS stored procedures be coded in Java or SQL PL (i.e., SQL Procedure Language, used to create DB2 native SQL procedures). That was a no-brainer, as far as I was concerned. SQL PL was my recommendation because I really like DB2's native SQL procedure functionality, and because I really didn't like the idea of running Java programs in z/OS systems. I was fine with Java code executing in a browser on my laptop, or in an application server on a distributed systems platform, but on a mainframe? No! I saw Java, in the context of a z/OS execution environment, as being a CPU and a memory hog, a waste of processing capacity. Want performance and efficiency from your z/OS programs? You don't want Java.
Well, that was then, and a funny thing happened on the way to now: I started encountering more and more organizations running Java programs on z/OS systems, and they were getting excellent performance and throughput, and people weren't griping about mainframe resource utilization. How could that be? Had reality been warped in some way? No, nothing that cosmic. Basically, what changed was technology -- IBM System z and z/OS technology, in particular. A lot of the changes were on the hardware side, including:
So, today's zEnterprise servers and the z/OS operating system provide an outstanding execution environment for Java applications. How are organizations implementing DB2 for z/OS-accessing Java programs in this environment? This is what I'm seeing:
And so you have it. The positive results that organizations with which I work have gotten in running Java on z/OS systems has caused me to change my tune (or my stripes, as suggested by this entry's title). Tell me now that you're running Java on z/OS, and you won't hear me groan. Instead, I'm likely to say, "That's a fine choice you've made, there." My colleagues at the IBM Poughkeepsie (zEnterprise and z/OS) and Silicon Valley Labs (DB2 for z/OS), who've worked hard to make z/OS an excellent platform for Java applications, would wholeheartedly agree.
My disappointment on hearing my friend's news was genuine. Sometime later, I was asked to weigh in on an issue that people with another organization were trying to decide: should some new DB2 for z/OS stored procedures be coded in Java or SQL PL (i.e., SQL Procedure Language, used to create DB2 native SQL procedures). That was a no-brainer, as far as I was concerned. SQL PL was my recommendation because I really like DB2's native SQL procedure functionality, and because I really didn't like the idea of running Java programs in z/OS systems. I was fine with Java code executing in a browser on my laptop, or in an application server on a distributed systems platform, but on a mainframe? No! I saw Java, in the context of a z/OS execution environment, as being a CPU and a memory hog, a waste of processing capacity. Want performance and efficiency from your z/OS programs? You don't want Java.
Well, that was then, and a funny thing happened on the way to now: I started encountering more and more organizations running Java programs on z/OS systems, and they were getting excellent performance and throughput, and people weren't griping about mainframe resource utilization. How could that be? Had reality been warped in some way? No, nothing that cosmic. Basically, what changed was technology -- IBM System z and z/OS technology, in particular. A lot of the changes were on the hardware side, including:
- zAAPs and zIIPs to reduce the cost of computing for z/OS-based Java applications. zAAPs (System z Application Assist Processor) and zIIPs (System z Integrated Information Processor) are specialty engines that pack the power and performance of System z general-purpose central processors (GCPs) but do not factor into the cost of mainframe software (that's based on GCP capacity). zAAPs are specifically intended to run Java workloads (as well as handling XML data processing functions). zIIPs, while typically driven primarily by DB2 for z/OS DDF/DRDA (i.e., network-attached client-server) processing, can also be used for zAAP-eligible work when a system does not have zAAP engines (that's called zAAP-on-zIIP). zAAPs and zIIPs have been around for a while, but they've come lately to the forefront in terms of Java processing in large part because of the aforementioned zAAP-on-zIIP capability, and as a result of the recent (July 2013) increase in the allowable ratio of zAAP and zIIP engines to GCPs on a zEC12 or zBC12 server (whereas the number of zAAP or zIIP engines on a mainframe formerly could not exceed the number of GCPs on the server, now for a zEC12 or zBC12 server there can be up to twice the number of zAAPs or zIIPs as GCPs).
- More and cheaper mainframe memory, and new ways to manage it. I no longer think of Java as a memory hog on z/OS systems, any more than I think of DB2 as a memory hog. Like DB2, Java applications are exploiters of large server memory resources: give them more memory, and they'll perform better and with greater efficiency. Providing Big Memory for Java applications on z/OS is much more feasible and practical now than was the case just a few years ago. A modern mainframe (z196 or zEC12) can be configured with up to three terabytes of central storage, and that memory is far lest costly per gigabyte than it would have been in the not-too-distant past. And, this is not just a more-and-cheaper story -- there's a smarter angle, too, in the form of large frame support. Instead of managing all memory in 4 KB page frames, modern mainframes provide the ability to have some of a z/OS system's memory managed in 1 MB frames, thereby boosting CPU efficiency by reducing the overhead associated with translating virtual to real storage addresses. A JVM (Java virtual machine) in a z/OS system can be set up to use non-pageable 1 MB page frames for heap storage, and (if the server has Flash Express for super-fast paging) pageable 1 MB frames for JIT (just-in-time compiler) work areas. With a zEC12 or zBC12 server, the Java heap can be backed by 2 GB page frames for even greater efficiency.
- Transactional Execution (TX) facility. This capability, known in the industry as hardware transactional memory, is essentially an in-hardware implementation of optimistic locking for transactional workloads. The TX facility, a feature of zEC12 and zBC12 servers, is aimed largely at boosting the performance of Java application workloads through lock avoidance.
- In-hardware boosts for decimal floating-point and IEEE binary floating-point operations. Java applications often make use of these operations.
- Runtime instrumentation (RI). This is a hardware facility, introduced with the zEC12 and zBC12 servers, that delivers a dynamic and self-tuning online recompilation capability for Java applications.
So, today's zEnterprise servers and the z/OS operating system provide an outstanding execution environment for Java applications. How are organizations implementing DB2 for z/OS-accessing Java programs in this environment? This is what I'm seeing:
- The IBM JZOS Toolkit. This is a set of of classes, distributed with the IBM Java SDKs for z/OS, that can be used to launch Java applications as batch jobs or started tasks in a z/OS system. Also included in the toolkit are Java methods that facilitate access to z/OS data sources and services such as DB2. More information about the classes associated with the JZOS Toolkit can be found in developerWorks on IBM's Web site.
- DB2 for z/OS Java stored procedures. Java as a language for stored procedure development has been supported by DB2 for z/OS since Version 5 (late 1990s), but for a long time there was not a lot of activity here, due largely, I think, to the historically negative view of Java on z/OS to which I alluded in the opening part of this blog entry. Nowadays, Java stored procedures are showing up at a growing number of DB2 sites. This trend should get a boost from DB2 11 for z/OS, which added support for running Java stored procedures in 64-bit JVMs (previously, only 31-bit JVMs were supported for Java stored procedures). Not only that, but these are multi-threaded JVMs; so, in a DB2 11 system you'll have a single 64-bit JVM per WLM-managed stored procedure address space in which Java stored procedures run, versus having a 31-bit JVM for each TCB in such an address space. That means a smaller JVM storage footprint, less overhead associated with starting JVMs, and more concurrently active Java stored procedures per stored procedure address space.
- WebSphere Application Server (WAS) for z/OS. I'm encountering WAS on z/OS a lot more than was the case just a few years ago. An organization with which I work pretty extensively is a case in point: they have a modern, high-volume, business-critical, client-server application that is wholly contained within a z/OS system, with business logic running in WAS and a DB2 subsystem on the same LPAR providing data management (and a good bit of data access logic in the form of stored procedures and user-defined functions). Plenty of other companies have gone, or are planning on going, this same route. One of the interesting aspects of such an environment is the decision presented regarding the choice of JDBC driver for the WAS-based, DB2-accessing Java applications: type 2 (a "local" connection using the DB2 Recoverable Resource Services Attach Facility) or type 4 (a "remote" connection through the DDF address space of the target DB2 subsystem). The type 2 JDBC driver provides the more CPU-efficient connection to a DB2 subsystem in the same LPAR as WAS. The type 4 driver, while adding to connection pathlength (this because the connection involves going into the LPAR's TCP/IP stack and then into the DB2 DDF address space), increases zIIP engine offload for SQL statement execution because the statements run under enclave SRBs in DDF. Which JDBC driver should you use? That will vary by application. For an application with transactions that issue a lot of quick-running SQL statements, the type 2 driver might be a good fit, as there could be a premium in that case on getting to DB2 and back as efficiently as possible. For applications characterized by a smaller number of longer-running SQL statements, the greater zIIP offload for SQL execution provided by the type 4 JDBC driver might tilt you in that direction. The good news? Going from one driver to the other for an application is a pretty simple thing. It's not a big deal to try both of them.
And so you have it. The positive results that organizations with which I work have gotten in running Java on z/OS systems has caused me to change my tune (or my stripes, as suggested by this entry's title). Tell me now that you're running Java on z/OS, and you won't hear me groan. Instead, I'm likely to say, "That's a fine choice you've made, there." My colleagues at the IBM Poughkeepsie (zEnterprise and z/OS) and Silicon Valley Labs (DB2 for z/OS), who've worked hard to make z/OS an excellent platform for Java applications, would wholeheartedly agree.
Monday, April 21, 2014
DB2 for z/OS: the Importance of Historical Performance Data
Just within the past couple of weeks, I've received questions of a similar nature from two different DB2 for z/OS people. In each message, a DBA described a change that he had made in a production DB2 environment (in one case a size increase for several buffer pools, and in the other a change from an external stored procedure to a native SQL procedure). The questions asked pertained to assessing the impact of the change on system and/or application performance.
I've been fielding questions of this nature for a long time. In every such instance, my first question is pretty much the same: do you have accounting and statistics trace information for the DB2 subsystem in question from before the time of the change? If the answer to that question is, "No," I know that assessing the impact of the DB2 system or application change will be less easy -- maybe a lot less easy -- than it otherwise would have been. In this blog entry, I hope to convey to you the importance of having historical DB2 for z/OS performance information on hand (ideally, information that is based on DB2 accounting and statistics trace information), and to provide you with some related guidance and recommendations.
Why you need "before" performance data
In a nutshell: because the value of your "after" data (meaning, from after the change that you made and want to assess) is considerably diminished if there's no "before" data that can be used for comparative purposes. This is true not only for situations in which you want to determine the performance impact of an alteration of a DB2 setting (e.g., a change to PGFIX(YES) for a buffer pool), or of some aspect of a database object's physical design (e.g., going to LOCKSIZE ROW for a table space) or of DB2-accessing application code (e.g., going to multi-row INSERT for a program) -- it's also applicable to situations in which something's gone wrong, performance-wise (perhaps a transaction's response time has quadrupled, or a batch job is running past a time specified in an SLA), and you need to get to the root of the problem. Online monitoring is great for seeing things from a "now" perspective, but sometimes you need the "then" view of things, too, to avoid going down dead-end streets in your pursuit of a solution.
Your historical data raw materials
First, you need to have DB2 writing trace records to data sets on disk -- not just to the in-memory buffers that are used by performance monitors. In my experience, DB2 trace records are most often written to SMF data sets (GTF is another option). As for the trace classes to have active, I'd go with accounting trace classes 1, 2, and 3, and statistics trace classes 1, 3, 4, 5, and 6 (class 5 is applicable to a DB2 data sharing system). The combined overhead of running all these trace classes should be less than 5%. There are organizations that like to have DB2 accounting trace classes 7 and 8 on, too, to provide package-level information. I agree that package-level figures can be very useful, but at some sites there is concern about the volume of trace records generated via accounting trace classes 7 and 8. If that's a concern at your shop, you could leave classes 7 and 8 off most of the time, and activate them on-demand if you have a need for package-level accounting data. Here's another idea: leave accounting trace classes 7 and 8 on, and use the new (with DB2 10) ZPARM parameter SMFCOMP to activate compression of DB2-generated SMF trace records. You're likely to get a disk space reduction of 60-80% for these compressed SMF records, and the CPU overhead associated with the compression work is likely to be very small (like 1% or less). Before compressing SMF trace records, verify that your DB2 monitor can handle SMF records in compressed form (IBM's OMEGAMON DB2 monitor can) -- if your monitor can't, you can use DB2 sample program DSNTSMFD, provided via APAR PM27872, to decompress DB2 SMF trace records that have been compressed).
Options for keeping historical performance data in a space-efficient way
With DB2 trace records being written to SMF data sets, you then need to decide how long to keep the data around. This is going to have something to do with the volume of trace data produced on a daily basis (workload dependent -- lots more DB2 trace data will be generated at some sites versus others), and the amount of space available for the data in your disk subsystem. Personally, I'd like to have at least 60 days of DB2 accounting and statistics trace data available to me -- 30 days at a minimum. What if you'd like to keep a rolling 60 days of DB2 trace data but don't have the space needed to hold that much in its raw SMF form (even with SMF data compression turned on)? In that case, you have at least a couple of options, which I'll briefly describe below.
Option 1: print to disk. Have a batch process kick off once each night to generate (by way of your DB2 monitor's batch reporting function) a standard set of accounting and statistics reports, and "print" those reports to disk. At a DB2-using site at which I worked in the early- and mid-2000s, we had such reports written to a set of GDGs, with each GDG having 60 data sets (thus, an automatically maintained rolling 60 days of reports). If you go with the "print to disk" approach, I'd recommend generating, each night (using the previous days' SMF records), the following reports:
Option 2: a performance database. Here, you'd use two functions of your monitor (if your DB2 monitor has these functions): a "reduce" function (probably, to filter and/or summarize information contained in the raw SMF trace records) and a "save" or "file" function. The "save" or "file" function would write data to a file, and you could subsequently use that file to load the DB2 tables that would comprise your performance database. If you go this route, you'll want to follow the directions provided in your monitor's documentation to create and populate the tables in the performance database. The performance database approach might be more involved than the print-to-disk option (in terms of set-up, that is), but once you get the prep work done and the data loaded, you'll have a lot of flexibility in terms of how you view and report on your historical DB2 performance data.
Information is power
When you have historical DB2 performance data at your fingertips, you'll be able to much more effectively point to the effects of your DB2 tuning changes, and say, "Our team did that." What was the impact of a buffer pool change on DB2 DBM1 address space CPU time? How did thread reuse increase when you set up some CICS-DB2 protected entry threads, and how did average in-DB2 CPU time change for transactions that use those protected threads to execute DB2 packages bound with RELEASE(DEALLOCATE)? How did the change to row-level locking for a particular set of table spaces affect deadlock and timeout totals, and how was per-transaction in-DB2 CPU time impacted? How did an increase in the value of the SRTPOOL parameter in ZPARM affect activity in the buffer pools used for the work file table spaces? How did an increase in the page size of a set of indexes affect the throughput of a certain batch job? How did leveraging of DB2 10 high-performance DBATs impact per-transaction in-DB2 CPU times for a DDF-using application? How did a change from an external stored procedure to a native SQL procedure affect zIIP offload for a DDF-using application? How did migration to DB2 11 from DB2 10 affect average in-DB2 CPU times for your applications? How did the archive and purge of "old and cold" data in DB2 tables affect GETPAGE counts in the system?
And so on, and so on. Yes, your DB2 monitor's "what's happening now" capability makes it a powerful tool, but combine that with some historical data, and watch that power grow exponentially. The cost is low, and the return is high. If you're not there, get busy.
I've been fielding questions of this nature for a long time. In every such instance, my first question is pretty much the same: do you have accounting and statistics trace information for the DB2 subsystem in question from before the time of the change? If the answer to that question is, "No," I know that assessing the impact of the DB2 system or application change will be less easy -- maybe a lot less easy -- than it otherwise would have been. In this blog entry, I hope to convey to you the importance of having historical DB2 for z/OS performance information on hand (ideally, information that is based on DB2 accounting and statistics trace information), and to provide you with some related guidance and recommendations.
Why you need "before" performance data
In a nutshell: because the value of your "after" data (meaning, from after the change that you made and want to assess) is considerably diminished if there's no "before" data that can be used for comparative purposes. This is true not only for situations in which you want to determine the performance impact of an alteration of a DB2 setting (e.g., a change to PGFIX(YES) for a buffer pool), or of some aspect of a database object's physical design (e.g., going to LOCKSIZE ROW for a table space) or of DB2-accessing application code (e.g., going to multi-row INSERT for a program) -- it's also applicable to situations in which something's gone wrong, performance-wise (perhaps a transaction's response time has quadrupled, or a batch job is running past a time specified in an SLA), and you need to get to the root of the problem. Online monitoring is great for seeing things from a "now" perspective, but sometimes you need the "then" view of things, too, to avoid going down dead-end streets in your pursuit of a solution.
Your historical data raw materials
First, you need to have DB2 writing trace records to data sets on disk -- not just to the in-memory buffers that are used by performance monitors. In my experience, DB2 trace records are most often written to SMF data sets (GTF is another option). As for the trace classes to have active, I'd go with accounting trace classes 1, 2, and 3, and statistics trace classes 1, 3, 4, 5, and 6 (class 5 is applicable to a DB2 data sharing system). The combined overhead of running all these trace classes should be less than 5%. There are organizations that like to have DB2 accounting trace classes 7 and 8 on, too, to provide package-level information. I agree that package-level figures can be very useful, but at some sites there is concern about the volume of trace records generated via accounting trace classes 7 and 8. If that's a concern at your shop, you could leave classes 7 and 8 off most of the time, and activate them on-demand if you have a need for package-level accounting data. Here's another idea: leave accounting trace classes 7 and 8 on, and use the new (with DB2 10) ZPARM parameter SMFCOMP to activate compression of DB2-generated SMF trace records. You're likely to get a disk space reduction of 60-80% for these compressed SMF records, and the CPU overhead associated with the compression work is likely to be very small (like 1% or less). Before compressing SMF trace records, verify that your DB2 monitor can handle SMF records in compressed form (IBM's OMEGAMON DB2 monitor can) -- if your monitor can't, you can use DB2 sample program DSNTSMFD, provided via APAR PM27872, to decompress DB2 SMF trace records that have been compressed).
Options for keeping historical performance data in a space-efficient way
With DB2 trace records being written to SMF data sets, you then need to decide how long to keep the data around. This is going to have something to do with the volume of trace data produced on a daily basis (workload dependent -- lots more DB2 trace data will be generated at some sites versus others), and the amount of space available for the data in your disk subsystem. Personally, I'd like to have at least 60 days of DB2 accounting and statistics trace data available to me -- 30 days at a minimum. What if you'd like to keep a rolling 60 days of DB2 trace data but don't have the space needed to hold that much in its raw SMF form (even with SMF data compression turned on)? In that case, you have at least a couple of options, which I'll briefly describe below.
Option 1: print to disk. Have a batch process kick off once each night to generate (by way of your DB2 monitor's batch reporting function) a standard set of accounting and statistics reports, and "print" those reports to disk. At a DB2-using site at which I worked in the early- and mid-2000s, we had such reports written to a set of GDGs, with each GDG having 60 data sets (thus, an automatically maintained rolling 60 days of reports). If you go with the "print to disk" approach, I'd recommend generating, each night (using the previous days' SMF records), the following reports:
- At least one statistics long report for each production DB2 subsystem. That report should have FROM and TO times that will capture 24 hours of system activity (midnight-to-midnight), and the data should be summarized at 1-hour intervals (so, one report would consist of twenty-four 1-hour subreports). If you are running DB2 in data sharing mode, in addition to member-level statistics long reports generate one group-level statistics long report for each production data sharing group.
- At least one accounting long report for each production DB2 subsystem. If you just generate one report, I'd go with a report that captures 24 hours of data (as with the previously mentioned statistics long report, this should be midnight-to-midnight, with data summarized at 1-hour intervals), and I'd have the data ordered by connection type (i.e., aggregated at the connection type level, so that I could see, for a given time period, the CICS-DB2 activity, the DRDA activity, the call attach activity, etc.). If you want to slice and dice things a little more finely, consider generating at least a couple more accounting long reports each night: 1) for your CICS-DB2 and batch-DB2 activity, consider a report that includes only the CICS and call attach connection types (and/or TSO attach, depending on how your batch jobs connect to DB2), and that orders information by correlation name (batch job names and CICS transaction names are correlation names in DB2 accounting lingo), so that you'll see detailed accounting data for each DB2-accessing batch job and CICS transaction; and 2) for your DDF activity, generate an accounting detail report that includes only the DRDA connection type, with data ordered by primary authorization ID (that should then show you information at the DDF-using application level if, as is typically the case, each such application has its own ID with which it connects to the DB2 server).
Option 2: a performance database. Here, you'd use two functions of your monitor (if your DB2 monitor has these functions): a "reduce" function (probably, to filter and/or summarize information contained in the raw SMF trace records) and a "save" or "file" function. The "save" or "file" function would write data to a file, and you could subsequently use that file to load the DB2 tables that would comprise your performance database. If you go this route, you'll want to follow the directions provided in your monitor's documentation to create and populate the tables in the performance database. The performance database approach might be more involved than the print-to-disk option (in terms of set-up, that is), but once you get the prep work done and the data loaded, you'll have a lot of flexibility in terms of how you view and report on your historical DB2 performance data.
Information is power
When you have historical DB2 performance data at your fingertips, you'll be able to much more effectively point to the effects of your DB2 tuning changes, and say, "Our team did that." What was the impact of a buffer pool change on DB2 DBM1 address space CPU time? How did thread reuse increase when you set up some CICS-DB2 protected entry threads, and how did average in-DB2 CPU time change for transactions that use those protected threads to execute DB2 packages bound with RELEASE(DEALLOCATE)? How did the change to row-level locking for a particular set of table spaces affect deadlock and timeout totals, and how was per-transaction in-DB2 CPU time impacted? How did an increase in the value of the SRTPOOL parameter in ZPARM affect activity in the buffer pools used for the work file table spaces? How did an increase in the page size of a set of indexes affect the throughput of a certain batch job? How did leveraging of DB2 10 high-performance DBATs impact per-transaction in-DB2 CPU times for a DDF-using application? How did a change from an external stored procedure to a native SQL procedure affect zIIP offload for a DDF-using application? How did migration to DB2 11 from DB2 10 affect average in-DB2 CPU times for your applications? How did the archive and purge of "old and cold" data in DB2 tables affect GETPAGE counts in the system?
And so on, and so on. Yes, your DB2 monitor's "what's happening now" capability makes it a powerful tool, but combine that with some historical data, and watch that power grow exponentially. The cost is low, and the return is high. If you're not there, get busy.
Friday, March 28, 2014
DB2 for z/OS People: Wearing Your "Applications" Hat Versus Your "Systems" Hat
Not long ago, a colleague of mine sent to me an e-mail message that he'd received from a person who supports DB2 for z/OS at a US-based company. In this message the IT professional wrote of some DB2-accessing application programs that were running into lock contention issues with page-level locking (the default) in effect. He knew that going to row-level locking for the target tables (accomplished by altering the associated table spaces with LOCKSIZE(ROW)) would likely resolve the locking conflicts that were negatively impacting throughput and response time for the aforementioned applications; however, he was reluctant to pursue this physical database design change because it would be a DB2 response to what he regarded as an application problem.
This person, in a well-meaning way, was wearing his "systems" hat. Wrong choice of figurative headwear, I'd say. It might have been the right hat to wear 10 or 20 years ago, but times have changed. These days, it is more important than ever for a mainframe DB2 person to reach first for his or her "applications" hat. Hereinafter I'll endeavor to explain the reasoning behind this contention of mine.
First, I have a "micro" response regarding the particular matter of page- versus row-level locking, and I believe that this will serve as a perfect lead-in to my take on the larger, "macro" issue which has to do with striking the proper balance between system- and application-focused thinking.
Row- versus page-level locking -- think about it
IBM invented relational database technology, and DB2 for z/OS was, in essence, the first commercial-grade, enterprise-level relational DBMS on the market (it was called DB2 for MVS back then). Page-level was initially the finest locking granularity available in a mainframe DB2 environment, and for years that worked just fine for all kinds of DB2-using organizations. In cases where lock contention problems arose with page-level locking in effect, programmers were often ready and willing to make application code changes that eliminated or at least minimized the occurrence of timeouts and/or deadlocks. On occasion, when an application code change was not feasible, a DB2 DBA would reduce effective lock granularity by limiting the number of rows that could be stored in a table space's pages (this via the MAXROWS option of ALTER TABLESPACE). The marching orders given to most DB2 for z/OS DBAs were, "Focus on making the DB2 workload as CPU-efficient as possible." DBAs heeded these orders, and so routinely wore their "systems" hat.
Time went by, and while DB2 for MVS (and then OS/390 and then z/OS) continued to grow in terms of installations and of data volumes managed and transaction volumes processed, relational database management software for distributed systems servers made the scene and eventually became quite popular. For these DBMSs, row-level was (and still is) the default locking granularity utilized. Page-level locking continued to be (and still is) the default for DB2 for z/OS, but a new index structure introduced with DB2 Version 4 made row-level locking an option for the mainframe platform.
Relational database technology evolved, and so did the ways in which people used it. An important development was the rise of vendor-supplied -- versus in-house-written -- application software. Companies producing this software understandably sought to develop code that would be suitable for use with a wide range of database management systems. That meant designing with row-level locking in mind -- the default in most of the relational database world.
The rise of vendor-supplied applications, of course, didn't mean the end of in-house application development. Organizations continued to build their own apps to address unique demands and opportunities in their respective markets, but in this sphere there was a pronounced shift towards multi-tiered, client-server applications and away from more monolithic application architectures. People developing these applications favored the use of data access interfaces, such as JDBC and ODBC, that worked across many different relational DBMSs. Developing in such a DBMS-agnostic way, programmers quite naturally gravitated towards coding with the most widely-used database locking granularity -- row-level -- in mind.
Fast-forward, then, to the present day. You have row-level-locking-using vendor-supplied applications, and you have in-house developers writing code with an implicit assumption of row-level locking granularity, and you have people who want to pair these purchased and in-house-written applications with DB2 for z/OS (which a friend of mine likes to call the "super-server"), and you have DB2 DBAs and systems programmers looking sternly out from under their "systems" hats and telling these people, "Row-level locking doesn't deliver optimal CPU efficiency. Come back to me after you've changed that application to work well with page-level locking."
EXCUSE ME? There are people coming to you, bearing applications that will drive workload growth on the platform you support, and you're giving them the cold shoulder because the preferred (and maybe required, absent code changes) locking mode might cause DB2-related CPU consumption to be a little higher than it otherwise would be? I have a different response to recommend. How about, "Row-level locking? No problem. Of course we can do that. We've been able to do that for about 20 years now. Works like a champ. Let's talk about setting up a testing environment for the application, and let's start putting a production implementation plan together."
On a macro level: technology enables applications
DB2 for z/OS features all kinds of great technology, and that story gets better with each new release of the software, but guess what? That advanced technology is valuable to an organization to the extent that it facilitates and accelerates the development of applications that make the organization money (enabling new revenue streams, upping business volumes from existing clients, growing the customer base, etc.) or reduce costs (better inventory management, reduced customer churn, reduction of fraud-related losses, etc.), or both; thus, your mindset as a DB2 support person should be about rolling out the red carpet to application developers (and to those interested in storing the data related to vendor-supplied applications in a DB2 for z/OS database). You should aim to make DB2 a preferred DBMS for your organization's programmers, and you don't get there by making those folks do things differently versus other data-serving platforms with which they work.
When you wear your "applications" hat, you think "accommodate" more than "challenge" when working with programmers. Supporting row-level locking is just one example of such accommodation. Another example would be creating an index on a column expression in order to make an otherwise non-indexable predicate indexable (and stage 1). Maybe you utilize concentrate-statements-with-literals functionality (introduced with DB2 10) to have DB2 parameterize dynamic SQL statements that were coded with literal values, when application-side statement parameterization is not feasible. Maybe you create an "instead of" trigger to allow programmers to code data-changing SQL statements targeting what would otherwise be a read-only view. Maybe you create a user-defined function that replicates the capability of a function that was useful to a development team when they worked with some other DBMS. Maybe you define a check constraint for a table to help ensure that only certain data values can be inserted into a column -- this so that programmers won't have to code that data domain-restricting logic themselves. There are many, many more ways in which you can use DB2 functionality to make life easier for application developers -- I've just scratched the surface here. Put on your "applications" hat and get creative and be proactive -- go to programmers with ideas, instead of just waiting for them to bring problems and challenges to you.
Now, reaching first for your "applications" hat doesn't mean throwing your "systems" hat away. Enhancing the CPU efficiency of the DB2 for z/OS workload at your site is a very worthwhile pursuit. That said, you should think about actions that could reduce per-transaction CPU consumption without requiring application code changes. Tuning steps of that nature include page-fixing buffer pools, combining persistent threads with RELEASE(DEALLOCATE) packages, and, in some cases, hash-organizing data in a table. Shoot, just rebinding packages when you go to a new release of DB2 can deliver significant CPU savings for your organization.
DB2 is a tool. When people approach you about using that tool to enable a new in-house-developed application, or to support a vendor-supplied application, you should be delighted, not bothered. Such requests give you the opportunity to show what DB2 for z/OS can do as a data server. Consider DB2 features and functions while wearing your "applications" hat, and you'll be that much more effective in leveraging DB2 technology for the benefit of developers, and to provide an ideal data-serving system for vendor-supplied applications. DB2 for z/OS really is the "super server" -- tops in availability, scalability, and security. Don't stand between it and your organization's applications folks. Be a bridge, not a barrier.
This person, in a well-meaning way, was wearing his "systems" hat. Wrong choice of figurative headwear, I'd say. It might have been the right hat to wear 10 or 20 years ago, but times have changed. These days, it is more important than ever for a mainframe DB2 person to reach first for his or her "applications" hat. Hereinafter I'll endeavor to explain the reasoning behind this contention of mine.
First, I have a "micro" response regarding the particular matter of page- versus row-level locking, and I believe that this will serve as a perfect lead-in to my take on the larger, "macro" issue which has to do with striking the proper balance between system- and application-focused thinking.
Row- versus page-level locking -- think about it
IBM invented relational database technology, and DB2 for z/OS was, in essence, the first commercial-grade, enterprise-level relational DBMS on the market (it was called DB2 for MVS back then). Page-level was initially the finest locking granularity available in a mainframe DB2 environment, and for years that worked just fine for all kinds of DB2-using organizations. In cases where lock contention problems arose with page-level locking in effect, programmers were often ready and willing to make application code changes that eliminated or at least minimized the occurrence of timeouts and/or deadlocks. On occasion, when an application code change was not feasible, a DB2 DBA would reduce effective lock granularity by limiting the number of rows that could be stored in a table space's pages (this via the MAXROWS option of ALTER TABLESPACE). The marching orders given to most DB2 for z/OS DBAs were, "Focus on making the DB2 workload as CPU-efficient as possible." DBAs heeded these orders, and so routinely wore their "systems" hat.
Time went by, and while DB2 for MVS (and then OS/390 and then z/OS) continued to grow in terms of installations and of data volumes managed and transaction volumes processed, relational database management software for distributed systems servers made the scene and eventually became quite popular. For these DBMSs, row-level was (and still is) the default locking granularity utilized. Page-level locking continued to be (and still is) the default for DB2 for z/OS, but a new index structure introduced with DB2 Version 4 made row-level locking an option for the mainframe platform.
Relational database technology evolved, and so did the ways in which people used it. An important development was the rise of vendor-supplied -- versus in-house-written -- application software. Companies producing this software understandably sought to develop code that would be suitable for use with a wide range of database management systems. That meant designing with row-level locking in mind -- the default in most of the relational database world.
The rise of vendor-supplied applications, of course, didn't mean the end of in-house application development. Organizations continued to build their own apps to address unique demands and opportunities in their respective markets, but in this sphere there was a pronounced shift towards multi-tiered, client-server applications and away from more monolithic application architectures. People developing these applications favored the use of data access interfaces, such as JDBC and ODBC, that worked across many different relational DBMSs. Developing in such a DBMS-agnostic way, programmers quite naturally gravitated towards coding with the most widely-used database locking granularity -- row-level -- in mind.
Fast-forward, then, to the present day. You have row-level-locking-using vendor-supplied applications, and you have in-house developers writing code with an implicit assumption of row-level locking granularity, and you have people who want to pair these purchased and in-house-written applications with DB2 for z/OS (which a friend of mine likes to call the "super-server"), and you have DB2 DBAs and systems programmers looking sternly out from under their "systems" hats and telling these people, "Row-level locking doesn't deliver optimal CPU efficiency. Come back to me after you've changed that application to work well with page-level locking."
EXCUSE ME? There are people coming to you, bearing applications that will drive workload growth on the platform you support, and you're giving them the cold shoulder because the preferred (and maybe required, absent code changes) locking mode might cause DB2-related CPU consumption to be a little higher than it otherwise would be? I have a different response to recommend. How about, "Row-level locking? No problem. Of course we can do that. We've been able to do that for about 20 years now. Works like a champ. Let's talk about setting up a testing environment for the application, and let's start putting a production implementation plan together."
On a macro level: technology enables applications
DB2 for z/OS features all kinds of great technology, and that story gets better with each new release of the software, but guess what? That advanced technology is valuable to an organization to the extent that it facilitates and accelerates the development of applications that make the organization money (enabling new revenue streams, upping business volumes from existing clients, growing the customer base, etc.) or reduce costs (better inventory management, reduced customer churn, reduction of fraud-related losses, etc.), or both; thus, your mindset as a DB2 support person should be about rolling out the red carpet to application developers (and to those interested in storing the data related to vendor-supplied applications in a DB2 for z/OS database). You should aim to make DB2 a preferred DBMS for your organization's programmers, and you don't get there by making those folks do things differently versus other data-serving platforms with which they work.
When you wear your "applications" hat, you think "accommodate" more than "challenge" when working with programmers. Supporting row-level locking is just one example of such accommodation. Another example would be creating an index on a column expression in order to make an otherwise non-indexable predicate indexable (and stage 1). Maybe you utilize concentrate-statements-with-literals functionality (introduced with DB2 10) to have DB2 parameterize dynamic SQL statements that were coded with literal values, when application-side statement parameterization is not feasible. Maybe you create an "instead of" trigger to allow programmers to code data-changing SQL statements targeting what would otherwise be a read-only view. Maybe you create a user-defined function that replicates the capability of a function that was useful to a development team when they worked with some other DBMS. Maybe you define a check constraint for a table to help ensure that only certain data values can be inserted into a column -- this so that programmers won't have to code that data domain-restricting logic themselves. There are many, many more ways in which you can use DB2 functionality to make life easier for application developers -- I've just scratched the surface here. Put on your "applications" hat and get creative and be proactive -- go to programmers with ideas, instead of just waiting for them to bring problems and challenges to you.
Now, reaching first for your "applications" hat doesn't mean throwing your "systems" hat away. Enhancing the CPU efficiency of the DB2 for z/OS workload at your site is a very worthwhile pursuit. That said, you should think about actions that could reduce per-transaction CPU consumption without requiring application code changes. Tuning steps of that nature include page-fixing buffer pools, combining persistent threads with RELEASE(DEALLOCATE) packages, and, in some cases, hash-organizing data in a table. Shoot, just rebinding packages when you go to a new release of DB2 can deliver significant CPU savings for your organization.
DB2 is a tool. When people approach you about using that tool to enable a new in-house-developed application, or to support a vendor-supplied application, you should be delighted, not bothered. Such requests give you the opportunity to show what DB2 for z/OS can do as a data server. Consider DB2 features and functions while wearing your "applications" hat, and you'll be that much more effective in leveraging DB2 technology for the benefit of developers, and to provide an ideal data-serving system for vendor-supplied applications. DB2 for z/OS really is the "super server" -- tops in availability, scalability, and security. Don't stand between it and your organization's applications folks. Be a bridge, not a barrier.
Saturday, March 1, 2014
DB2 for z/OS SQL PL Routines: REBIND PACKAGE vs. ALTER REGENERATE
I have long been a big fan of DB2 for z/OS native SQL procedures, but I also recognized early on that putting this technology to use would require DB2 DBAs to learn some new things. Much of this newness has to do with the fact that for a native SQL procedure (and for its more recently arrived cousin, the "native" SQL scalar function, also known as a compiled SQL scalar function or a non-inline SQL scalar function), there is no external-to-DB2 executable -- no object module, no load module. There is just the SQL PL routine's package (SQL PL is the language in which native SQL procedures and non-inlne SQL scalar functions are written). The package is the executable, period. That being the case, managing these packages is a different game versus managing a package that is tied to (for example) the external-to-DB2 load module of a COBOL stored procedure program. Think about making a package "active" in a DB2 for z/OS system. In the context of a load module-tied package, that's familiar territory: you bind the package into the target DB2 environment, and it doesn't get "activated" (i.e., it's not used) until the associated load module (batch job, CICS transaction, external stored procedure, whatever) is executed in that environment. But what if the package itself is the sole executable (the case, as previously mentioned, for a native SQL procedure or non-inlined SQL scalar function)? What then? Well, that's what the DEPLOY option of BIND PACKAGE, and the ACTIVATE VERSION option of ALTER PROCEDURE and ALTER FUNCTION, are for, as I pointed out in a blog entry (written while I was working as an independent consultant) that I posted back in 2009. In the blog entry I'm writing right now, I want to provide you with information about another new wrinkle associated SQL PL routines: "full" versus "partial" package bind.
"Say what?" you might be thinking. "You don't 'partially' bind a package. You bind the whole thing." That WAS true before SQL PL routines came along (and with "SQL routines" I am NOT referring to external SQL procedures, which are written in SQL PL but become C language programs with embedded SQL DML statements in the course of being prepared for execution). When the package in question is related to a native SQL procedure or a non-inline SQL scalar user-defined function (UDF), a REBIND PACKAGE operation in fact rebinds only part of the package. To understand why this is so, consider that the package for a SQL PL routine has a section that corresponds to the SQL "control" statements in the routine (i.e., the logic flow control statements such as IF, WHILE, ITERATE, and LOOP), and another section that pertains to the SQL DML statements (e.g., SELECT, INSERT, UPDATE, DELETE) in the native SQL procedure or non-inline SQL scalar UDF. Generally speaking, a REBIND PACKAGE is executed so as to drive reoptimization for SQL DML statements. If, say, a new index is added to a table to provide a better-performing access path for a static SQL statement embedded in a transaction program, DB2 will not use the new index in executing the statement unless the package of which the statement is a part is rebound. But what if the package is, essentially, the compiled form of a native SQL procedure or a non-inline SQL scalar UDF? In that case, if you want to drive reoptimization of DML statements included in the SQL procedure or UDF, you don't necessarily want the control statements in the SQL PL routine reworked, and so REBIND PACKAGE for a SQL PL routine leaves the control-statement section of the package alone and rebinds only the DML section.
What if you DO want a SQL PL routine's package to be rebound in its entirety? That can be done by way of an ALTER PROCEDURE (or ALTER FUNCTION) statement with the REGENERATE option specified. The effect of that statement's execution will be a rebinding of the whole of the package -- the SQL control statements as well as the DML statements. Why might you want to use ALTER PROCEDURE (or ALTER FUNCTION) with REGENERATE versus REBIND PACKAGE for the package associated with a SQL PL routine? One reason would be to rebind the control statements of a routine so as to get the advantage of a SQL PL performance enhancement (an example of such an enhancement would be the reduced path length of IF statement execution delivered with DB2 10 for z/OS). Here's another reason to use ALTER PROCEDURE (or FUNCTION) with REGENERATE: in a DB2 10 (or later) environment, it gets the bulk of the control section of the package, as well as the DML section, copied into above-the-bar virtual storage in the DB2 database services address space (DBM1) when the package is executed (i.e., when it's allocated to a thread). If a native SQL procedure created in a DB2 9 system is not either regenerated (via ALTER PROCEDURE with REGENERATE) or recreated in a DB2 10 (or later) system, the control section of the procedure's package will occupy more below-the-bar virtual storage when allocated to a thread.
Note that the REBIND PACKAGE option APREUSE, which tells DB2 to reuse the existing access path (when possible) for SQL DML statements when a package is rebound, is not an option that can be specified in an ALTER PROCEDURE (or FUNCTION) statement; so, when you execute ALTER PROCEDURE (or FUNCTION) with REGENERATE, access path changes for SQL DML statements in the procedure or function are a possibility.
Now you know (if you didn't already): with respect to a package associated with a native SQL procedure or a non-inline SQL scalar UDF, use REBIND PACKAGE when all you want to do is rebind the part of the package pertaining to the SQL DML statements in the procedure or function. To rebind all of the package's statements -- control statements as well as DML statements -- use ALTER PROCEDURE or ALTER FUNCTION with the REGENERATE option.
"Say what?" you might be thinking. "You don't 'partially' bind a package. You bind the whole thing." That WAS true before SQL PL routines came along (and with "SQL routines" I am NOT referring to external SQL procedures, which are written in SQL PL but become C language programs with embedded SQL DML statements in the course of being prepared for execution). When the package in question is related to a native SQL procedure or a non-inline SQL scalar user-defined function (UDF), a REBIND PACKAGE operation in fact rebinds only part of the package. To understand why this is so, consider that the package for a SQL PL routine has a section that corresponds to the SQL "control" statements in the routine (i.e., the logic flow control statements such as IF, WHILE, ITERATE, and LOOP), and another section that pertains to the SQL DML statements (e.g., SELECT, INSERT, UPDATE, DELETE) in the native SQL procedure or non-inline SQL scalar UDF. Generally speaking, a REBIND PACKAGE is executed so as to drive reoptimization for SQL DML statements. If, say, a new index is added to a table to provide a better-performing access path for a static SQL statement embedded in a transaction program, DB2 will not use the new index in executing the statement unless the package of which the statement is a part is rebound. But what if the package is, essentially, the compiled form of a native SQL procedure or a non-inline SQL scalar UDF? In that case, if you want to drive reoptimization of DML statements included in the SQL procedure or UDF, you don't necessarily want the control statements in the SQL PL routine reworked, and so REBIND PACKAGE for a SQL PL routine leaves the control-statement section of the package alone and rebinds only the DML section.
What if you DO want a SQL PL routine's package to be rebound in its entirety? That can be done by way of an ALTER PROCEDURE (or ALTER FUNCTION) statement with the REGENERATE option specified. The effect of that statement's execution will be a rebinding of the whole of the package -- the SQL control statements as well as the DML statements. Why might you want to use ALTER PROCEDURE (or ALTER FUNCTION) with REGENERATE versus REBIND PACKAGE for the package associated with a SQL PL routine? One reason would be to rebind the control statements of a routine so as to get the advantage of a SQL PL performance enhancement (an example of such an enhancement would be the reduced path length of IF statement execution delivered with DB2 10 for z/OS). Here's another reason to use ALTER PROCEDURE (or FUNCTION) with REGENERATE: in a DB2 10 (or later) environment, it gets the bulk of the control section of the package, as well as the DML section, copied into above-the-bar virtual storage in the DB2 database services address space (DBM1) when the package is executed (i.e., when it's allocated to a thread). If a native SQL procedure created in a DB2 9 system is not either regenerated (via ALTER PROCEDURE with REGENERATE) or recreated in a DB2 10 (or later) system, the control section of the procedure's package will occupy more below-the-bar virtual storage when allocated to a thread.
Note that the REBIND PACKAGE option APREUSE, which tells DB2 to reuse the existing access path (when possible) for SQL DML statements when a package is rebound, is not an option that can be specified in an ALTER PROCEDURE (or FUNCTION) statement; so, when you execute ALTER PROCEDURE (or FUNCTION) with REGENERATE, access path changes for SQL DML statements in the procedure or function are a possibility.
Now you know (if you didn't already): with respect to a package associated with a native SQL procedure or a non-inline SQL scalar UDF, use REBIND PACKAGE when all you want to do is rebind the part of the package pertaining to the SQL DML statements in the procedure or function. To rebind all of the package's statements -- control statements as well as DML statements -- use ALTER PROCEDURE or ALTER FUNCTION with the REGENERATE option.
Tuesday, February 11, 2014
DB2 11 for z/OS Bind Concurrency - Even Better Than You Think*
For the asterisk at the end of the this blog entry's title, you could substitute, "If what you think is based on what you've likely read or heard about this feature up till now." In the space below, I'll provide some background information on DB2 11's bind concurrency enhancement, and then I'll give you the bonus insight that you might see as the "cherry on top" -- the important scenario that is addressed by DB2 11 bind concurrency but which has received little (if any) coverage in documents and presentations that I've seen to date.
OK, background: first, the term "bind concurrency" is kind of a nickname for the DB2 11 feature of interest here. A longer and more comprehensively descriptive name for the enhancement could be worded as follows: "the capability through which BIND, REBIND, DDL, and utility operations can execute successfully in a DB2 11 new-function mode environment when they would otherwise have been blocked by RELEASE(DEALLOCATE) packages executed via persistent threads (or blocked by another circumstance)." That's a mouthful. You can see why people use the short descriptor, "bind concurrency." That "other circumstance" in the italicized parenthetical phrase has to do with the "cherry on top" aspect of bind concurrency to which I referred previously, and which I'll describe momentarily.
About RELEASE(DEALLOCATE) and persistent threads: this is a combination that has long been utilized at DB2 sites to improve the CPU efficiency of DB2-accessing applications. It's something about which I've blogged a number of times over the years, most recently in an entry posted here just a couple of weeks ago. A persistent thread (i.e., one that persists through commits) can stick around for quite some time, and therein lies a rub that can be encountered when a package bound with RELEASE(DEALLOCATE) is executed by way of such a thread: the package is considered to be in-use as long as the thread exists, because it won't be "released" until thread deallocation time (versus being released at each commit). This potentially lengthy and continuous (for the life of the thread) "use" of the package (not to be confused with "execution" of the package -- a package, once executed through a persistent thread, retains "in use" status until thread deallocation, even if the thread won't be deallocated for a while after package execution has completed) can interfere with a variety of database administration operations. For example, a package cannot be rebound while it is in use. A DDL operation that would cause a dependent package to become invalidated, such as adding a partition to a range-partitioned table, will not succeed while the package is in use, because a package can't be invalidated while it's in use. Similarly, a utility operation that would cause a package to be invalidated, such as an online REORG executed for the purpose of materializing a pending DDL action, will not succeed if the package is in use.
So, RELEASE(DEALLOCATE) combined with persistent threads is a CPU efficiency booster, but from a DB2 DBA's perspective it can gum up the system in some respects. This flip side of RELEASE(DEALLOCATE) plus persistent threads was addressed nicely for high-performance DBATs (a type of persistent thread introduced with DB2 10 for z/OS) through the introduction of the PKGREL keyword of the -MODIFY DDF command: when you need a "window" in which to perform bind/rebind, DDL, or utility operations that might be blocked by high-performance DBATs, you issue the command -MODIFY DDF PKGREL(COMMIT) and that effectively turns off high-performance DBATs until further notice (that "further notice" is indicated via issuance of the command -MODIFY DDF PKGREL(BNDOPT), which re-enables high-performance DBAT functionality).
That's nice for high-performance DBATs, but what if RELEASE(DEALLOCATE) packages are used with other persistent thread types, such as CICS-DB2 protected entry threads or long-running batch jobs (a batch job's thread doesn't go away until the job completes, regardless of the number of commits issued in the course of the job's execution)? That's where DB2 11 bind concurrency comes in. DB2 11 for z/OS introduces a new ZPARM parameter, PKGREL_COMMIT, that can be set to YES (the default) or NO. In a DB2 11 new-function mode environment, PKGREL_COMMIT YES has this effect: when a BIND/REBIND, DDL, or utility operation would be blocked because of a RELEASE(DEALLOCATE) package tied to a persistent thread, a flag is set for that package and any persistent thread to which the package is allocated will automatically switch to RELEASE(COMMIT) behavior for the package at the next commit (or rollback). Voila - the package is released when associated persistent threads commit, and the BIND/REBIND, DDL, or utility operation that would otherwise have hit a package-related concurrency wall can instead execute to completion. Pretty slick, eh?
There are a few caveats I should mention: PKGREL_COMMIT YES does not free up a RELEASE(DEALLOCATE) package at the next commit point for a persistent thread when 1) the package has a WITH HOLD cursor that is open at the time of the next commit, or 2) the package was bound with KEEPDYNAMIC(YES), or 3) the next commit (or rollback) is issued by a stored procedure.
Now for the promised cherry on top: in looking at documentation and/or presentations that describe DB2 11 bind concurrency, you may find yourself thinking:
"Nice feature, but it appears to be applicable only to situations involving RELEASE(DEALLOCATE) packages and persistent threads. In my DB2 system, we've run into package-related bind, DDL, and utility concurrency problems that have nothing to do with RELEASE(DEALLOCATE) and persistent threads. These cases involve packages executed by high-volume transactions. We have trouble sometimes getting bind, DDL, and utility operations to go through to successful completion. These operations get blocked by an in-use package. Even though the package is bound with RELEASE(COMMIT) and is not executed by way of persistent threads, it is in-use for long stretches of time because there is always at least one transaction using it at any given time. In other words, executions of some of our high-volume transactions overlap so that the use count for associated packages rarely goes to zero. When are we going to get some relief from that problem?"
Guess what? You have gotten relief from that problem, and the relief is in the form of the DB2 11 bind concurrency feature about which I've been writing in this blog entry. See, even though the write-up -- in documents and presentations -- about DB2 11's bind concurrency feature always (as far as I've seen) mentions RELEASE(DEALLOCATE) and persistent threads, that's NOT the only scenario to which bind concurrency is applicable. It also addresses bind, DDL and utility blockage situations caused by in-use RELEASE(COMMIT) packages that stay in-use for long stretches of time because of overlapping executions of related transactions. Bind concurrency functionality does this by providing something akin to a drain locking mechanism for packages (drain locking, introduced way back in the nineties, pertains to table spaces and indexes and partitions of same, and is used by DB2 utilities and also some commands and SQL statements). When PKGREL_COMMIT YES is in effect and an in-use package needs to go out-of-use to allow a bind or DDL or utility operation to complete, the package in question is flagged by DB2 and any transaction that would use the package will hold off on using the package as long as the flag remains in place; thus, as in-flight transactions associated with a flagged package hit commit points and other transactions hold off on using the package, the package's use count goes to zero and the bind or DDL or utility operation that is waiting to proceed gets the green light and gets done. After that, normal application processing resumes.
And there you have it: DB2 11 bind concurrency is even better than advertised. If you're not running DB2 11 yet, this gives you one more reason to get going with migration planning.
OK, background: first, the term "bind concurrency" is kind of a nickname for the DB2 11 feature of interest here. A longer and more comprehensively descriptive name for the enhancement could be worded as follows: "the capability through which BIND, REBIND, DDL, and utility operations can execute successfully in a DB2 11 new-function mode environment when they would otherwise have been blocked by RELEASE(DEALLOCATE) packages executed via persistent threads (or blocked by another circumstance)." That's a mouthful. You can see why people use the short descriptor, "bind concurrency." That "other circumstance" in the italicized parenthetical phrase has to do with the "cherry on top" aspect of bind concurrency to which I referred previously, and which I'll describe momentarily.
About RELEASE(DEALLOCATE) and persistent threads: this is a combination that has long been utilized at DB2 sites to improve the CPU efficiency of DB2-accessing applications. It's something about which I've blogged a number of times over the years, most recently in an entry posted here just a couple of weeks ago. A persistent thread (i.e., one that persists through commits) can stick around for quite some time, and therein lies a rub that can be encountered when a package bound with RELEASE(DEALLOCATE) is executed by way of such a thread: the package is considered to be in-use as long as the thread exists, because it won't be "released" until thread deallocation time (versus being released at each commit). This potentially lengthy and continuous (for the life of the thread) "use" of the package (not to be confused with "execution" of the package -- a package, once executed through a persistent thread, retains "in use" status until thread deallocation, even if the thread won't be deallocated for a while after package execution has completed) can interfere with a variety of database administration operations. For example, a package cannot be rebound while it is in use. A DDL operation that would cause a dependent package to become invalidated, such as adding a partition to a range-partitioned table, will not succeed while the package is in use, because a package can't be invalidated while it's in use. Similarly, a utility operation that would cause a package to be invalidated, such as an online REORG executed for the purpose of materializing a pending DDL action, will not succeed if the package is in use.
So, RELEASE(DEALLOCATE) combined with persistent threads is a CPU efficiency booster, but from a DB2 DBA's perspective it can gum up the system in some respects. This flip side of RELEASE(DEALLOCATE) plus persistent threads was addressed nicely for high-performance DBATs (a type of persistent thread introduced with DB2 10 for z/OS) through the introduction of the PKGREL keyword of the -MODIFY DDF command: when you need a "window" in which to perform bind/rebind, DDL, or utility operations that might be blocked by high-performance DBATs, you issue the command -MODIFY DDF PKGREL(COMMIT) and that effectively turns off high-performance DBATs until further notice (that "further notice" is indicated via issuance of the command -MODIFY DDF PKGREL(BNDOPT), which re-enables high-performance DBAT functionality).
That's nice for high-performance DBATs, but what if RELEASE(DEALLOCATE) packages are used with other persistent thread types, such as CICS-DB2 protected entry threads or long-running batch jobs (a batch job's thread doesn't go away until the job completes, regardless of the number of commits issued in the course of the job's execution)? That's where DB2 11 bind concurrency comes in. DB2 11 for z/OS introduces a new ZPARM parameter, PKGREL_COMMIT, that can be set to YES (the default) or NO. In a DB2 11 new-function mode environment, PKGREL_COMMIT YES has this effect: when a BIND/REBIND, DDL, or utility operation would be blocked because of a RELEASE(DEALLOCATE) package tied to a persistent thread, a flag is set for that package and any persistent thread to which the package is allocated will automatically switch to RELEASE(COMMIT) behavior for the package at the next commit (or rollback). Voila - the package is released when associated persistent threads commit, and the BIND/REBIND, DDL, or utility operation that would otherwise have hit a package-related concurrency wall can instead execute to completion. Pretty slick, eh?
There are a few caveats I should mention: PKGREL_COMMIT YES does not free up a RELEASE(DEALLOCATE) package at the next commit point for a persistent thread when 1) the package has a WITH HOLD cursor that is open at the time of the next commit, or 2) the package was bound with KEEPDYNAMIC(YES), or 3) the next commit (or rollback) is issued by a stored procedure.
Now for the promised cherry on top: in looking at documentation and/or presentations that describe DB2 11 bind concurrency, you may find yourself thinking:
"Nice feature, but it appears to be applicable only to situations involving RELEASE(DEALLOCATE) packages and persistent threads. In my DB2 system, we've run into package-related bind, DDL, and utility concurrency problems that have nothing to do with RELEASE(DEALLOCATE) and persistent threads. These cases involve packages executed by high-volume transactions. We have trouble sometimes getting bind, DDL, and utility operations to go through to successful completion. These operations get blocked by an in-use package. Even though the package is bound with RELEASE(COMMIT) and is not executed by way of persistent threads, it is in-use for long stretches of time because there is always at least one transaction using it at any given time. In other words, executions of some of our high-volume transactions overlap so that the use count for associated packages rarely goes to zero. When are we going to get some relief from that problem?"
Guess what? You have gotten relief from that problem, and the relief is in the form of the DB2 11 bind concurrency feature about which I've been writing in this blog entry. See, even though the write-up -- in documents and presentations -- about DB2 11's bind concurrency feature always (as far as I've seen) mentions RELEASE(DEALLOCATE) and persistent threads, that's NOT the only scenario to which bind concurrency is applicable. It also addresses bind, DDL and utility blockage situations caused by in-use RELEASE(COMMIT) packages that stay in-use for long stretches of time because of overlapping executions of related transactions. Bind concurrency functionality does this by providing something akin to a drain locking mechanism for packages (drain locking, introduced way back in the nineties, pertains to table spaces and indexes and partitions of same, and is used by DB2 utilities and also some commands and SQL statements). When PKGREL_COMMIT YES is in effect and an in-use package needs to go out-of-use to allow a bind or DDL or utility operation to complete, the package in question is flagged by DB2 and any transaction that would use the package will hold off on using the package as long as the flag remains in place; thus, as in-flight transactions associated with a flagged package hit commit points and other transactions hold off on using the package, the package's use count goes to zero and the bind or DDL or utility operation that is waiting to proceed gets the green light and gets done. After that, normal application processing resumes.
And there you have it: DB2 11 bind concurrency is even better than advertised. If you're not running DB2 11 yet, this gives you one more reason to get going with migration planning.
Subscribe to:
Posts (Atom)