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):

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.

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.

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:

• 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.

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’?”

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. 

If You've Got DB2 for z/OS Product Requirements, This is your Online Community

Who makes DB2 for z/OS a great database management system? Well, the IBM DB2 developers, of course. But who else? You, that's who. You, the users of DB2 for z/OS, have provided IBM with a steady stream of enhancement requests since the product was introduced in 1983. There have been various means by which DB2 requirements have been communicated by users to IBM, including an application called FITS. FITS served its purpose pretty well, but one aspect of the application came to be seen as a drawback in light of the growing popularity and importance of social media: FITS is an IBM-internal application. A product enhancement request might be raised by a DB2 user, but it had to be entered and monitored on the user's behalf by an IBMer. What was needed was a new application that would provide a direct link between DB2 users and IBM DB2 developers. That new application is here, and it's called RFE (short for Request for Enhancements).

With RFE, you can enter DB2 for z/OS product requirements yourself, and interact directly with IBMers who evaluate and act on submitted requests. You can track the status of requests you've entered, and you can view -- and vote on, and comment on -- requests submitted by others. RFE is going to be a win-win for the DB2 for z/OS user community and for IBM, spurring increased flow of ideas on how to make DB2 even better than it already is, and contributing to the strengthening of the DB2 user community, across industries and around the world.

I encourage you to learn more about RFE. Here are some links that can get you started:

• The home page for the RFE community for DB2 for z/OS. What you see at the top of this page is "Information Management." What makes it the RFE home page for DB2 for z/OS is "DB2 for z/OS" in the box near the top of the page, under the heading, "Customize this page for your favorite product."
• RFE FAQs. You'll find here a long list of questions and answers. Scroll through them if you'd like, or use the links at the top of the page to go directly to FAQs pertaining to a specific area of interest. I recommend checking out the information on RFE groups, a very useful feature of the application. You can create an invitation-only private group for you and your co-workers, or a public group to attract "birds of a feather" DB2 users who share an interest in a particular category of product enhancements.
• RFE video tutorials. Watch and learn.
• RFE status definitions. Entries on this page expand on the meaning of request status values such as "Under consideration" and "Need more information."
• Getting an IBM ID (required for the use of RFE). Lots of you already have an IBM ID. It's free, and if you don't have one you can get one by clicking on the "Need an IBM ID?" link on this page.

The information available on the above-listed Web pages is pretty extensive, but in case you need more help in getting started with RFE, you can send a note to my colleague Janet Figone, at jfigone@us.ibm.com.

I've been on the IBM and the user side of the DB2 community. When those sides come together to make DB2 for z/OS a better product, great things can happen. Use RFE, and become a part of that.

Migrating from DB2 for z/OS V8? DO THIS

A lot of organizations have already migrated from DB2 for z/OS Version 8 to a more-current release of DB2 -- either DB2 9 or (in the case of skip-level migrations) DB2 10. For the most part, those migrations from DB2 V8 went quite smoothly; however, some companies making the move from DB2 V8 to DB2 9 or DB2 10 have encountered performance problems related to one thing: the need in DB2 9 and DB2 10 environments for more resources -- disk space, table spaces, buffer pool space -- associated with work file table spaces that have a 32KB page size. A lot of my colleagues and I have been beating this drum for some time, reminding people of the jump in 32KB-page sorting (versus 4KB-page sorting) that is to be expected when going from DB2 V8 to DB2 9 or DB2 10, but the message has apparently not gotten through to everyone, as evidenced by the fact that organizations to this day get caught with too little in the way of 32KB-page sort resources when migrating from DB2 V8 to DB2 9 or DB2 10. So, for those of you who are in the process of migrating from DB2 V8 to a more-current DB2 release, or who are just now completing that process, or who have been on DB2 9 or DB2 10 for a while and have been living with sub-optimal performance caused by inadequate 32KB-page sort resources and perhaps have been unaware of this connection, I'm going to lay out the relevant facts and recommendations in this blog entry. Get this one thing right, and your transition from DB2 V8 will be a smoother one.

Fact: starting with Version 9, DB2 directs more -- MUCH more -- of its SQL sort work to work file table spaces that have 32KB (versus 4KB) pages. By "SQL sort work," I'm referring to sorts that are related to SQL statements (e.g., sorts associated with ORDER BY, GROUP BY, and DISTINCT specifications, and with merge scan join operations), as opposed to sorts associated with IBM DB2 utility execution (these are handled by DFSORT, or by DB2 Sort when that product is installed). In a DB2 9 or DB2 10 environment, if records to be sorted have a length of 100 bytes or more, a work file table space with 32KB pages will be used for the sort operation; otherwise, a 4KB-page table space will be used. This change was implemented for a reason: sorting of longer records is more efficient when a larger work file page size is used.

Fact: starting with DB2 9, work file table spaces are segmented, not simple, and the SEGSIZE (which can't be altered in a DB2 9 CM or DB2 10 CM8 environment) is 16. This can add to the demand for 32KB-page sort space in a DB2 9 or DB2 10 environment. Think about it: suppose that a 200 rows with a length of 200 bytes apiece are to be sorted to satisfy an ORDER BY specification. A couple of 32KB pages should suffice, right? Wrong. With a work file table space SEGSIZE of 16, DB2 is going to use sixteen 32KB pages for this sort. Now, if you're running DB2 9 NFM or DB2 10 CM9 or NFM, don't go rushing to make the SEGSIZE of your work file table spaces something smaller than 16 -- not without reading further. I'll get to this.

Recommendation: have a lot of 32KB-page work file space in your DB2 9 or DB2 10 system. What's "a lot," you ask? That's going to vary from site to site, depending on the usage of the DB2 subsystem (a business intelligence workload might be more sort-intensive than an operational transaction or batch workload) and the volume of sort-driving SQL statement execution. Here's what I tell people: if, in your DB2 V8 environment, you had X amount of 4KB-page work file space, have at least X amount of 32KB-page sort work space in your DB2 9 or DB2 10 system, and consider having 2X amount of 32KB-page sort work space, to be on the safer side. You will likely be able to reduce -- perhaps considerably -- your 4KB-page sort work space once you're running with DB2 9 or DB2 10. At some sites where DB2 9 or DB2 10 is in use, the ratio of 32KB-page work file space to 4KB-page space is 3:1 or 4:1.

Recommendation: have a pretty good number of 32KB-page and 4KB-page work file table spaces with a secondary space allocation quantity of zero. You're probably aware that, starting with DB2 9 for z/OS, the TEMP database is no more. Things that had used TEMP DB space in DB2 V8 (declared global temporary tables and the temporary tables that hold result sets of static scrollable cursors) use space in the work file database in a DB2 9 or DB2 10 environment. It's a good idea to physically separate the two categories of temporary space usage -- SQL-related sorts, view materialization, triggers, etc. on the one hand, and declared global temporary tables and static scrollable cursors on the other -- within the DB2 9 or DB2 10 work file database. This can be accomplished by having some work file table spaces that are DB2-managed with a non-zero secondary space allocation quantity, and some that are either DB2-managed with a secondary space allocation quantity of zero, or user-managed. Depending on the value of the ZPARM parameter WFDBSEP (built into the DB2 10 base code, and provided a couple of years ago for DB2 9 via the fix for APAR PM02528), DB2 will favor (WFDBSEP=NO) or require (WFDBSEP=YES) the use of work file table spaces that are DB2-managed with a non-zero SECQTY value for declared global temporary tables and static scrollable cursors, and will favor or require the use of work file table spaces that are DB2-managed with SECQTY 0 -- or user-managed -- for SQL-related sorts, view materialization, etc. Note that while a user-managed work file table space will be favored for SQL sorts regardless of the secondary space allocation quantity, if you have user-managed work file table spaces these should have a secondary space allocation quantity of zero -- this is better for SQL sort performance.

Obviously, having work file table spaces with a secondary space allocation quantity of zero will mean that these table spaces will not grow beyond "piece" one. That being the case, you should have a fair number of these, of both the 4KB-page and 32KB-page variety. There's not a one-size-fits-all quantity here, but you might want to start with 10-15 32KB-page work file table spaces (and a similar number of 4KB-page table spaces) with a secondary space allocation quantity of zero. How large should the primary space allocation quantity be for these table spaces? That might depend on the availability of disk space at your site. A primary space allocation quantity of just under 2 GB (as recommended in "info" APAR II14587) is good if your disk storage resources will accommodate that specification; otherwise, go for 400-500 cylinders (around 280-350 MB of DB2-usable space). Keep in mind that -- as previously mentioned -- you will likely be able to adjust the amount of 4KB-page work file space downward once you're on DB2 9 or DB2 10 long enough to be past the fallback-possibility stage of migration.

By the way, DB2 9 introduced a REXX exec, DSNTWFG, that can be used to create DB2-managed work file table spaces. The parameters that you supply for this REXX exec are explained in the text of APAR PM17336, the fix for which allowed DSNTWFG to be used for the creation of DB2-managed work file table spaces with SECQTY 0 (previously the REXX exec could only be used to create DB2-managed work file table spaces with a non-zero SECQTY).

Recommendation: start with the default value of NO for the WFDBSEP parameter of ZPARM. This setting establishes a "soft" physical separation of work file space use for SQL sorts (and view materialization and other things) and declared global temporary tables (and static scrollable cursors). With that set-up in place, if space in the work file table spaces that have a secondary quantity of zero is inadequate for the processing of SQL sorts, DB2 can use space in DB2-managed table spaces with a non-zero SECQTY for this purpose. This flexibility serves to provide a "safety valve" (or expansion reservoir, depending on your preferred analogy) for SQL sort work in the subsystem. If you have WFDBSEP=NO and 1) you see your non-zero SECQTY  work file table spaces going into multiple "pieces" (A001, A002, A003, etc. data sets) and 2) you don't have a lot of declared global temporary table usage that would drive this expansion into multiple pieces, chances are the expansion of these table spaces into multiple pieces is being driven by SQL sort "spillover" activity that is occurring because space for that purpose in SECQTY 0 work file table spaces is under-allocated. In that case, consider increasing the amount of space in SECQTY 0 table spaces (of the 4KB-page or 32KB-page variety, as needed). If you don't see evidence of SQL sort "spillover" (or if you have heavy declared global temporary table usage and you want to keep that from spilling over into work file table spaces used for SQL sorts) then you can consider -- carefully -- going with WFDBSEP=YES (understanding that with this specification you'll get negative SQL codes and/or error messages if the space needed for declared temporary tables or SQL sorts is not available in work file table spaces with non-zero SECQTY and SECQTY 0, respectively).

Recommendation: dedicate 4K and 32K buffer pools to your 4KB-page and 32KB-page work file table spaces, and make them relatively large. "Large" is going to vary from one site to another, but given an adequate central storage resource I'd go with at least 10,000 buffers for the buffer pool used for 4KB-page work file table spaces (20,000-30,000 buffers would be better still) and at least 5000 buffers for the pool used for 32KB-page work file table spaces (10,000 buffers would be nice for this pool, if you have the storage). Check out a blog entry I posted a few weeks ago for other recommendations concerning buffer pools dedicated to work file table spaces.

Recommendation: consider -- carefully -- changing the SEGSIZE for your work file table spaces, once you are in DB2 9 NFM or (or DB2 10 NFM if you're doing a skip-level migration from DB2 V8). As I mentioned previously, the default SEGSIZE for these table spaces in a DB2 9 or DB2 10 environment is 16. IF your environment is characterized by a large number of sorts of relatively small result sets (e.g., result sets that could fit into a few 4KB or 32KB pages), a SEGSIZE of 8 (or maybe even 4) could relieve pressure on your work file database, as DB2 would not need to use considerably more pages than needed to hold small sets of rows to be sorted (and keep in mind here that SQL sorts are not just for things like ORDER BY specifications in SELECT statements -- they are also needed for operations such as merge scan joins, and these can involve sorts of many records, depending on the filtering accomplished via application of predicates to outer or inner table rows). On the other hand, IF you are seeing a lot of suspend time due to latch contention on space map pages of work file table spaces (and DB2 traces can be used to check on this), a SEGSIZE of 32 (or even 64) could provide relief. If neither of these situations matches your environment, stay with SEGSIZE 16.

Wrap-up: I hope that the information in this entry will be useful to you. Going from DB2 V8 to DB2 9 or DB2 10 will provide your organization with lots of great new functionality. Along the way, don't stub your toe on the work file database. Understand what's changed in this area, and make the needed adjustments to your configuration.

DB2 for z/OS: Trading Memory for MIPS (Part 3)

In part 1 of this three-part series on leveraging Big Memory (i.e., a large z/OS LPAR real storage resource) to enhance DB2 workload CPU efficiency, I focused on getting the most out of a DB2 buffer pool configuration without increasing its size (this by adjusting certain threshold settings and redistributing buffers between low-I/O and high-I/O pools). In part 2, I offered guidance on being bold -- but not reckless -- in asking for more memory for a DB2 subsystem's buffer pool configuration. In this, the concluding entry of the series, I'll look at memory-for-MIPS moves that involve DB2 pools other than the buffer pools -- starting with the EDM pool.

Actually, that "starting with the EDM pool" bit is an "it depends" thing, depending on the version of DB2 for z/OS you're using. What I want to talk about here is improving CPU efficiency via the use of the RELEASE(DEALLOCATE) parameter of the BIND (and REBIND) PACKAGE command. RELEASE(DEALLOCATE), when used for an SQL-issuing program that is executed via a thread that persists through commits, saves CPU cycles by causing DB2 to retain certain items associated with package execution, such as table space locks and thread copies of package sections, until thread deallocation, as opposed to releasing these resources at each commit (and very likely having to re-acquire them as the application process continues to execute). Threads that persist through commits include CICS-DB2 protected entry threads, threads used by batch jobs, and -- starting with DB2 10 -- high-performance database access threads used for DRDA clients (I blogged about high-performance DBATs last year). The less SQL work an application process performs between commits, the greater the proportional CPU cost of at-commit resource release and re-acquisition that occurs when RELEASE(COMMIT) is in effect. It is for such application processes -- high volume, relatively simple transactions, and batch jobs that issue lots of commits -- that RELEASE(DEALLOCATE) can deliver significant CPU savings (sometimes on the order of 10%). There are operational considerations associated with RELEASE(DEALLOCATE) and persistent threads. These include utility execution scheduling (a utility might not be able to execute if an application process holds a lock -- even a non-exclusive, intent-type lock -- on the target table space, and with RELEASE(DEALLOCATE) that table space -level lock will be held until thread deallocation) and package bind and DDL activity scheduling (same reason -- you could end up having to wait for deallocation of persistent threads used for the execution of packages bound with RELEASE(DEALLOCATE)). Keep these considerations in mind. It's likely that you can deal with them -- lots of folks have (and the PKGREL option of the -MODIFY DDF command can help in this regard when you use high-performance DBATs).

Another operational consideration associated with the use (or increased use) of the RELEASE(DEALLOCATE) bind option has to do with increased virtual and real storage utilization, and that's where the "it depends" at the start of the preceding paragraph comes in. There's no "it depends" with respect to increased memory utilization -- that WILL happen when you use RELEASE(DEALLOCATE) with persistent threads. The dependency is on WHERE that increased memory utilization happens in the DB2 database services address space (aka DBM1). See, one of the thread-allocated resources that DB2 will retain until thread deallocation when RELEASE(DEALLOCATE) is in effect for a package is the copies of package sections associated with the SQL statements issued by the program. Here's how that works: when a program issues an SQL statement, the section of the associated package containing the bound statement (if it's static) will be copied from the "skeleton" part of the EDM pool to another part of DBM1 (if it's a dynamic SQL statement, the relevant package could be, for example, one of the IBM Data Server Driver packages used for dynamic SQL statements issued by network-attached client-server applications via JDBC calls). WHERE in DBM1 will that thread copy of the package be stored? THAT'S the version-dependent part of this equation. Prior to DB2 10, the thread's copy of a package section (or a plan section, in the case of a DBRM bound directly into a plan -- something that is not supported in a DB2 10 environment) was stored in a part of the EDM pool. With DB2 10, the package section allocated to a thread is copied to an agent local pool that is exclusive to the thread in question (and there's some plan-related thread storage, too, even in a DB2 10 system -- it's mostly package storage, but not all). This DB2 10 change is a good thing, in that it eliminates latching that occurred when EDM pool space was used for copies of packages allocated to threads. It also moved this storage above the 2 GB bar for packages bound in a DB2 10 system, and THAT gives you a WHOLE lot more virtual storage head room for using RELEASE(DEALLOCATE) to reduce the CPU cost of DB2-accessing programs. [In a DB2 Version 8 system, none of this virtual storage used for thread copies of package and plan sections went above the 2 GB bar, and with DB2 9 only some of it went above the 2 GB bar.]

The bottom line, then, is that RELEASE(DEALLOCATE) can do you more good, in terms of boosting CPU efficiency, in a DB2 10 environment because you almost certainly will be able to use this bind option more extensively than before. Now, this doesn't mean that you can or should bind ALL of your packages with RELEASE(DEALLOCATE) in a DB2 10 system, because real storage resources, though potentially extensive, or nonetheless finite. If your DB2 monitor supports DB2 10, it will report to you (in a statistics detail report or an online display) the amount of storage -- above and below the 2 GB bar -- used for plans and packages (these are copies associated with threads -- storage used for skeleton package and cursor table purposes is still in the EDM pool, and is reported as such). Keep an eye on that storage utilization, and of course keep an eye on the demand paging rate for the z/OS LPAR in which the DB2 subsystem is running: if that's in the single digits or less per second, on average, during busy periods of the processing day, real storage is not being pressured to an undesirable extent. In a DB2 9 or Version 8 system, keep an eye on the EDM pool storage used for PT and CT, and consider enlarging that part of the pool (or cutting back on your use of RELEASE(DEALLOCATE)) if the number of free pages there is indicated to be less than 10% of the total number of pages in that part of the pool (and that's actually two parts in a DB2 9 environment -- one above the 2 GB bar and one below).

The other EDM-pool related memory-for-MIPS play has to do with dynamic statement caching. With respect to the global (versus the local) statement cache, this has been above-the-bar storage since DB2 V8 (the global statement cache, allocated when the ZPARM parameter CACHEDYN is set to YES, is very commonly used; local statement caching, associated with the KEEPDYAMIC(YES) package bind option, is less commonly used). Your DB2 monitor will show you the hit ratio for the dynamic statement cache. The larger this section of the EDM pool, the greater the hit ratio is likely to be, and the larger that hit ratio is the more you save through avoidance of full prepares of dynamic SQL statements. With this said, there's probably a point of diminishing returns that you could hit in your system with regard to enlarging the dynamic statement cache (accomplished by updating the EDMSTMTC parameter in ZPARM). If the hit ratio for the dynamic statement cache is north of 90% in your environment, making the cache larger may not move the needle much further to the right.

Turning now the RID pool, used by DB2 for RID list processing (done for things like index ANDing and ORing, hybrid join, and list prefetch): what you want is for as many of these operations as practically possible to complete in memory, and a larger RID pool will help in accomplishing that objective. Your DB2 monitor will show you the number of times that RID processing operations were terminated because of a lack of storage for such processing, and a RID pool enlargement can be accomplished by increasing the value of the MAXRBLK parameter in ZPARM (the RID pool has been allocated in above-the-bar virtual storage since DB2 V8). DB2 10 delivered a couple of important changes related to RID list processing: first, the default size of the RID pool in a DB2 10 system is 400 MB, versus 8 MB with DB2 9 and DB2 V8 (this is one of a number of reasons why you can expect DB2 real storage utilization to increase when you migrate to DB2 10). Important change number 2 concerns what happens when RID pool space is insufficient for the completion of a RID list processing operation. Prior to DB2 10, such a storage-related RID list processing failure would cause DB2 to abandon RID list processing for the SQL statement in question and go instead with a table space scan for the object being accessed. With DB2 10, insufficient RID pool space for a RID list processing operation causes DB2 to keep on trucking with the operation, using 32K work file space for the RID list. That's likely to be a performance-benefiting approach versus abandoning RID list processing for a table space scan, and even though processing a RID list using work file space is not quite as CPU-efficient as getting it all done in the RID pool, the overhead of overflow is mitigated if you have a large buffer pool dedicated to your 32K work file table spaces (fewer I/Os means less CPU consumption). Note that the new DB2 10 ZPARM parameter MAXTEMPS_RID can be used to limit the amount of space in the work file database that DB2 can use for a single RID list processing operation.

Lastly, a word about the DB2 sort pool. This is space in memory (above the bar since DB2 V8) that DB2 uses for SQL-related (versus utility-related) sort operations. The larger the sort pool, the more CPU-efficient DB2 SQL-related sorts tend to be. If you're considering a sort pool enlargement, keep a couple of things in mind: first, this pool (sized via the SRTPOOL parameter in ZPARM), is the maximum size of the sort work area that DB2 will allocate for each concurrent sort user (so a sort pool size of X MB could result in DB2 using 20X MB of virtual storage for sort pool space if there are 20 concurrent sort operations in progress). Second, the default size of the sort pool went from 2 MB in DB2 V8 and DB2 9 systems to 10 MB with DB2 10 (again, one of multiple reasons for DB2 10's greater use of memory versus DB2 9 and DB2 V8). If you want to increase the size of the sort pool on your system (the upper limit is 128 MB, but the largest value I've seen on a DB2 system is 30 MB), keep that per concurrent sort item in mind, and as always keep your eye (perhaps with the help of a z/OS systems programmer) on the z/OS LPAR's demand paging rate. I'm fine with a rate in the low single digits per second or less. A demand paging rate in the higher single digits per second is yellow-light territory in my book, and a rate in excess of 10 per second would tell me that the z/OS LPAR's memory resource may be over-taxed, and I might take steps to reduce DB2's use of that resource (via buffer pool, EDM pool, RID pool, or sort pool adjustments, or some combination of these).

Modern mainframe systems tend to have a lot of real storage, and the size of that resource for the average z/OS LPAR keeps getting larger, based on what I'm seeing. I'd like for you to use System z memory to make your DB2 workload more CPU-efficient, and I hope that the information in this three-part blog entry will help you to do that.