When I say "bigger," I don't mean bigger in a "footprint" sense -- the new flagship system in IBM's mainframe server product line is very close, size-wise, to its predecessor, the z196, and has similar power draw and heat load characteristics. What I mean by "bigger"is that the zEnterprise EC12 provides the ability to get more work done, in less time, more efficiently than ever before. That's good news for organizations that need best-of-breed enterprise computing capabilities. It's also good news for those of us who comprise the DB2 for z/OS community: the horse that our favorite DBMS rides is, in the form of the EC12, a thoroughbred like no other.
Continuing with the equine analogy, let's start with a look at the raw horsepower numbers. The EC12's engines run at 5.5 GHz, the highest clock frequency in the industry (the frequency of a z196 engine is 5.2 GHz). That's impressive enough, but the EC12 delivers a per-core processing capacity advantage over the z196 -- up to 25% -- that is significantly greater than suggested just by the difference in engine speed. Very fast processors are only part of the EC12 performance story. There's also a doubling of the cache memory on each processor chip, which reduces delays caused by having to go "off chip" for instructions and data.
Not only are there faster engines that can be more effectively kept busy, there are more engines: a single EC12 server can have up to 120 cores, up to 101 of which can be configured for client use (versus a maximum of 80 configurable cores on a z196 server). Put it all together (a larger number of faster engines), and a fully configured EC12 can deliver 50% more processing capacity than a top-end z196 server.
The total amount of memory available on an EC12 server -- 3 TB -- is the same as for the z196, but something new and very cool is being done with EC12 memory: it can be managed in 2 GB page frames. Yes, that is 2 GB, as in 2000 times the size of the 1 MB page frames available on z196 servers. What does that mean for DB2? Think about a page-fixed buffer pool of 2 GB in size (e.g., 500,000 4K buffers) fitting into ONE real storage page frame. Virtual-to-real storage translation will be more efficient than ever, and that will translate into CPU savings. If you've ever wondered, as a mainframe DB2 person, whether or not the age of Big Memory has really begun, wonder no more. It's here. Get ready to exploit it, if you haven't already.
You might find yourself thinking, "What would my company do with a server that has that much memory and that much processing capacity?" In that case, I have one word for you: virtualization. Don't limit your thinking of EC12 exploitation to one or just a few system images. Think LOTS of system images. Lots of z/OS LPARs on an EC12? Maybe, but I'm thinking more along Linux lines when I think of lots of virtual systems running on a single EC12 box. If your organization isn't running Linux on System z, or thinking seriously about doing so, you're missing a train on which a whole lot of people are traveling. Installation of IFL engines on zEnterprise servers (these are specialty engines that run the Linux operating system) is soaring, and with good reason. The mainframe is a great virtual-system platform (has been for decades, since long before "cloud" mean anything other than coalesced water vapor), and if you have a big and growing DB2 for z/OS client-server workload (as many organizations do), what better place is there for your DB2-accessing application servers than in a Linux image on the same System z server in which DB2 is running? Ever heard of HiperSockets? Network performance doesn't get any better than that. And, think of the benefits in terms of network simplification (and security) when app servers run under Linux on System z, instead of on outboard boxes. With the EC12, the already great System z virtualization story gets better still.
Virtualization's hot, and so is analytics. How about analytics on z? I'm not talking just about having the data accessed by your analytics tools housed in a mainframe database -- we already know that DB2 for z/OS is a great solution there. I'm talking about having the analytics tools themselves running on System z -- in a Linux image or a z/OS LPAR. More and more organizations are doing just that, and the EC12 will provide a related momentum boost. How's that? Well, as you get into progressively higher-value types of analytics -- from "what happened" reporting to "what will happen" predictive modeling -- then you find that the associated processing gets to be more and more CPU-intensive. The EC12 delivers here with significant improvements in performance for compute-intensive and floating-point applications. C and C++ on z? Java on z? Yes. The EC12 is the best mainframe server yet for business analytics.
Excellent performance across a wide range of applications is great, but we all know that a system has to be up if it's to deliver the processing power your organization needs. System z has always been the high-availability champ, and the EC12 takes high availability to a whole new level with zAware (short for System z Advanced Workload Analysis Reporter). zAware provides what one of my colleagues has termed "integrated expert system diagnostics," constantly monitoring OPERLOG messages (which at some sites can number in the millions per day) and presenting related information via an easy-to-interpret graphical user interface. zAware can help mainframe operations personnel to quickly determine when an EC12 system is not behaving normally -- thereby enabling corrective action to be taken before a situation reaches user-impacting proportions.
I've covered a lot in this blog post, but you can learn more about the zEnterprise EC12 and I encourage you to do so. Use the hyperlink I provided at the top of this entry, check out the IBM EC12 Technical Introduction and EC12 Technical Guide "red books," and look for presentations at national conferences and local events. The more you know, the more you'll realize that Big Iron never looked better.
Tuesday, August 28, 2012
Monday, August 20, 2012
When CICS and DB2 for z/OS Monitors Disagree (Part 2)
About a week ago, in part 1 of this two-part blog entry, I described a situation I've encountered several times at mainframe DB2 sites over the past 20 years: CICS users complain of elongated response times for DB2-accessing transactions, and these users' complaints are backed up by CICS monitor data that indicate elevated "wait for DB2" times as being the cause of the performance degradation. Concurrently, DB2 systems people point to their own data, from a DB2 monitor, showing that in-DB2 time for CICS transactions is NOT significantly higher than normal during times when the CICS programs are running long. Finger-pointing can ensue, and frustration can build in the face of a seemingly intractable problem: how can it be that the CICS monitor shows higher "wait for DB2" times, while according to DB2 monitor data the performance picture looks good? I noted in the aforementioned part 1 blog entry that this situation can occur when there is either a shortage of DB2 threads available for CICS-DB2 transactions or a shortage of TCBs through which the DB2 threads are utilized. In this part 2 entry I'll shine a light on another possibility: the disagreement between CICS and DB2 monitors can be caused by inappropriate priority specifications for DB2 address spaces and/or CICS transactions.
The priority to which I'm referring is dispatching priority -- the kind that determines which of several "in and ready" tasks in a z/OS system will get CPU cycles first. Dispatching priorities for address spaces are specified in the WLM policy of the z/OS LPAR in which they run. The busier a system is (with respect to CPU utilization), the more important it is to get the address space dispatching priorities right. DB2's IRLM address space (the lock manager) should have a very high priority, and in fact should be assigned to the SYSSTC service class (for ultra-high-priority address spaces). Most sites get this right. The mistake that I've seen more than once is giving the DB2 system services and database services address spaces (aka MSTR and DBM1) a priority that is lower than that of the CICS application owning regions (AORs) in the z/OS LPAR. The thinking here is often something like this: "If the DB2 MSTR and DBM1 address spaces have a higher priority than the CICS AORs, they will get in the way of CICS transactions and performance will be the worse for it." Not so. The DB2 "system" address spaces generally consume little in the way of CPU time (DDF can be a different animal in this regard, as I pointed out in an entry posted to this blog last month), so "getting in the way" of CICS regions is not an issue. In fact, it's when CICS regions "get in the way" of the DB2 system address spaces (as they can in a busy system when they have a higher-than-DB2 priority), that CICS transaction performance can go downhill.
How's that? Well, it can come down to a thread-availability issue. It's common for CICS-DB2 threads to be created and terminated with great frequency as transactions start and complete. The DB2 address space that handles thread creation and termination is MSTR, the system services address space. If CICS address spaces are ahead of MSTR in the line for CPU cycles (i.e., if the CICS AORs have a higher priority than MSTR), and if the z/OS LPAR is really busy (think CPU utilization north of 90%), MSTR may not be readily dispatched when it has work to do -- and the work MSTR needs to do may be related to CICS-DB2 thread creation. If DB2 threads aren't made available to CICS transactions in a timely manner, the transactions will take longer to complete, and "wait for DB2" will be seen -- via a CICS monitor -- as the reason for the not-so-good performance. Check DB2 monitor data at such times and you'll likely see that things look fine. This is because DB2 doesn't "see" a CICS transaction until it gets a thread. Thus, as I pointed out in part 1 of this two-part entry, the "wait for DB2" time reported by the CICS monitor can be time spent in-between CICS and DB2, and that's why the different monitors paint different pictures of the same performance scene: the CICS monitor indicates that transactions are waiting longer for DB2, and that's true, but the DB2 monitor shows that when a transaction does get the thread it needs it performs just fine. If you see this kind of situation in your environment, check the dispatching priorities of the DB2 address spaces and of the CICS AORs. The priority of the DB2 MSTR, DBM1, and DDF address spaces should be a little higher than that of the CICS AORs in the LPAR. [Don't sweat giving DDF a relatively high dispatching priority. The vast majority of DDF CPU utilization is associated with the execution of SQL statements that come from network-attached clients, and these execute at the priority of the application processes that issue the SQL statements -- not at the priority of DDF.] Oh, and in addition to ensuring that DB2 MSTR can create threads quickly when they are needed for CICS transaction execution, think about taking some of the thread creation pressure off of MSTR by increasing the rate at which CICS-DB2 threads are reused -- I blogged on this topic a few months ago.
One other thing to check is the priority of the CICS transactions themselves -- more precisely, the priority of the TCBs used for CICS transaction execution. This is specified through the value given to PRIORITY in the definition of a CICS DB2ENTRY resource, or in the DB2CONN definition for transactions that utilize pool threads. The default value of the PRIORITY attribute is HIGH, and this means that the tasks associated with entry threads (or pool threads, as the case may be) will have a dispatching priority that is a little higher than that of the CICS AOR's main task. HIGH is an OK specification if the z/OS LPAR isn't too busy -- it helps to get transactions through the system quickly; however, if the z/OS LPAR is very busy, PRIORITY(HIGH) may lead to a throughput issue -- this because not only the DB2 address spaces, but the CICS AORs, as well, could end up waiting behind transactions to get dispatched. In that case, going with PRIORITY(LOW) could actually improve CICS-DB2 transaction throughput. I have seen this myself. Bear in mind that PRIORITY(LOW) doesn't mean batch low -- it means that the transactions will have a priority that is a little lower than that of the CICS region's main task.
Bottom line: dealing with (or better, preventing) CICS-DB2 performance problems is sometimes just a matter of getting your priorities in order.
The priority to which I'm referring is dispatching priority -- the kind that determines which of several "in and ready" tasks in a z/OS system will get CPU cycles first. Dispatching priorities for address spaces are specified in the WLM policy of the z/OS LPAR in which they run. The busier a system is (with respect to CPU utilization), the more important it is to get the address space dispatching priorities right. DB2's IRLM address space (the lock manager) should have a very high priority, and in fact should be assigned to the SYSSTC service class (for ultra-high-priority address spaces). Most sites get this right. The mistake that I've seen more than once is giving the DB2 system services and database services address spaces (aka MSTR and DBM1) a priority that is lower than that of the CICS application owning regions (AORs) in the z/OS LPAR. The thinking here is often something like this: "If the DB2 MSTR and DBM1 address spaces have a higher priority than the CICS AORs, they will get in the way of CICS transactions and performance will be the worse for it." Not so. The DB2 "system" address spaces generally consume little in the way of CPU time (DDF can be a different animal in this regard, as I pointed out in an entry posted to this blog last month), so "getting in the way" of CICS regions is not an issue. In fact, it's when CICS regions "get in the way" of the DB2 system address spaces (as they can in a busy system when they have a higher-than-DB2 priority), that CICS transaction performance can go downhill.
How's that? Well, it can come down to a thread-availability issue. It's common for CICS-DB2 threads to be created and terminated with great frequency as transactions start and complete. The DB2 address space that handles thread creation and termination is MSTR, the system services address space. If CICS address spaces are ahead of MSTR in the line for CPU cycles (i.e., if the CICS AORs have a higher priority than MSTR), and if the z/OS LPAR is really busy (think CPU utilization north of 90%), MSTR may not be readily dispatched when it has work to do -- and the work MSTR needs to do may be related to CICS-DB2 thread creation. If DB2 threads aren't made available to CICS transactions in a timely manner, the transactions will take longer to complete, and "wait for DB2" will be seen -- via a CICS monitor -- as the reason for the not-so-good performance. Check DB2 monitor data at such times and you'll likely see that things look fine. This is because DB2 doesn't "see" a CICS transaction until it gets a thread. Thus, as I pointed out in part 1 of this two-part entry, the "wait for DB2" time reported by the CICS monitor can be time spent in-between CICS and DB2, and that's why the different monitors paint different pictures of the same performance scene: the CICS monitor indicates that transactions are waiting longer for DB2, and that's true, but the DB2 monitor shows that when a transaction does get the thread it needs it performs just fine. If you see this kind of situation in your environment, check the dispatching priorities of the DB2 address spaces and of the CICS AORs. The priority of the DB2 MSTR, DBM1, and DDF address spaces should be a little higher than that of the CICS AORs in the LPAR. [Don't sweat giving DDF a relatively high dispatching priority. The vast majority of DDF CPU utilization is associated with the execution of SQL statements that come from network-attached clients, and these execute at the priority of the application processes that issue the SQL statements -- not at the priority of DDF.] Oh, and in addition to ensuring that DB2 MSTR can create threads quickly when they are needed for CICS transaction execution, think about taking some of the thread creation pressure off of MSTR by increasing the rate at which CICS-DB2 threads are reused -- I blogged on this topic a few months ago.
One other thing to check is the priority of the CICS transactions themselves -- more precisely, the priority of the TCBs used for CICS transaction execution. This is specified through the value given to PRIORITY in the definition of a CICS DB2ENTRY resource, or in the DB2CONN definition for transactions that utilize pool threads. The default value of the PRIORITY attribute is HIGH, and this means that the tasks associated with entry threads (or pool threads, as the case may be) will have a dispatching priority that is a little higher than that of the CICS AOR's main task. HIGH is an OK specification if the z/OS LPAR isn't too busy -- it helps to get transactions through the system quickly; however, if the z/OS LPAR is very busy, PRIORITY(HIGH) may lead to a throughput issue -- this because not only the DB2 address spaces, but the CICS AORs, as well, could end up waiting behind transactions to get dispatched. In that case, going with PRIORITY(LOW) could actually improve CICS-DB2 transaction throughput. I have seen this myself. Bear in mind that PRIORITY(LOW) doesn't mean batch low -- it means that the transactions will have a priority that is a little lower than that of the CICS region's main task.
Bottom line: dealing with (or better, preventing) CICS-DB2 performance problems is sometimes just a matter of getting your priorities in order.
Wednesday, August 8, 2012
When CICS and DB2 for z/OS Monitors Disagree (Part 1)
Today I'm writing about a situation that I first encountered almost 20 years ago, and which I most recently saw about three months ago: an organization with a CICS transaction workload accessing a DB2 for z/OS database reports a performance problem. The company's CICS people point to monitor data that indicate higher "wait for DB2" times when CICS transactions run long. Meanwhile, the folks on the DB2 team serve up monitor numbers of their own, showing that in-DB2 times for CICS transactions are not higher than normal during CICS slow-down events. What's to be made of this seeming contradiction? How can it be that for a given period, a CICS monitor shows unusually high "wait for DB2" times while DB2 monitor data for the same period show consistently good in-DB2 elapsed times for CICS transactions? I've found that this kind of "are too!" / "am not!" situation is usually the result of CICS transactions waiting longer than usual for DB2 threads. "Wait for thread" goes in the "wait for DB2" bucket from the CICS monitor perspective, but it doesn't inflate in-DB2 elapsed times because the DB2 monitor doesn't "see" a transaction until it has a thread. "Wait for thread" time is, in essence, a waiting that is "between" CICS and DB2, and that's why the CICS and DB2 monitors see it differently.
OK, so why would CICS transactions have to wait longer than usual for DB2 threads? In my experience, this happens for one of two reasons: not enough threads, or not enough priority. In the remainder of this blog entry I'll expand on the first of these two factors: not enough threads (in some cases, this is actually a matter of not having enough TCBs, as explained below). In a part 2 entry, I'll address the priority issue.
Back in the 1980s and 1990s, the connection between a CICS application-owning region (AOR) and a local DB2 for z/OS subsystem was defined by way of a macro called the RCT, or resource control table. RCT gave way to RDO (CICS resource definition online) towards the end of the 1990s. One of the CICS-DB2 connection values, specified in the definition of a resource called DB2CONN, is TCBLIMIT -- the maximum number of TCBs (task control blocks) that can be used to connect transactions running in the CICS region to a target DB2 subsystem. Another of the CICS-DB2 connection set-up parameters, THREADLIMIT, appears in a DB2CONN resource definition (indicating the maximum number of pool threads for CICS-DB2 transactions) and can also appear in a DB2ENTRY resource definition (indicating the maximum number of entry threads for transactions associated with the DB2ENTRY resource). The sum of all THREADLIMIT values (for pool threads and entry threads) for a given CICS region should be less than the value of TCBLIMIT for the region, and people generally start out that way; however, over time folks may increase THREADLIMIT values -- to accommodate a growing CICS-DB2 transaction workload -- without adjusting TCBLIMIT accordingly; thus, the sum of all THREADLIMIT values for a CICS region could end up being greater than the TCBLIMIT value, and that could result in a situation in which threads are available for transactions, but TCBs needed to use those threads to connect to DB2 are insufficient in number to avoid elongated wait times. You should check your system for this possibility, and, if the sum of THREADLIMIT values exceeds TCBLIMIT for a region, either adjust TCBLIMIT upwards or adjust THREADLIMIT values downwards. I'd generally lean towards a larger TCBLIMIT value in that case, but if there were a lot of protected entry threads defined (PROTECTNUM > 0 and THREADLIMIT > 0 for DB2ENTRY resources), I'd consider reducing PROTECTNUM and THREADLIMIT values for those DB2ENTRY resources.
Here's another scenario: TCBLIMIT is greater than the sum of all THREADLIMIT values for a CICS region, but wait-for-thread time is still relatively high. This can happen when the following are true:
In that case, it may be that lots of transactions are overflowing to the pool, but the number of pool threads is not large enough for the associated transaction volume. This situation could be indicated by a non-zero value in the W/P column for the POOL row in the output of a DSNC DISPLAY STATISTICS command for the region in question (this is an attachment command issued through a CICS-supplied transaction called DSNC). If you see such a value, bump up the THREADLIMIT number in the DB2CONN resource definition for the region (and in doing that, check, as previously mentioned, to see if a TCBLIMIT increase might be needed, as well).
Of course, you could also have elongated wait-for-thread times if a DB2ENTRY resource definition has a very small (but non-zero) THREADLIMIT value and a specification of THREADWAIT(YES). Before messing with that, check to see if this is an intentional aspect of the DB2ENTRY definition: it's conceivable that a certain transaction must be limited with respect to concurrent execution (maybe even single-threaded, via THREADLIMIT(1) and THREADWAIT(YES) for a DB2ENTRY resource) in order to prevent a contention problem.
Here's one more possibility (though it's not something that I've actually seen): you may have high CICS wait-for-thread times because the DB2 limit on the number of threads for local (i.e., not network-attached) programs is too small. The ZPARM parameter CTHREAD specifies this limit, and it's for all local threads: for CICS transactions, batch jobs, TSO users, etc. If you have an indication of high CICS wait-for-thread times, and you have a pretty small CTHREAD value, consider adjusting CTHREAD upwards. Note that CTHREAD can have a much larger value in a DB2 10 environment versus prior versions of DB2 for z/OS -- this because almost all thread-related virtual storage usage goes above the 2 GB "bar" when packages are bound (or rebound) in a DB2 10 system. Whereas the sum of CTHREAD and MAXDBAT formerly had to be less than 2000, in a DB2 10 environment that sum has to be less than 20,000.
You can help to avoid high CICS wait-for-thread times by ensuring that you have enough threads (and enough CICS TCBs) for your CICS-DB2 transactions. Check back in a week or so for part 2 of this two-part entry, in which I'll explain how high wait-for-thread times can be a matter of priorities (dispatching, that is).
OK, so why would CICS transactions have to wait longer than usual for DB2 threads? In my experience, this happens for one of two reasons: not enough threads, or not enough priority. In the remainder of this blog entry I'll expand on the first of these two factors: not enough threads (in some cases, this is actually a matter of not having enough TCBs, as explained below). In a part 2 entry, I'll address the priority issue.
Back in the 1980s and 1990s, the connection between a CICS application-owning region (AOR) and a local DB2 for z/OS subsystem was defined by way of a macro called the RCT, or resource control table. RCT gave way to RDO (CICS resource definition online) towards the end of the 1990s. One of the CICS-DB2 connection values, specified in the definition of a resource called DB2CONN, is TCBLIMIT -- the maximum number of TCBs (task control blocks) that can be used to connect transactions running in the CICS region to a target DB2 subsystem. Another of the CICS-DB2 connection set-up parameters, THREADLIMIT, appears in a DB2CONN resource definition (indicating the maximum number of pool threads for CICS-DB2 transactions) and can also appear in a DB2ENTRY resource definition (indicating the maximum number of entry threads for transactions associated with the DB2ENTRY resource). The sum of all THREADLIMIT values (for pool threads and entry threads) for a given CICS region should be less than the value of TCBLIMIT for the region, and people generally start out that way; however, over time folks may increase THREADLIMIT values -- to accommodate a growing CICS-DB2 transaction workload -- without adjusting TCBLIMIT accordingly; thus, the sum of all THREADLIMIT values for a CICS region could end up being greater than the TCBLIMIT value, and that could result in a situation in which threads are available for transactions, but TCBs needed to use those threads to connect to DB2 are insufficient in number to avoid elongated wait times. You should check your system for this possibility, and, if the sum of THREADLIMIT values exceeds TCBLIMIT for a region, either adjust TCBLIMIT upwards or adjust THREADLIMIT values downwards. I'd generally lean towards a larger TCBLIMIT value in that case, but if there were a lot of protected entry threads defined (PROTECTNUM > 0 and THREADLIMIT > 0 for DB2ENTRY resources), I'd consider reducing PROTECTNUM and THREADLIMIT values for those DB2ENTRY resources.
Here's another scenario: TCBLIMIT is greater than the sum of all THREADLIMIT values for a CICS region, but wait-for-thread time is still relatively high. This can happen when the following are true:
- THREADLIMIT is set to zero (or a very small number) for DB2ENTRY resources
- THREADWAIT(POOL) is specified for these same DB2ENTRY resources
- THREADLIMIT is a too-small number for pool threads (i.e., THREADLIMIT has a small value in the region's DB2CONN resource definition)
In that case, it may be that lots of transactions are overflowing to the pool, but the number of pool threads is not large enough for the associated transaction volume. This situation could be indicated by a non-zero value in the W/P column for the POOL row in the output of a DSNC DISPLAY STATISTICS command for the region in question (this is an attachment command issued through a CICS-supplied transaction called DSNC). If you see such a value, bump up the THREADLIMIT number in the DB2CONN resource definition for the region (and in doing that, check, as previously mentioned, to see if a TCBLIMIT increase might be needed, as well).
Of course, you could also have elongated wait-for-thread times if a DB2ENTRY resource definition has a very small (but non-zero) THREADLIMIT value and a specification of THREADWAIT(YES). Before messing with that, check to see if this is an intentional aspect of the DB2ENTRY definition: it's conceivable that a certain transaction must be limited with respect to concurrent execution (maybe even single-threaded, via THREADLIMIT(1) and THREADWAIT(YES) for a DB2ENTRY resource) in order to prevent a contention problem.
Here's one more possibility (though it's not something that I've actually seen): you may have high CICS wait-for-thread times because the DB2 limit on the number of threads for local (i.e., not network-attached) programs is too small. The ZPARM parameter CTHREAD specifies this limit, and it's for all local threads: for CICS transactions, batch jobs, TSO users, etc. If you have an indication of high CICS wait-for-thread times, and you have a pretty small CTHREAD value, consider adjusting CTHREAD upwards. Note that CTHREAD can have a much larger value in a DB2 10 environment versus prior versions of DB2 for z/OS -- this because almost all thread-related virtual storage usage goes above the 2 GB "bar" when packages are bound (or rebound) in a DB2 10 system. Whereas the sum of CTHREAD and MAXDBAT formerly had to be less than 2000, in a DB2 10 environment that sum has to be less than 20,000.
You can help to avoid high CICS wait-for-thread times by ensuring that you have enough threads (and enough CICS TCBs) for your CICS-DB2 transactions. Check back in a week or so for part 2 of this two-part entry, in which I'll explain how high wait-for-thread times can be a matter of priorities (dispatching, that is).
Sunday, July 29, 2012
DB2 10 (and 9) for z/OS: I Need 128 GB of WHAT?
The other day, I got a note from a mainframe DB2 DBA who'd seen a system requirement for DB2 10 that had him concerned. This DBA, whose company was preparing for the migration of their DB2 9 subsystems to DB2 10, had read the following in the text of IBM APAR II14564 (an "info" APAR that documents some DB2 9-to-10 migration and fallback considerations):
128 GB is a lot of space. That and the "for each subsystem" bit really jumped out at the DBA. As he pointed out in his note, "We have four subsystems on an LPAR and that would be half a terabyte."
I'll tell anyone who's concerned about this what I told the DBA who contacted me: RELAX. Most important point: this is VIRTUAL storage we're talking about, not real storage. Second most important point: this HVSHARE value (a parameter in the IEASYSxx member of PARMLIB) establishes the BOUNDARY of an area of z/OS virtual storage -- it demarcates the part of z/OS virtual storage that is available for use by subsystems in the form of something called shared private storage. How can something be both shared and private? I'll get to that in a moment.
The 128 GB of HVSHARE space required by DB2 is, more than likely, just a small portion of the total HVSHARE space defined on your system: the DEFAULT value of HVSHARE is 510 TERABYTES. How can any system support 510 TB of HVSHARE virtual storage? Easy: shared private storage does not have to be backed by real storage when it's allocated -- it only has to be backed by real storage as it's USED. The amount of HVSHARE space used by DB2 and other subsystems will probably be a small fraction of what's allocated on the z/OS LPAR. You might see a few hundred megabytes, or maybe a gigabyte or two, of shared private storage used by a production DB2 subsystem -- nothing remotely close to 128 GB (DB2 10 uses more shared private storage than does DB2 9, for reasons noted a little further on in this entry). If you have the current version of IBM's Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS monitor, you can see, in a statistics report or online display, the amount of shared private storage used by a DB2 subsystem. Are you concerned that the default of 510 TB of z/OS virtual storage available for use as shared private storage will leave little virtual storage space for other uses? No need to worry about that: 64-bit addressing enables access to 16 million terabytes of virtual storage. Setting aside 510 of those 16 million terabytes for shared private storage is not a big deal.
Now, how does DB2 use shared private memory? That actually depends on the release of DB2 to which you're referring. You see, DB2 9 was actually the first DB2 for z/OS release to exploit shared private memory (a technology delivered with z/OS 1.5 in the mid-2000s). Here's the story: a lot of data is transferred between certain DB2 address spaces (for example, between the DDF and DBM1 address spaces, and between utility address spaces and DBM1). If said data can be exchanged by way of an area of virtual storage that is part of both of the participating address spaces, the CPU cost of data transfer operations is reduced. An option here would be to use common storage, which is part of every address space. One problem with that approach is that making common storage bigger to accommodate large inter-address space exchanges of data reduces the private portion of virtual storage for all address spaces. Why make the private part of every address space smaller when the data exchanges we're talking about involve just a few DB2 address spaces? Shared private storage offered a better solution: a section of virtual storage in which shared memory objects could be created -- these being areas of virtual storage that can be shared by address spaces, but only by address spaces that register to use a particular shared memory object (that's what makes the virtual storage both shared and private). Beginning with DB2 9, the DDF, DBM1, and MSTR address spaces (and, as mentioned, address spaces in which some DB2 utilities execute) are registered to use the DB2 shared memory object, which is created at DB2 start-up time in the portion of virtual storage reserved for this purpose by way of the HVSHARE specification.
So, if DB2 9 is the release that first exploited shared private storage, how is it that DB2 10 has more people talking about this z/OS resource? The answer, in a word, is threads. One of the big benefits provided by DB2 10 for z/OS is constraint relief as it pertains to virtual storage below the 2 GB "bar." This big free-up of space was achieved by moving almost all thread- and stack-related storage above the bar ("stack" storage is essentially working storage). Most of this thread and stack storage is of the shared private variety; so, the amount of shared private storage usage in a DB2 10 environment will depend largely on the number of threads that are concurrently active in the system. I'll point out here that you need to rebind packages in a DB2 10 environment to get this thread-related virtual storage constraint relief. When you do that, space used for package tables (aka PTs -- the sections of packages copied out of the skeleton package table for each package executed by a particular thread) is moved not only above the bar, but out of the EDM pool (the skeleton package table remains in the EDM pool). Thus is delivered the added benefit of eliminating contention caused by the serialization of updates to EDM pool control blocks used in the execution of packages allocated to threads (this contention in previous DB2 releases was associated with latch class 24).
There you have it. Yes, DB2 10 requires (as does DB2 9) 128 GB of HVSHARE space, so that the shared memory object used by DB2 address spaces can be created when DB2 starts up. No, you don't need to get worked up about this, because 1) the amount of shared private storage used by DB2 is going to be WAY less than 128 GB, and 2) a z/OS LPAR, by default, will have 510 terabytes of HVSHARE space. There are plenty of things in life you can worry about. This shouldn't be one of them.
REAL STORAGE CONSIDERATION
Ensure that you have defined a high enough value for HVSHARE... to satisfy all requests on this LPAR. DB2 Version 10 requires 128GB of contiguous 64-bit shared private storage above the 2GB bar for each DB2 subsystem... If not enough contiguous storage for... HVSHARE exists, the DB2 [subsystem] will abend with S0DC2 RC8A001721 at the first starting.
Ensure that you have defined a high enough value for HVSHARE... to satisfy all requests on this LPAR. DB2 Version 10 requires 128GB of contiguous 64-bit shared private storage above the 2GB bar for each DB2 subsystem... If not enough contiguous storage for... HVSHARE exists, the DB2 [subsystem] will abend with S0DC2 RC8A001721 at the first starting.
128 GB is a lot of space. That and the "for each subsystem" bit really jumped out at the DBA. As he pointed out in his note, "We have four subsystems on an LPAR and that would be half a terabyte."
I'll tell anyone who's concerned about this what I told the DBA who contacted me: RELAX. Most important point: this is VIRTUAL storage we're talking about, not real storage. Second most important point: this HVSHARE value (a parameter in the IEASYSxx member of PARMLIB) establishes the BOUNDARY of an area of z/OS virtual storage -- it demarcates the part of z/OS virtual storage that is available for use by subsystems in the form of something called shared private storage. How can something be both shared and private? I'll get to that in a moment.
The 128 GB of HVSHARE space required by DB2 is, more than likely, just a small portion of the total HVSHARE space defined on your system: the DEFAULT value of HVSHARE is 510 TERABYTES. How can any system support 510 TB of HVSHARE virtual storage? Easy: shared private storage does not have to be backed by real storage when it's allocated -- it only has to be backed by real storage as it's USED. The amount of HVSHARE space used by DB2 and other subsystems will probably be a small fraction of what's allocated on the z/OS LPAR. You might see a few hundred megabytes, or maybe a gigabyte or two, of shared private storage used by a production DB2 subsystem -- nothing remotely close to 128 GB (DB2 10 uses more shared private storage than does DB2 9, for reasons noted a little further on in this entry). If you have the current version of IBM's Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS monitor, you can see, in a statistics report or online display, the amount of shared private storage used by a DB2 subsystem. Are you concerned that the default of 510 TB of z/OS virtual storage available for use as shared private storage will leave little virtual storage space for other uses? No need to worry about that: 64-bit addressing enables access to 16 million terabytes of virtual storage. Setting aside 510 of those 16 million terabytes for shared private storage is not a big deal.
Now, how does DB2 use shared private memory? That actually depends on the release of DB2 to which you're referring. You see, DB2 9 was actually the first DB2 for z/OS release to exploit shared private memory (a technology delivered with z/OS 1.5 in the mid-2000s). Here's the story: a lot of data is transferred between certain DB2 address spaces (for example, between the DDF and DBM1 address spaces, and between utility address spaces and DBM1). If said data can be exchanged by way of an area of virtual storage that is part of both of the participating address spaces, the CPU cost of data transfer operations is reduced. An option here would be to use common storage, which is part of every address space. One problem with that approach is that making common storage bigger to accommodate large inter-address space exchanges of data reduces the private portion of virtual storage for all address spaces. Why make the private part of every address space smaller when the data exchanges we're talking about involve just a few DB2 address spaces? Shared private storage offered a better solution: a section of virtual storage in which shared memory objects could be created -- these being areas of virtual storage that can be shared by address spaces, but only by address spaces that register to use a particular shared memory object (that's what makes the virtual storage both shared and private). Beginning with DB2 9, the DDF, DBM1, and MSTR address spaces (and, as mentioned, address spaces in which some DB2 utilities execute) are registered to use the DB2 shared memory object, which is created at DB2 start-up time in the portion of virtual storage reserved for this purpose by way of the HVSHARE specification.
So, if DB2 9 is the release that first exploited shared private storage, how is it that DB2 10 has more people talking about this z/OS resource? The answer, in a word, is threads. One of the big benefits provided by DB2 10 for z/OS is constraint relief as it pertains to virtual storage below the 2 GB "bar." This big free-up of space was achieved by moving almost all thread- and stack-related storage above the bar ("stack" storage is essentially working storage). Most of this thread and stack storage is of the shared private variety; so, the amount of shared private storage usage in a DB2 10 environment will depend largely on the number of threads that are concurrently active in the system. I'll point out here that you need to rebind packages in a DB2 10 environment to get this thread-related virtual storage constraint relief. When you do that, space used for package tables (aka PTs -- the sections of packages copied out of the skeleton package table for each package executed by a particular thread) is moved not only above the bar, but out of the EDM pool (the skeleton package table remains in the EDM pool). Thus is delivered the added benefit of eliminating contention caused by the serialization of updates to EDM pool control blocks used in the execution of packages allocated to threads (this contention in previous DB2 releases was associated with latch class 24).
There you have it. Yes, DB2 10 requires (as does DB2 9) 128 GB of HVSHARE space, so that the shared memory object used by DB2 address spaces can be created when DB2 starts up. No, you don't need to get worked up about this, because 1) the amount of shared private storage used by DB2 is going to be WAY less than 128 GB, and 2) a z/OS LPAR, by default, will have 510 terabytes of HVSHARE space. There are plenty of things in life you can worry about. This shouldn't be one of them.
Thursday, July 12, 2012
DB2 for z/OS: What's With DDF CPU Time?
Recently I got a question from a mainframer who was seeing rather high CPU times for the DB2 DDF address space. DDF, of course, is the distributed data facility -- the DB2 component that handles communications with client systems (usually application servers) that send requests to the DB2 for z/OS server using the DRDA protocol (DRDA being Distributed Relational Database Architecture). Anyway, the questioner wanted to know what he and his colleagues could do to reduce DDF CPU consumption.
This query prompted me to write a blog entry about DDF CPU utilization, because there remains today -- even 20 years after DDF was introduced with DB2 V2.2 -- a good bit of misunderstanding concerning this subject. I believe that the confusion comes from people looking at the DDF address space as they do the other DB2 "system" address spaces (the database services address space, also known as DBM1; the system services address space, or MSTR; and the internal resource lock manager, or IRLM). Generally speaking, these address spaces show very little in the way of CPU consumption (this is particularly true of the IRLM and MSTR address spaces). Here is some information from a real-world mainframe DB2 environment, as seen in a DB2 monitor statistics detail report covering a busy two-hour time period:
TOTAL TIME
------------
SYSTEM SERVICES 2:16.529524
DATABASE SERVICES 55:38.969257
IRLM 21.249774
So, on this system (a big one, with multiple engines, running a large DB2 workload), the IRLM address space consumed 21 seconds of CPU time and the DB2 system services address space consumed just over 2 minutes and 16 seconds of CPU time. The DB2 database services address space consumed considerably more CPU time than these other two (a little more than 55 minutes' worth), but that's not unusual when there's a lot of prefetch and database write I/O activity on a system (true in this case), as the CPU time for such I/O operations is charged to DBM1. That the CPU consumption of these address spaces is relatively low on a system with a great deal of DB2 data access activity is not at all unusual: overall CPU consumption for a DB2 workload is associated primarily with SQL statement execution, and the vast majority of that time is charged to the address spaces (e.g., CICS regions and batch initiators) through which data access requests get to DB2.
Now, during the same two-hour period on the same system on which the numbers shown above were seen, CPU consumption of the DB2 DDF address space was as follows:
TOTAL TIME
--------------
DDF ADDRESS SPACE 3:16:34.642514
"Three hours and sixteen minutes?!? What's with that? I thought that the DB2 address spaces are supposed to show smaller numbers for CPU consumption! Is there a problem in my system? What should I do?!?"
First of all, you should relax. You can't equate DDF CPU consumption with that of the other DB2 "system" address spaces, because it really is a different animal. IRLM, MSTR, and DBM1 are about data access. DDF is about transaction management (specifically, client-server transactions) -- and that understanding should point you to the reason for sometimes-high DDF CPU utilization: it's driven by execution of SQL statements that get to DB2 through the DDF address space (typically, SQL statements issued from programs running in network-attached application servers, or from DB2 stored procedures called by such programs). Recall that I mentioned, a couple of paragraphs up, that the vast majority of the CPU time associated with SQL statement execution is charged to the "come from" address space. If the statement comes from a CICS transaction program, that time is charged to the CICS region in which the program executed. If the statement comes from a DRDA requester, the CPU time is going to be charged to the DDF address space (and stored procedure usage doesn't change this picture: CPU time consumed by a stored procedure is charged to the address space through which the stored procedure CALL got to DB2). So, in a system in which there is a lot of client-server DB2 activity, you'll see higher numbers for DDF CPU consumption (at plenty of sites, DDF-related activity is the fastest-growing component of the overall DB2 for z/OS workload, and at more and more locations it is already the largest component of the DB2 workload).
Back to the DB2 monitor statistics detail report referenced earlier: the breakdown of address space CPU time tells the story with respect to DDF CPU consumption. Here's the additional information from the DDF address space line that I didn't show before (I'm showing times down to the millisecond level instead of the microsecond level to save space):
TCB TIME PREEMPT SRB NONPREEMPT SRB TOTAL TIME
-------- ----------- -------------- -----------
DDF 1:02.659 3:13:21.699 2:10.283 3:16:34.642
You can see that almost all of the DDF address space CPU time (about 98%) is in the "preemptible SRB" category. This is "user" CPU time, associated with the execution of SQL statements issued by DRDA requesters (work tied to database access threads -- aka DBATs -- is represented by preemptible SRBs, which you can think of as "dispatchable" SRBs). The other 2% of the DDF CPU time is "system" time, related to work done under DDF's own tasks on behalf of the aforementioned "user" tasks. So, DDF is in fact a very CPU-efficient manager of DB2-accessing client-server transactions.
Now, what would you do to reduce the CPU consumption of a DDF address space? You'd take actions to reduce the execution cost of SQL statements that get to DB2 through DDF. Those actions might include the following:
Now you know what's behind the DDF address space CPU times that you're seeing on your system, and what you can do to reduce DDF address space CPU consumption. I hope that this information will be useful to you.
This query prompted me to write a blog entry about DDF CPU utilization, because there remains today -- even 20 years after DDF was introduced with DB2 V2.2 -- a good bit of misunderstanding concerning this subject. I believe that the confusion comes from people looking at the DDF address space as they do the other DB2 "system" address spaces (the database services address space, also known as DBM1; the system services address space, or MSTR; and the internal resource lock manager, or IRLM). Generally speaking, these address spaces show very little in the way of CPU consumption (this is particularly true of the IRLM and MSTR address spaces). Here is some information from a real-world mainframe DB2 environment, as seen in a DB2 monitor statistics detail report covering a busy two-hour time period:
TOTAL TIME
------------
SYSTEM SERVICES 2:16.529524
DATABASE SERVICES 55:38.969257
IRLM 21.249774
So, on this system (a big one, with multiple engines, running a large DB2 workload), the IRLM address space consumed 21 seconds of CPU time and the DB2 system services address space consumed just over 2 minutes and 16 seconds of CPU time. The DB2 database services address space consumed considerably more CPU time than these other two (a little more than 55 minutes' worth), but that's not unusual when there's a lot of prefetch and database write I/O activity on a system (true in this case), as the CPU time for such I/O operations is charged to DBM1. That the CPU consumption of these address spaces is relatively low on a system with a great deal of DB2 data access activity is not at all unusual: overall CPU consumption for a DB2 workload is associated primarily with SQL statement execution, and the vast majority of that time is charged to the address spaces (e.g., CICS regions and batch initiators) through which data access requests get to DB2.
Now, during the same two-hour period on the same system on which the numbers shown above were seen, CPU consumption of the DB2 DDF address space was as follows:
TOTAL TIME
--------------
DDF ADDRESS SPACE 3:16:34.642514
"Three hours and sixteen minutes?!? What's with that? I thought that the DB2 address spaces are supposed to show smaller numbers for CPU consumption! Is there a problem in my system? What should I do?!?"
First of all, you should relax. You can't equate DDF CPU consumption with that of the other DB2 "system" address spaces, because it really is a different animal. IRLM, MSTR, and DBM1 are about data access. DDF is about transaction management (specifically, client-server transactions) -- and that understanding should point you to the reason for sometimes-high DDF CPU utilization: it's driven by execution of SQL statements that get to DB2 through the DDF address space (typically, SQL statements issued from programs running in network-attached application servers, or from DB2 stored procedures called by such programs). Recall that I mentioned, a couple of paragraphs up, that the vast majority of the CPU time associated with SQL statement execution is charged to the "come from" address space. If the statement comes from a CICS transaction program, that time is charged to the CICS region in which the program executed. If the statement comes from a DRDA requester, the CPU time is going to be charged to the DDF address space (and stored procedure usage doesn't change this picture: CPU time consumed by a stored procedure is charged to the address space through which the stored procedure CALL got to DB2). So, in a system in which there is a lot of client-server DB2 activity, you'll see higher numbers for DDF CPU consumption (at plenty of sites, DDF-related activity is the fastest-growing component of the overall DB2 for z/OS workload, and at more and more locations it is already the largest component of the DB2 workload).
Back to the DB2 monitor statistics detail report referenced earlier: the breakdown of address space CPU time tells the story with respect to DDF CPU consumption. Here's the additional information from the DDF address space line that I didn't show before (I'm showing times down to the millisecond level instead of the microsecond level to save space):
TCB TIME PREEMPT SRB NONPREEMPT SRB TOTAL TIME
-------- ----------- -------------- -----------
DDF 1:02.659 3:13:21.699 2:10.283 3:16:34.642
You can see that almost all of the DDF address space CPU time (about 98%) is in the "preemptible SRB" category. This is "user" CPU time, associated with the execution of SQL statements issued by DRDA requesters (work tied to database access threads -- aka DBATs -- is represented by preemptible SRBs, which you can think of as "dispatchable" SRBs). The other 2% of the DDF CPU time is "system" time, related to work done under DDF's own tasks on behalf of the aforementioned "user" tasks. So, DDF is in fact a very CPU-efficient manager of DB2-accessing client-server transactions.
Now, what would you do to reduce the CPU consumption of a DDF address space? You'd take actions to reduce the execution cost of SQL statements that get to DB2 through DDF. Those actions might include the following:
- Convert external stored procedures to native SQL procedures. This is actually more about reducing the monetary cost of CPU cycles consumed by a DB2 client-server workload, versus reducing the cycles consumed. When a native SQL procedure (introduced with DB2 9 in new-function mode, and available in a DB2 10 new-function mode environment if you're migrating to DB2 10 from DB2 V8) is executed through DDF, around 60% of the associated CPU time will be zIIP eligible (the same is not true for external stored procedures). This zIIP offload will reduce the cost of your DB2 client-server workload, because zIIP MIPS are less expensive than general-purpose CPU MIPS.
- Take greater advantage of DB2 dynamic statement caching. Often, a lot of dynamic SQL statements are executed through DDF. If you are getting a low hit ratio in your dynamic statement cache (check this via your DB2 monitor), consider making the dynamic statement cache larger (done via a ZPARM change). This assumes that you have enough real storage on your system to back a larger dynamic statement cache.
- Go to DB2 10 (if you're not already there) and take advantage of high-performance DBATs. This is done by binding packages executed via database access threads with RELEASE(DEALLOCATE). I blogged on this topic last year.
- Improve SQL statement CPU efficiency by tuning your DB2 buffer pool configuration. This, of course, would beneficially affect all SQL statements -- not just those executed via DDF. I have "part 1" and "part 2" blog entries on this subject.
- Turn dynamic SQL statements executed via DDF into static SQL statements. We have a nice tool, IBM Optim pureQuery Runtime, that can help you to do this without having to change client-side program code. pureQuery Runtime can also be used to "parameterize" dynamic SQL statements that were coded with references to literal values in predicates -- something that will boost the effectiveness of the DB2 dynamic statement cache. I recently wrote about pureQuery Runtime on my DB2 for z/OS tools blog.
- Tune your higher-cost DDF-related SQL statements. We have a tool that can help you to do this in a proactive, productive way. It's called the IBM InfoSphere Optim Query Workload Tuner. You can find out more about this product in an entry I posted to my DB2 for z/OS tools blog just last week.
Now you know what's behind the DDF address space CPU times that you're seeing on your system, and what you can do to reduce DDF address space CPU consumption. I hope that this information will be useful to you.
Monday, June 25, 2012
Why is IBM System z Essential to Your Business?
Earlier this year, some of my colleagues and I who work with IBM information management software for the System z platform were invited to write essays addressing the question, "Why is IBM System z essential to business?" I decided to act on this invitation, and to get my creative juices flowing I imagined an impromptu conversation with a skeptical CEO on the business value of System z. A co-worker requested that I post my essay on my DB2 blog, so I'm doing that now. You'll find it below. I hope that it will provide you with some ideas that you can use in discussions that you might have at your site about the value of System z as an enterprise data-serving platform.
On a recent business trip, I got an upgrade to first class on the flight home. The person seated next to me turned out to be the CEO of a retail company. When I told her that I work for IBM, she said, “Ah, yes. Big Blue. Big Iron.” And then, “When are you folks going to get out of the mainframe business and into the modern world?”
Smiling, I replied, “Since that’s a two-part question, I’ll give you a two-part answer. First, I suppose we’ll get out of the mainframe business when our customers give up on the platform, and our customers are doing nothing of the sort. Second, organizations that utilize mainframes, which in the context of our product line are called IBM System z servers, know that it’s not a choice between using mainframes and moving into the ‘modern world,’ as you put it, because System z technology is always moving forward – has been for almost fifty years. A server line doesn’t stick around that long if it’s standing still.”
The CEO smiled back at me, warming to the debate. “OK, so mainframes, or (air quotes) ‘System z servers,’ can do what they’ve done for a long time, and they can do it faster than before. But doing the same thing faster doesn’t mean they’re doing the right thing. Mainframes run legacy applications just fine, but newer applications have to be more flexible and adaptable – my CIO talks a lot about ‘agile’ applications. Frankly, I think of mainframes as rigid and ponderous – not the light-on-their-feet systems that we need in today’s world.”
Back to me. “I hear what you’re saying. In addition to ‘agile,’ I imagine that your CIO uses terms such as ‘service-oriented,’ ‘multi-tiered,’ and ‘loosely coupled’ in describing your newer application systems.”
“Those words sound familiar,” said the CEO.
“Your CIO is thinking the right way,” I said, “but there’s no reason that mainframes can’t be part of his thinking. Organizations all over the world are developing applications with all of the characteristics cited by your CIO, and these applications are running on mainframes, or they’re running on other platforms – and accessing data stored on mainframes. Either way, System z servers are the foundation on which these very modern applications are built.”
The CEO shook her head. “Even if building those kinds of applications on a mainframe is technically possible, it’s an expensive proposition based on what I know.”
“Look,” I said, “as the captain of a big ship, you understand that the tip of an iceberg is not the big concern – the whole of the iceberg is what matters. Similarly, people who say that mainframes are expensive tend to focus only on a part of the cost-of-computing iceberg – namely, the part that is the initial cost to acquire the server hardware and software. Why don’t you have your CIO take you through your data center to see all the other costs? Get him to show you where the mainframes are and compare that to where the non-mainframe servers are. How much floor space do those other servers take up? How much electricity do they consume? How much heat do they pump out, adding to your data center cooling costs? After you’ve surveyed your computer server scene, get the CIO to go over his org chart with you. How many people support the System z servers? How many support the non-mainframe servers? I think you’ll find that your System z servers handle a big workload at a total cost of computing that compares very favorably to that of your non-mainframe servers.”
I could see that the CEO’s gears were turning. “You’ve got me curious. I think I’ll take that data center tour. But I think that you’re leaving out an important cost factor: the people who develop our applications. The programmers we hire out of college know languages like Java. Mainframe programs are written mostly in COBOL, right? The only people who know that language these days are folks who’ve been programming since the 1970s, and a lot of them are pretty expensive.”
“It’s true,” I said, “that many programs running on mainframes are written in COBOL – largely because COBOL programs that were written years ago still run great on mainframes. They still keep the trucks rolling, the shelves stocked, and the customer invoices flowing. But application developers today – including the twenty-somethings – are accessing mainframe databases using Java and even Python and other languages with amusing names such as Ruby, C#, Perl, and PHP. These are often modern web-interfacing applications with slick user interfaces.”
“I don’t know,” said the CEO. “I hear that ‘open systems’ are the way to go. That leaves mainframes out, right?”
“Not as I see it. A computing platform can be ‘closed’ – in the sense that one company owns the design and development of the associated hardware and operating system – and still be an excellent fit in a heterogeneous application infrastructure. The key is to have open interfaces to the platform. When a Java programmer uses something called Java database connectivity, or JDBC – an industry-standard interface – to access records in a relational database, his work isn’t made any more challenging because he’s targeting a database on a System z server.”
“Well if the mainframe database looks to a programmer like databases on other servers, what’s the advantage of having the data on a mainframe?” asked the CEO.
“Good question. The mainframe database system likely looks different versus other data-serving platforms because it’s always up, never gets hacked, and never gets in the way of your company’s growth.”
Now the CEO cracks a pretty good smile. “Nice pitch. I’m going to follow up on it when I get back to the office.”
“You should. Remember: our job is to help you win in the marketplace. Some of your most effective weapons have “IBM” and “System z” stamped on them. Use them for all they’re worth, and kick some butt.”
“Will do.” Then, turning to the crossword puzzle in the in-flight magazine: “Do you know a three-letter word for ‘essential company’?”
On a recent business trip, I got an upgrade to first class on the flight home. The person seated next to me turned out to be the CEO of a retail company. When I told her that I work for IBM, she said, “Ah, yes. Big Blue. Big Iron.” And then, “When are you folks going to get out of the mainframe business and into the modern world?”
Smiling, I replied, “Since that’s a two-part question, I’ll give you a two-part answer. First, I suppose we’ll get out of the mainframe business when our customers give up on the platform, and our customers are doing nothing of the sort. Second, organizations that utilize mainframes, which in the context of our product line are called IBM System z servers, know that it’s not a choice between using mainframes and moving into the ‘modern world,’ as you put it, because System z technology is always moving forward – has been for almost fifty years. A server line doesn’t stick around that long if it’s standing still.”
The CEO smiled back at me, warming to the debate. “OK, so mainframes, or (air quotes) ‘System z servers,’ can do what they’ve done for a long time, and they can do it faster than before. But doing the same thing faster doesn’t mean they’re doing the right thing. Mainframes run legacy applications just fine, but newer applications have to be more flexible and adaptable – my CIO talks a lot about ‘agile’ applications. Frankly, I think of mainframes as rigid and ponderous – not the light-on-their-feet systems that we need in today’s world.”
Back to me. “I hear what you’re saying. In addition to ‘agile,’ I imagine that your CIO uses terms such as ‘service-oriented,’ ‘multi-tiered,’ and ‘loosely coupled’ in describing your newer application systems.”
“Those words sound familiar,” said the CEO.
“Your CIO is thinking the right way,” I said, “but there’s no reason that mainframes can’t be part of his thinking. Organizations all over the world are developing applications with all of the characteristics cited by your CIO, and these applications are running on mainframes, or they’re running on other platforms – and accessing data stored on mainframes. Either way, System z servers are the foundation on which these very modern applications are built.”
The CEO shook her head. “Even if building those kinds of applications on a mainframe is technically possible, it’s an expensive proposition based on what I know.”
“Look,” I said, “as the captain of a big ship, you understand that the tip of an iceberg is not the big concern – the whole of the iceberg is what matters. Similarly, people who say that mainframes are expensive tend to focus only on a part of the cost-of-computing iceberg – namely, the part that is the initial cost to acquire the server hardware and software. Why don’t you have your CIO take you through your data center to see all the other costs? Get him to show you where the mainframes are and compare that to where the non-mainframe servers are. How much floor space do those other servers take up? How much electricity do they consume? How much heat do they pump out, adding to your data center cooling costs? After you’ve surveyed your computer server scene, get the CIO to go over his org chart with you. How many people support the System z servers? How many support the non-mainframe servers? I think you’ll find that your System z servers handle a big workload at a total cost of computing that compares very favorably to that of your non-mainframe servers.”
I could see that the CEO’s gears were turning. “You’ve got me curious. I think I’ll take that data center tour. But I think that you’re leaving out an important cost factor: the people who develop our applications. The programmers we hire out of college know languages like Java. Mainframe programs are written mostly in COBOL, right? The only people who know that language these days are folks who’ve been programming since the 1970s, and a lot of them are pretty expensive.”
“It’s true,” I said, “that many programs running on mainframes are written in COBOL – largely because COBOL programs that were written years ago still run great on mainframes. They still keep the trucks rolling, the shelves stocked, and the customer invoices flowing. But application developers today – including the twenty-somethings – are accessing mainframe databases using Java and even Python and other languages with amusing names such as Ruby, C#, Perl, and PHP. These are often modern web-interfacing applications with slick user interfaces.”
“I don’t know,” said the CEO. “I hear that ‘open systems’ are the way to go. That leaves mainframes out, right?”
“Not as I see it. A computing platform can be ‘closed’ – in the sense that one company owns the design and development of the associated hardware and operating system – and still be an excellent fit in a heterogeneous application infrastructure. The key is to have open interfaces to the platform. When a Java programmer uses something called Java database connectivity, or JDBC – an industry-standard interface – to access records in a relational database, his work isn’t made any more challenging because he’s targeting a database on a System z server.”
“Well if the mainframe database looks to a programmer like databases on other servers, what’s the advantage of having the data on a mainframe?” asked the CEO.
“Good question. The mainframe database system likely looks different versus other data-serving platforms because it’s always up, never gets hacked, and never gets in the way of your company’s growth.”
Now the CEO cracks a pretty good smile. “Nice pitch. I’m going to follow up on it when I get back to the office.”
“You should. Remember: our job is to help you win in the marketplace. Some of your most effective weapons have “IBM” and “System z” stamped on them. Use them for all they’re worth, and kick some butt.”
“Will do.” Then, turning to the crossword puzzle in the in-flight magazine: “Do you know a three-letter word for ‘essential company’?”
Tuesday, June 5, 2012
The ONE Key Aspect of DB2 for z/OS Table-Controlled Partitioning that People Seem Most Likely to Forget
Sometimes, an important DB2 for z/OS feature can be out there for years, and yet not be fully appreciated by lots of people in the DB2 user community. Table-controlled partitioning is such a feature. In this blog entry I want to generally set the record straight about table-controlled partitioning, and in particular I want to make sure that mainframe DB2 folks understand a key aspect of this physical database design option that for some reason eludes many users to this day.
First, the basics: table-controlled partitioning refers to a means of range-partitioning the data in a table, so that, for example, sales records for retail store numbers 1-25 go into table space partition 1, records for store numbers 26-50 go into partition 2, records for store numbers 51-75 go into partition 3, and so on. Table-controlled partitioning is so called to distinguish it from index-controlled partitioning, which for years and years was the only way to assign a table's rows to table space partitions based on the value of a user-specified partitioning key: you created a partitioning index for a table, and in the definition of that index you associated table space partitions with ranges of the partitioning key.
That changed with DB2 Version 8 for z/OS, and this brings me to another point of clarification: some people get table-controlled partitioning and universal table spaces a little mixed up, in that they believe that table-controlled partitioning was introduced along with universal table spaces. If you've been under that impression, you're off by one DB2 release. DB2 V8 delivered table-controlled partitioning. DB2 9 delivered universal table spaces. There are two types of universal table space, and one of these, called a partition-by-range universal table space, is conceptually very similar to a DB2 V8 table-controlled partitioned table space (the other type of universal table space is known as partition-by-growth). Those who lump DB2 V8-introduced table-controlled partitioning in with DB2 9-introduced universal table spaces may be under the impression that you cannot convert an index-controlled partitioned table space to a table-controlled partitioned table space until you're running with DB2 10, because that's the release in which a smooth path to universal table spaces from other table space types was provided. In fact, in a DB2 V8 or DB2 9 (or DB2 10) environment you can easily convert an index-controlled partitioned table space to a table-controlled partitioned table space. How is that done? It's as simple as this: for the partitioning index of the index-controlled partitioned table space, issue an ALTER INDEX with a NOT CLUSTER. Bingo -- you've now got a table-controlled partitioned table space.
This simple means of converting an index-controlled partitioned table space to a table-controlled partitioned table space highlights the really key feature of table-controlled partitioning that -- as I mentioned up front -- has escaped the notice of many a DB2 person: data in a table-controlled partitioned table space can be partitioned by one key and clustered within partitions by another key. For a number of mainframe DB2-using organizations, the fact that the partitioning index of an index-controlled partitioned table space had to also be the table's clustering index was a pain. I worked in the early 2000s in the IT department of a financial services company, and I well recall that we had, in our DB2 for z/OS V7 database, a partitioned table space that presented us with a Hobson's choice: we could go with a key that would be good for partitioning (benefiting the performance of high-volume insert operations) but lousy for clustering (hurting query result set retrieval performance), or vice versa. When DB2 for z/OS V8 was announced, and with it the ability to specify data partitioning at the table level (versus the index level) and -- as a consequence -- to specify a clustering index with no dependence on the partitioning scheme, we jumped on that like a cat on a mouse. In my mind, this feature ranked right up there with 64-bit addressing as the MOST valuable capability provided via DB2 V8.
Obviously, not everyone in the DB2 user community shared this assessment of mine, as evidenced by the fact that I still find people who are under the impression that the partitioning key for a table-controlled partitioned table space has to be the table's clustering key. Again, NOT SO. Suppose you have a table for which it is important, for data load and offload purposes, that rows be partitioned by date (so, for example, one week's data goes into partition 1, the next week's into partition 2, and so on). For that same table, good query performance may depend on rows within the date-based partitions being clustered by account number. FINE. NO PROBLEM. Define the table with a specification of the date column as the partitioning key, and create an index, with the CLUSTER option specified, on the account number column. What if this table exists today in index-controlled partitioned form, with the partitioning/clustering index defined on the data column (good for load/offload, not good for result set retrieval)? In that case, you could first issue, as previously mentioned, an ALTER INDEX statement, with the NOT CLUSTER option, targeting the existing partitioning/clustering index on the table. Because the partitioning index on an index-controlled partitioned table space must also be the clustering index on the table, execution of this ALTER INDEX statement will have the effect of changing the table space's partitioning scheme from index-controlled to table-controlled (it will still be partitioned by the date column, but that partitioning information will now be tied to the table definition instead of being tied to an index's definition). Next, if you already have an index defined on the account number column, issue an ALTER INDEX statement, with the CLUSTER option specified, for that index (if an index on the account number column doesn't exist already, create one and include CLUSTER in the CREATE INDEX statement).
Here's another potential benefit you can get by going from index-controlled to table-controlled partitioning for a table space: you might be able to get rid of an index that is useful ONLY for partitioning purposes (i.e., it provides no query access path performance benefit, isn't defined on a foreign key, etc.). For the purpose of illustrating this point, consider an index-controlled partitioned table space that is partitioned on a column, called PARTITION_KEY, that is populated with a value by the program that inserts rows into the table. Not only will users never search on this column, it's probably a stinko clustering key, to boot. Issue the handy-dandy ALTER INDEX statement with the NOT CLUSTER option for this index, and alter some other index on the table to make it the clustering index. After that (and after checking that the index on the PARTITION_KEY column, now no longer needed for partitioning, is not needed for any other reason), drop the index on PARTITION_KEY. You'll save the disk space that had been occupied by the index, and insert and delete operations targeting the table will be more CPU-efficient (due to your having one less index on the table that would have to be maintained by DB2). Remember, the table space is still partitioned by the PARTITION_KEY column -- it's just that you no longer need an index to effect that partitioning scheme.
There you go. Nice capability, eh? Could you take advantage of it? Have you taken advantage of it? I hope so. Thanks for reading.
First, the basics: table-controlled partitioning refers to a means of range-partitioning the data in a table, so that, for example, sales records for retail store numbers 1-25 go into table space partition 1, records for store numbers 26-50 go into partition 2, records for store numbers 51-75 go into partition 3, and so on. Table-controlled partitioning is so called to distinguish it from index-controlled partitioning, which for years and years was the only way to assign a table's rows to table space partitions based on the value of a user-specified partitioning key: you created a partitioning index for a table, and in the definition of that index you associated table space partitions with ranges of the partitioning key.
That changed with DB2 Version 8 for z/OS, and this brings me to another point of clarification: some people get table-controlled partitioning and universal table spaces a little mixed up, in that they believe that table-controlled partitioning was introduced along with universal table spaces. If you've been under that impression, you're off by one DB2 release. DB2 V8 delivered table-controlled partitioning. DB2 9 delivered universal table spaces. There are two types of universal table space, and one of these, called a partition-by-range universal table space, is conceptually very similar to a DB2 V8 table-controlled partitioned table space (the other type of universal table space is known as partition-by-growth). Those who lump DB2 V8-introduced table-controlled partitioning in with DB2 9-introduced universal table spaces may be under the impression that you cannot convert an index-controlled partitioned table space to a table-controlled partitioned table space until you're running with DB2 10, because that's the release in which a smooth path to universal table spaces from other table space types was provided. In fact, in a DB2 V8 or DB2 9 (or DB2 10) environment you can easily convert an index-controlled partitioned table space to a table-controlled partitioned table space. How is that done? It's as simple as this: for the partitioning index of the index-controlled partitioned table space, issue an ALTER INDEX with a NOT CLUSTER. Bingo -- you've now got a table-controlled partitioned table space.
This simple means of converting an index-controlled partitioned table space to a table-controlled partitioned table space highlights the really key feature of table-controlled partitioning that -- as I mentioned up front -- has escaped the notice of many a DB2 person: data in a table-controlled partitioned table space can be partitioned by one key and clustered within partitions by another key. For a number of mainframe DB2-using organizations, the fact that the partitioning index of an index-controlled partitioned table space had to also be the table's clustering index was a pain. I worked in the early 2000s in the IT department of a financial services company, and I well recall that we had, in our DB2 for z/OS V7 database, a partitioned table space that presented us with a Hobson's choice: we could go with a key that would be good for partitioning (benefiting the performance of high-volume insert operations) but lousy for clustering (hurting query result set retrieval performance), or vice versa. When DB2 for z/OS V8 was announced, and with it the ability to specify data partitioning at the table level (versus the index level) and -- as a consequence -- to specify a clustering index with no dependence on the partitioning scheme, we jumped on that like a cat on a mouse. In my mind, this feature ranked right up there with 64-bit addressing as the MOST valuable capability provided via DB2 V8.
Obviously, not everyone in the DB2 user community shared this assessment of mine, as evidenced by the fact that I still find people who are under the impression that the partitioning key for a table-controlled partitioned table space has to be the table's clustering key. Again, NOT SO. Suppose you have a table for which it is important, for data load and offload purposes, that rows be partitioned by date (so, for example, one week's data goes into partition 1, the next week's into partition 2, and so on). For that same table, good query performance may depend on rows within the date-based partitions being clustered by account number. FINE. NO PROBLEM. Define the table with a specification of the date column as the partitioning key, and create an index, with the CLUSTER option specified, on the account number column. What if this table exists today in index-controlled partitioned form, with the partitioning/clustering index defined on the data column (good for load/offload, not good for result set retrieval)? In that case, you could first issue, as previously mentioned, an ALTER INDEX statement, with the NOT CLUSTER option, targeting the existing partitioning/clustering index on the table. Because the partitioning index on an index-controlled partitioned table space must also be the clustering index on the table, execution of this ALTER INDEX statement will have the effect of changing the table space's partitioning scheme from index-controlled to table-controlled (it will still be partitioned by the date column, but that partitioning information will now be tied to the table definition instead of being tied to an index's definition). Next, if you already have an index defined on the account number column, issue an ALTER INDEX statement, with the CLUSTER option specified, for that index (if an index on the account number column doesn't exist already, create one and include CLUSTER in the CREATE INDEX statement).
Here's another potential benefit you can get by going from index-controlled to table-controlled partitioning for a table space: you might be able to get rid of an index that is useful ONLY for partitioning purposes (i.e., it provides no query access path performance benefit, isn't defined on a foreign key, etc.). For the purpose of illustrating this point, consider an index-controlled partitioned table space that is partitioned on a column, called PARTITION_KEY, that is populated with a value by the program that inserts rows into the table. Not only will users never search on this column, it's probably a stinko clustering key, to boot. Issue the handy-dandy ALTER INDEX statement with the NOT CLUSTER option for this index, and alter some other index on the table to make it the clustering index. After that (and after checking that the index on the PARTITION_KEY column, now no longer needed for partitioning, is not needed for any other reason), drop the index on PARTITION_KEY. You'll save the disk space that had been occupied by the index, and insert and delete operations targeting the table will be more CPU-efficient (due to your having one less index on the table that would have to be maintained by DB2). Remember, the table space is still partitioned by the PARTITION_KEY column -- it's just that you no longer need an index to effect that partitioning scheme.
There you go. Nice capability, eh? Could you take advantage of it? Have you taken advantage of it? I hope so. Thanks for reading.
Subscribe to:
Posts (Atom)