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’?”
Monday, June 25, 2012
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.
Thursday, May 31, 2012
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 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.
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.
Monday, May 14, 2012
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.
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.
Friday, April 27, 2012
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.
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.
Thursday, April 12, 2012
DB2 for z/OS: Trading Memory for MIPS (Part 2)
In part one of this three-part entry on leveraging System z memory to reduce CPU consumption for a DB2-accessing application workload, I wrote about getting your DB2 buffer pool configuration house in order by doing things to improve CPU efficiency without increasing the total amount of virtual storage allocated for page caching (e.g., adjusting certain thresholds for work file-dedicated pools, making appropriate use of buffer page-fixing, choosing the right buffer-steal algorithm for a pool, and judiciously shifting buffers from low-I/O to high-I/O pools). After taking these actions you might find that you still have high read I/O rates for some of your buffer pools. That's when you'd take the next step (if feasible, and I'll get to that momentarily) and increase the size of your buffer pool configuration to drive down disk read activity and associated CPU consumption. In a nutshell, that is the subject of this part two post. Within the next couple of weeks I'll post my part three entry, in which I'll look at memory-for-MIPS actions that involve DB2 virtual storage pools other than the buffer pools (e.g., the EDM pool, the RID pool, and the sort pool).
Before going further, I'd like to address a topic that's come up a number of times in conversations I've had with DB2 for z/OS DBAs and systems programmers -- that being the difference between GETPAGE requests and DB2 read I/O requests. Plenty of people have the idea that a bigger buffer pool means fewer GETPAGEs. In fact, that is generally NOT the case. Driving down GETPAGEs is a worthwhile objective, since GETPAGE requests -- more than anything else, in my experience -- are the key determinant of the CPU cost of SQL statement execution; however, growing your buffer pool configuration is not the way to reduce GETPAGE activity. Instead, you'd attack that objective through means such as physical database design changes (adjusting table clustering, adding or modifying indexes, etc.) and SQL statement coding (e.g., changing a non-indexable predicate to an indexable predicate). Enlarging your buffer pool configuration is about reducing I/O requests (primarily read requests, which tend to greatly outnumber write I/O requests) and the CPU cycles consumed in driving those requests. Is there a connection between GETPAGE requests and read I/O activity for a buffer pool? Of course there is: when DB2 needs to examine a page from a table space or an index -- usually on behalf of an application program -- it will issue a GETPAGE request for that page. If the page in question is NOT already in the buffer pool to which the table space or index is assigned, the GETPAGE request will result in DB2 initiating a read I/O operation to bring the page into memory (unless the page is to be read into memory via an already-underway prefetch I/O, in which case the requesting program will wait for the completion said prefetch I/O). A bigger buffer pool, then, doesn't mean fewer GETPAGEs, as DB2 will still need to look at the same number of table and/or index pages in executing the same SQL statement (all other things being equal). What a bigger buffer pool DOES mean is that fewer of the GETPAGE requests will result in read I/O operations, since the likelihood of a requested page already being in memory will tend to increase as a buffer pool's size increases.
There is an exception to this rule: if a buffer pool is way too small relative to the number of pages belonging to objects assigned to the pool and the volume of requests for those pages, DB2 could end up hitting the data manager threshold for the pool. That threshold, also known by the acronym DMTH, is reached when 95% of a pool's buffers are non-stealable (either currently in use or updated and not yet externalized to disk or to a coupling facility in a DB2 data sharing system). When that happens, DB2 will issue a GETPAGE for every row retrieved from a page, so that, for a page from which 20 rows are retrieved for a program, 20 GETPAGE requests will be issued, versus one GETPAGE had DMTH not been hit for the pool. Obviously, a big spike in GETPAGE activity will cause a spike in CPU consumption, so keep an eye on DMTH and make sure that you're not hitting that threshold for any of your buffer pools (your DB2 monitor will provide this information, as will the output of the DB2 command -DISPLAY BUFFERPOOL(xxx) DETAIL, where "xxx" could be the name of an individual pool, such as BP4, or the word ACTIVE if you want to see information for all active buffer pools). If DMTH is being hit for a pool, it's likely that the pool is significantly undersized.
On now to making a buffer pool configuration larger. While there's no need to apologize for that (I'll expand on this point in a moment), you want to do it for a reason, and the reason, as just pointed out, is to reduce disk read I/O activity (which in turn reduces CPU consumption). So, your first order of business is to check on the rate of read I/O activity for each of your buffer pools. As pointed out near the end of my part one memory-for-MIPS post, what you're looking for is the TOTAL rate of read I/O requests (synchronous and asynchronous) per second for your buffer pools. Also as pointed out in the part one post, you can get that figure from a DB2 monitor statistics detail report or online display of buffer pool activity, or from the output of the DB2 command -DISPLAY BUFFERPOOL DETAIL (and I describe in the part one post how to use -DISPLAY BUFFERPOOL DETAIL output to get the desired information). If the total read I/O rate for a pool is less than 100 per second, making that pool larger won't hurt anything (assuming the presence of sufficient real storage to back that enlargement, about which I'll have something to say in a bit), but it likely won't move the CPU consumption needle much (though you might want a zero-read-I/O situation associated with "pinning" a DB2 object in memory -- something made more straightforward and effective in a DB2 10 environment via the new PGSTEAL(NONE) buffer pool option, described in the part one post). What should draw your attention are any pools for which the read I/O rate is in the hundreds -- or even thousands -- per second (the highest I've seen with my own eyes at a DB2 for z/OS site is 9000 read I/Os per second for a pool). Taking a big chunk out of these numbers through buffer pool enlargement can result in a significant improvement in CPU efficiency for your DB2 workload.
Now, taking a "big chunk" out of a high rate of disk read I/Os is not likely to happen via itty-bitty steps, by which I mean that you probably won't see much of an impact from adding 1000 buffers to an 80,000-buffer pool. Big-step buffer pool enlargements will often involve increasing a pool's size by 25% or even 50% (a pool that's currently quite small -- say, 10,000 4K buffers in a system with a lot of real storage -- might be doubled in size -- or more -- in a single step, depending on the particular circumstances of the situation). I tend to think big, in terms of buffer pool enlargement, because I so often see buffer pools with high read I/O rates that are way smaller than they could be, given the abundance of memory available on the associated z/OS LPAR. Here's the deal: since 64-bit addressing was introduced in the System z environment some years ago, and the cost of memory was reduced for new mainframe models, it's become more and more common for production z/OS LPARs to be configured with 20 or 40 GB or more of real storage (and sometimes you see a z/OS LPAR with north of 100 GB of real storage -- a z196 server can be configured with up to 3 TB of memory); yet, in these big lakes of memory you find production DB2 subsystems with a total aggregate buffer pool size (meaning, the sum of the sizes of each allocated pool) of a measly 1 GB -- or even less. HELLO! IS ANYONE UNDER THE IMPRESSION THAT YOU GET BONUS POINTS FOR NOT USING A HARDWARE RESOURCE -- MAINFRAME MEMORY -- FOR WHICH YOUR ORGANIZATION PAID MONEY? Come on, folks. That stuff is there to enable your system to perform better, and no subsystem will use more memory to better effect than DB2.
That brings us to the "don't apologize" part. Some DB2 for z/OS people -- especially those who've worked with DB2 for 15 or 20 years or more -- seem to have some kind of a guilt complex over their subsystem's use of mainframe memory resources. "Golly, I'm using a GIGABYTE just for my buffer pools. The other subsystems on the production LPAR don't use that much memory. I should be satisfied with what I have." WRONG. Other z/OS subsystems that handle things like transaction management and security and networking don't NEED the memory that a relational database management system needs, and -- more importantly -- they don't benefit from getting lots more memory the way DB2 does. DB2 for z/OS was architected, from the get-go, to perform better with a large real storage resource. Give DB2 more memory, and -- unless your system is storage-constrained, and I'll get to that -- throughput goes up and CPU consumption (most likely) goes down (though if more memory for DB2 lets you get a lot more work done in a unit of time, CPU utilization could increase, but that's usually OK if it means that, for example, a critical batch run completes in much less time than before). Some DB2 sites -- and more as time goes on -- have figured this out. They've leveraged big mainframe memory resources to greatly increase DB2 buffer pool sizes, with very positive effects on application performance. The biggest buffer pool configuration I've seen for a single DB2 subsystem is approximately 42 GB. Are the people at that shop crazy? Of course not. They have a z/OS system with a large real storage resource (way bigger than 42 GB), and they are taking advantage of that to deliver excellent performance for a high-volume, mission-critical application that accesses a very large database. They key is to go big when there's big memory available, without going too big.
And that's the rub, you might think. How big is too big, in terms of mainframe memory allocated to a DB2 subsystem? Actually, that's not too hard to monitor. The main thing here is to avoid a situation in which mainframe memory is over-committed, and the metric to keep an eye on in that regard is the z/OS LPAR's demand paging rate. This figure, available via a z/OS monitor, is the rate (usually reported as a per-second number) at which z/OS has to bring into real storage, on-demand, pages that were paged out to auxiliary storage (often referred to as page data sets, on disk). What should that rate be? I'll tell you one thing: it shouldn't be zero -- not in my book. If the z/OS system's demand paging rate is zero, that tells me you have an underutilized resource that could be used to improve the performance of your DB2 workload, and you can change that "underutilized" situation by enlarging buffer pools that have high read I/O rates (or by pinning some really performance-critical DB2 objects in memory). Now, while I think that a demand paging rate that's greater than zero is generally a good thing, you don't want that number to go too high. I'm personally very comfortable with a demand paging rate that's between 1 and 10 per second during busy times (i.e., times of high volume workload processing). Low single-digits I like more than high-single digits, but I wouldn't be concerned about the memory resource being under more-than-desired pressure unless the demand paging rate gets into 10+ per second territory (and here I'm not counting the occasional memory-intensive event, such as a dump, that could cause the demand paging rate to temporarily spike to a higher level). Should your aim be a demand paging rate of 10 per second? No. I'm just saying that a rate of less than 1 per second (quite common these days) means that the system's real storage resource is not under any kind of significant pressure, and there is "head room" available for increasing virtual storage usage -- through enlarging buffer pools, for example -- without negatively impacting non-DB2 work on the system.
So, if the demand paging rate on your system is less than 1 per second and you have high read I/O buffer pools and/or you want to pin some DB2 objects in memory, what should you do? How big should you go, buffer pool-wise? Here's a rule of thumb that's worked for me: in my experience, when a single production DB2 subsystem is running on a z/OS LPAR, that LPAR's memory resource is very unlikely to be under an undesirable level of pressure if the DB2 subsystem's buffer pool configuration size is up to 25% of the z/OS LPAR's real storage size. So, if you have a z/OS LPAR with 20 GB of real storage, a production DB2 subsystem buffer pool configuration size of 5 GB (again, meaning the aggregate size of all of the subsystem's active buffer pools) should pose no problem in terms of leaving plenty of memory for address spaces other than DB2's, such as those associated with TSO, CICS, batch, RACF, etc. (keep in mind that when the maximum real storage size was 2 GB, not too many years ago, it was not uncommon for sites to use 50% or even 75% of that for DB2 buffer pools). If you're well south of that 25% level, you should be able to grow your DB2 buffer pool configuration considerably without hurting overall system performance -- just keep an eye, as I've said, on that demand paging rate, and see that it stays in the aforementioned single digits per second range. Already have a DB2 buffer pool configuration size that's 25% (or even more) of the z/OS LPAR's real storage size? That doesn't mean you have to stop there. Just be deliberate about further buffer pool size increases, and don't overextend the memory resource of the system. Also, don't over-do it with respect to the use of PGFIX(YES) for your buffer pools, as I noted in my part one memory-for-MIPS post. And one more thing: if you have more than one production DB2 subsystem on an LPAR (test and development subsystems usually have buffer pool configurations that are smaller than that of a production subsystem), be aware of the TOTAL buffer pool configuration size of ALL the subsystems. Taking this to an extreme, if you have 4 production DB2 subsystems on one z/OS LPAR, don't size the buffer pool configuration of each to be 25% of the LPAR's memory -- that would likely result in a demand paging problem. You might want the TOTAL size of ALL of the subsystem's buffer pool configurations to be no more than half of the amount of real storage assigned to the LPAR.
If you make a buffer pool larger, check that read I/O rate again. If it's gone down, good. If it hasn't, you might want to make the pool larger still, but if you do that and the read I/O rate still doesn't budge, you may want to think in terms of taking some of the more performance-critical objects in that pool and assigning them to a new pool.
Finally, if you use DB2 in data sharing mode, keep in mind that enlarging a local pool on the member subsystems may necessitate an enlargement of the corresponding group buffer pool (GBP) in the coupling facility. What you want to do here is keep the GBP large enough, relative to the aggregate size of the corresponding local pools, to prevent page invalidations due to directory entry reclaims, as such invalidations require member DB2 subsystems to re-read into memory pages that were likely "clean" when invalidated, and that's a drag on system performance (output from the DB2 command -DISPLAY GROUPBUFFERPOOL GDETAIL will show you whether such invalidations are occurring or not -- look for the field labeled "CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS"). To properly size GBPs in this regard, you can make use of the IBM CFSizer tool, available on the Web at http://www-947.ibm.com/systems/support/z/cfsizer/index.html. There's also a quick sizing formula for 4K group buffer pools with the default 5:1 ratio of directory entries to data entries that has long worked well for me (I used it effectively at a DB2 data sharing site just last month): add up the sizes of the local pools and divide that by three, and you have a good GBP size. For example: if you have two member subsystems and BP4 has 15,000 buffers, or 60 MB, on each member, a GBP4 sized at (60 MB + 60 MB) / 3 = 40 MB should not cause page invalidations due to directory entry reclaims (I worked out that formula years ago for 4K GBPs, and haven't worked on a similar one for 8K or 16K or 32K GBPs, but I could do that for those GBP types without too much difficulty).
Whew. More words than usual for one of my blog entries, but I was on a roll and went with it. Hope the information is useful to you. As previously mentioned, I'll post part three of this three-part memory-for-MIPS entry within the next couple of weeks.
Before going further, I'd like to address a topic that's come up a number of times in conversations I've had with DB2 for z/OS DBAs and systems programmers -- that being the difference between GETPAGE requests and DB2 read I/O requests. Plenty of people have the idea that a bigger buffer pool means fewer GETPAGEs. In fact, that is generally NOT the case. Driving down GETPAGEs is a worthwhile objective, since GETPAGE requests -- more than anything else, in my experience -- are the key determinant of the CPU cost of SQL statement execution; however, growing your buffer pool configuration is not the way to reduce GETPAGE activity. Instead, you'd attack that objective through means such as physical database design changes (adjusting table clustering, adding or modifying indexes, etc.) and SQL statement coding (e.g., changing a non-indexable predicate to an indexable predicate). Enlarging your buffer pool configuration is about reducing I/O requests (primarily read requests, which tend to greatly outnumber write I/O requests) and the CPU cycles consumed in driving those requests. Is there a connection between GETPAGE requests and read I/O activity for a buffer pool? Of course there is: when DB2 needs to examine a page from a table space or an index -- usually on behalf of an application program -- it will issue a GETPAGE request for that page. If the page in question is NOT already in the buffer pool to which the table space or index is assigned, the GETPAGE request will result in DB2 initiating a read I/O operation to bring the page into memory (unless the page is to be read into memory via an already-underway prefetch I/O, in which case the requesting program will wait for the completion said prefetch I/O). A bigger buffer pool, then, doesn't mean fewer GETPAGEs, as DB2 will still need to look at the same number of table and/or index pages in executing the same SQL statement (all other things being equal). What a bigger buffer pool DOES mean is that fewer of the GETPAGE requests will result in read I/O operations, since the likelihood of a requested page already being in memory will tend to increase as a buffer pool's size increases.
There is an exception to this rule: if a buffer pool is way too small relative to the number of pages belonging to objects assigned to the pool and the volume of requests for those pages, DB2 could end up hitting the data manager threshold for the pool. That threshold, also known by the acronym DMTH, is reached when 95% of a pool's buffers are non-stealable (either currently in use or updated and not yet externalized to disk or to a coupling facility in a DB2 data sharing system). When that happens, DB2 will issue a GETPAGE for every row retrieved from a page, so that, for a page from which 20 rows are retrieved for a program, 20 GETPAGE requests will be issued, versus one GETPAGE had DMTH not been hit for the pool. Obviously, a big spike in GETPAGE activity will cause a spike in CPU consumption, so keep an eye on DMTH and make sure that you're not hitting that threshold for any of your buffer pools (your DB2 monitor will provide this information, as will the output of the DB2 command -DISPLAY BUFFERPOOL(xxx) DETAIL, where "xxx" could be the name of an individual pool, such as BP4, or the word ACTIVE if you want to see information for all active buffer pools). If DMTH is being hit for a pool, it's likely that the pool is significantly undersized.
On now to making a buffer pool configuration larger. While there's no need to apologize for that (I'll expand on this point in a moment), you want to do it for a reason, and the reason, as just pointed out, is to reduce disk read I/O activity (which in turn reduces CPU consumption). So, your first order of business is to check on the rate of read I/O activity for each of your buffer pools. As pointed out near the end of my part one memory-for-MIPS post, what you're looking for is the TOTAL rate of read I/O requests (synchronous and asynchronous) per second for your buffer pools. Also as pointed out in the part one post, you can get that figure from a DB2 monitor statistics detail report or online display of buffer pool activity, or from the output of the DB2 command -DISPLAY BUFFERPOOL DETAIL (and I describe in the part one post how to use -DISPLAY BUFFERPOOL DETAIL output to get the desired information). If the total read I/O rate for a pool is less than 100 per second, making that pool larger won't hurt anything (assuming the presence of sufficient real storage to back that enlargement, about which I'll have something to say in a bit), but it likely won't move the CPU consumption needle much (though you might want a zero-read-I/O situation associated with "pinning" a DB2 object in memory -- something made more straightforward and effective in a DB2 10 environment via the new PGSTEAL(NONE) buffer pool option, described in the part one post). What should draw your attention are any pools for which the read I/O rate is in the hundreds -- or even thousands -- per second (the highest I've seen with my own eyes at a DB2 for z/OS site is 9000 read I/Os per second for a pool). Taking a big chunk out of these numbers through buffer pool enlargement can result in a significant improvement in CPU efficiency for your DB2 workload.
Now, taking a "big chunk" out of a high rate of disk read I/Os is not likely to happen via itty-bitty steps, by which I mean that you probably won't see much of an impact from adding 1000 buffers to an 80,000-buffer pool. Big-step buffer pool enlargements will often involve increasing a pool's size by 25% or even 50% (a pool that's currently quite small -- say, 10,000 4K buffers in a system with a lot of real storage -- might be doubled in size -- or more -- in a single step, depending on the particular circumstances of the situation). I tend to think big, in terms of buffer pool enlargement, because I so often see buffer pools with high read I/O rates that are way smaller than they could be, given the abundance of memory available on the associated z/OS LPAR. Here's the deal: since 64-bit addressing was introduced in the System z environment some years ago, and the cost of memory was reduced for new mainframe models, it's become more and more common for production z/OS LPARs to be configured with 20 or 40 GB or more of real storage (and sometimes you see a z/OS LPAR with north of 100 GB of real storage -- a z196 server can be configured with up to 3 TB of memory); yet, in these big lakes of memory you find production DB2 subsystems with a total aggregate buffer pool size (meaning, the sum of the sizes of each allocated pool) of a measly 1 GB -- or even less. HELLO! IS ANYONE UNDER THE IMPRESSION THAT YOU GET BONUS POINTS FOR NOT USING A HARDWARE RESOURCE -- MAINFRAME MEMORY -- FOR WHICH YOUR ORGANIZATION PAID MONEY? Come on, folks. That stuff is there to enable your system to perform better, and no subsystem will use more memory to better effect than DB2.
That brings us to the "don't apologize" part. Some DB2 for z/OS people -- especially those who've worked with DB2 for 15 or 20 years or more -- seem to have some kind of a guilt complex over their subsystem's use of mainframe memory resources. "Golly, I'm using a GIGABYTE just for my buffer pools. The other subsystems on the production LPAR don't use that much memory. I should be satisfied with what I have." WRONG. Other z/OS subsystems that handle things like transaction management and security and networking don't NEED the memory that a relational database management system needs, and -- more importantly -- they don't benefit from getting lots more memory the way DB2 does. DB2 for z/OS was architected, from the get-go, to perform better with a large real storage resource. Give DB2 more memory, and -- unless your system is storage-constrained, and I'll get to that -- throughput goes up and CPU consumption (most likely) goes down (though if more memory for DB2 lets you get a lot more work done in a unit of time, CPU utilization could increase, but that's usually OK if it means that, for example, a critical batch run completes in much less time than before). Some DB2 sites -- and more as time goes on -- have figured this out. They've leveraged big mainframe memory resources to greatly increase DB2 buffer pool sizes, with very positive effects on application performance. The biggest buffer pool configuration I've seen for a single DB2 subsystem is approximately 42 GB. Are the people at that shop crazy? Of course not. They have a z/OS system with a large real storage resource (way bigger than 42 GB), and they are taking advantage of that to deliver excellent performance for a high-volume, mission-critical application that accesses a very large database. They key is to go big when there's big memory available, without going too big.
And that's the rub, you might think. How big is too big, in terms of mainframe memory allocated to a DB2 subsystem? Actually, that's not too hard to monitor. The main thing here is to avoid a situation in which mainframe memory is over-committed, and the metric to keep an eye on in that regard is the z/OS LPAR's demand paging rate. This figure, available via a z/OS monitor, is the rate (usually reported as a per-second number) at which z/OS has to bring into real storage, on-demand, pages that were paged out to auxiliary storage (often referred to as page data sets, on disk). What should that rate be? I'll tell you one thing: it shouldn't be zero -- not in my book. If the z/OS system's demand paging rate is zero, that tells me you have an underutilized resource that could be used to improve the performance of your DB2 workload, and you can change that "underutilized" situation by enlarging buffer pools that have high read I/O rates (or by pinning some really performance-critical DB2 objects in memory). Now, while I think that a demand paging rate that's greater than zero is generally a good thing, you don't want that number to go too high. I'm personally very comfortable with a demand paging rate that's between 1 and 10 per second during busy times (i.e., times of high volume workload processing). Low single-digits I like more than high-single digits, but I wouldn't be concerned about the memory resource being under more-than-desired pressure unless the demand paging rate gets into 10+ per second territory (and here I'm not counting the occasional memory-intensive event, such as a dump, that could cause the demand paging rate to temporarily spike to a higher level). Should your aim be a demand paging rate of 10 per second? No. I'm just saying that a rate of less than 1 per second (quite common these days) means that the system's real storage resource is not under any kind of significant pressure, and there is "head room" available for increasing virtual storage usage -- through enlarging buffer pools, for example -- without negatively impacting non-DB2 work on the system.
So, if the demand paging rate on your system is less than 1 per second and you have high read I/O buffer pools and/or you want to pin some DB2 objects in memory, what should you do? How big should you go, buffer pool-wise? Here's a rule of thumb that's worked for me: in my experience, when a single production DB2 subsystem is running on a z/OS LPAR, that LPAR's memory resource is very unlikely to be under an undesirable level of pressure if the DB2 subsystem's buffer pool configuration size is up to 25% of the z/OS LPAR's real storage size. So, if you have a z/OS LPAR with 20 GB of real storage, a production DB2 subsystem buffer pool configuration size of 5 GB (again, meaning the aggregate size of all of the subsystem's active buffer pools) should pose no problem in terms of leaving plenty of memory for address spaces other than DB2's, such as those associated with TSO, CICS, batch, RACF, etc. (keep in mind that when the maximum real storage size was 2 GB, not too many years ago, it was not uncommon for sites to use 50% or even 75% of that for DB2 buffer pools). If you're well south of that 25% level, you should be able to grow your DB2 buffer pool configuration considerably without hurting overall system performance -- just keep an eye, as I've said, on that demand paging rate, and see that it stays in the aforementioned single digits per second range. Already have a DB2 buffer pool configuration size that's 25% (or even more) of the z/OS LPAR's real storage size? That doesn't mean you have to stop there. Just be deliberate about further buffer pool size increases, and don't overextend the memory resource of the system. Also, don't over-do it with respect to the use of PGFIX(YES) for your buffer pools, as I noted in my part one memory-for-MIPS post. And one more thing: if you have more than one production DB2 subsystem on an LPAR (test and development subsystems usually have buffer pool configurations that are smaller than that of a production subsystem), be aware of the TOTAL buffer pool configuration size of ALL the subsystems. Taking this to an extreme, if you have 4 production DB2 subsystems on one z/OS LPAR, don't size the buffer pool configuration of each to be 25% of the LPAR's memory -- that would likely result in a demand paging problem. You might want the TOTAL size of ALL of the subsystem's buffer pool configurations to be no more than half of the amount of real storage assigned to the LPAR.
If you make a buffer pool larger, check that read I/O rate again. If it's gone down, good. If it hasn't, you might want to make the pool larger still, but if you do that and the read I/O rate still doesn't budge, you may want to think in terms of taking some of the more performance-critical objects in that pool and assigning them to a new pool.
Finally, if you use DB2 in data sharing mode, keep in mind that enlarging a local pool on the member subsystems may necessitate an enlargement of the corresponding group buffer pool (GBP) in the coupling facility. What you want to do here is keep the GBP large enough, relative to the aggregate size of the corresponding local pools, to prevent page invalidations due to directory entry reclaims, as such invalidations require member DB2 subsystems to re-read into memory pages that were likely "clean" when invalidated, and that's a drag on system performance (output from the DB2 command -DISPLAY GROUPBUFFERPOOL GDETAIL will show you whether such invalidations are occurring or not -- look for the field labeled "CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS"). To properly size GBPs in this regard, you can make use of the IBM CFSizer tool, available on the Web at http://www-947.ibm.com/systems/support/z/cfsizer/index.html. There's also a quick sizing formula for 4K group buffer pools with the default 5:1 ratio of directory entries to data entries that has long worked well for me (I used it effectively at a DB2 data sharing site just last month): add up the sizes of the local pools and divide that by three, and you have a good GBP size. For example: if you have two member subsystems and BP4 has 15,000 buffers, or 60 MB, on each member, a GBP4 sized at (60 MB + 60 MB) / 3 = 40 MB should not cause page invalidations due to directory entry reclaims (I worked out that formula years ago for 4K GBPs, and haven't worked on a similar one for 8K or 16K or 32K GBPs, but I could do that for those GBP types without too much difficulty).
Whew. More words than usual for one of my blog entries, but I was on a roll and went with it. Hope the information is useful to you. As previously mentioned, I'll post part three of this three-part memory-for-MIPS entry within the next couple of weeks.
Friday, March 30, 2012
DB2 for z/OS: Trading Memory for MIPS (Part 1)
If you are a DB2 for z/OS DBA or systems programmer, chances are that a good bit of what you do is focused on improving the CPU efficiency of your organization's DB2-accessing application workload. There are various ways in which people go about reducing DB2-related CPU consumption, including SQL statement tuning, physical database design changes (e.g., adding or modifying indexes), shifting work to zIIP engines (for example, by going with native SQL procedures versus external stored procedures for data access via DDF), and exploiting new DB2 features and functions (such as hash organization of data in a table space -- a potential winner for tables for which single-row access via a unique key is the dominant data access mode). If you're doing these things, good -- keep doing them; but, let me tell you something: I've been working with DB2 on the mainframe platform since the late 1980s, and to this day I am still taken aback by the number of sites at which one of the best DB2 efficiency-boosters of all is not fully leveraged (and sometimes is hardly leveraged at all). I'm talking about z/OS system memory. You should really be looking to use this resource for all it's worth, because a) System z servers these days often are configured with LOTS of the stuff (you can get 3 terabytes on a z196), b) mainframe memory costs a lot less than it did a few years ago, c) adding memory to your system doesn't affect the cost of your System z software, and d) thoughtful memory utilization offers a variety of ways to enable DB2 to do more while consuming less in the way of processor cycles. In this, the first of a three-part entry, I'll look at maximizing the effectiveness of your DB2 buffer pools without making them larger. In part two, I'll examine the issue of configuring DB2 to use more memory for buffer pools in an unapologetic way -- meaning, when you want more memory resource for your DB2 subsystem, don't ask for it as though you're Oliver Twist ("Please, sir, I want some more"). In part three, I'll look at ways you can leverage mainframe memory for performance purposes via pools other than the buffer pools (e.g., the EDM pool, the sort pool, and the RID pool).
I want to start out this memory-for-MIPS series with information on using the buffer pool virtual storage resource that you already have more beneficially, from a performance perspective, for three reasons: 1) the buffer pool configuration is typically the primary DB2 consumer of a system's memory resource, 2) you can really do some good by tuning what you've got, and 3) before asking for more memory for DB2 buffers, you ought to be making good use of the memory you've got.
Following, then, are some of my favorite hints and tips for getting your DB2 buffer pool house in order:
A buffer pool dedicated to the work file table spaces is a different animal. Let me start by saying that it's a REALLY good idea to have buffer pools that are dedicated to your work file table spaces (one for the 4K table spaces, and one for the 32K table spaces -- the latter being, often, MUCH more heavily utilized in a DB2 9 or 10 environment, versus a DB2 V8 system). In configuring these buffer pools, keep a couple of things in mind:
Steal smart. By default, DB2 utilizes a least-recently-used (LRU) algorithm in identifying buffers that are to be stolen to accommodate new pages being brought into memory from disk. That's the right algorithm to use in most cases; however, if you have a DB2 9 (or earlier) system and you "pin" some objects in memory by assigning those objects to a pool that has as many or more buffers than the objects have pages (e.g., you assign index A, with 200 pages, and index B, with 300 pages, to a pool that has 500 or more buffers) then you should use the first-in, first-out (FIFO) page steal algorithm for the pin-pool. Why? Because FIFO is a simpler, and therefore less costly (CPU-wise), page steal algorithm versus LRU. For a pool used for object pinning, little to no buffer stealing will occur (depending on whether or not you actually have enough buffers in the pool to hold all the pages of the objects assigned to the pool), so why go with the more expensive page steal algorithm?
In a DB2 10 environment you have a new choice here: PGSTEAL(NONE). When that option is specified for a buffer pool, here's what happens:
Use page-fixing of buffers appropriately. Since DB2 V8, you've had the option of fixing a pool's buffers in memory via the PGFIX(YES) option of the -ALTER BUFFERPOOL command. Page-fixing a pool's buffers (meaning that the page frames holding those buffers can't be stolen by z/OS) delivers CPU savings by eliminating the need to fix in memory and then release a buffer every time it receives a page read in from disk or provides a page to be written out to disk (those page fix and release actions become unnecessary when the buffers are always in a fixed-in-memory state). Page-fixing then, can boost a DB2 system's CPU efficiency when it's used for pools that have a high level of I/O activity (and the focus here is usually on read I/O activity). If there are 10 or 20 read I/O operations per second for a buffer pool, fixing that pool's buffers in memory won't have much of an impact on CPU consumption. If the rate of read I/O activity for a pool is hundreds or thousands per second, page-fixing buffers can deliver a nice CPU savings benefit. You might think that it would be easier to specify PGFIX(YES) for all of a DB2 subsystem's buffer pools, but unless you're really awash in mainframe memory I wouldn't recommend that approach. I think that it's a good idea to have some pools -- those with lower rates of read I/O activity -- for which buffers are not page-fixed. That approach provides z/OS with something of a safety valve, referring to stealable page frames holding DB2 buffers, that can be utilized if the system memory resource becomes overtaxed.
You may have heard that DB2 10 will use 1 MB page frames (if available) for page-fixed buffer pools. That's true, and this feature of DB2 10 further boosts the CPU savings effect of fixing buffers in memory by improving the efficiency of virtual-to-real storage address translation (versus a 4 KB page frame usage scenario). Keep this in mind, though: DB2 9 can't use 1 MB page frames for buffer pools, whether page-fixed or not. What this means is that you don't want to configure an overly large portion of your z/OS system's real storage resource to be managed using 1 MB page frames (that's done via the LFAREA parameter of the IEASYSxx member of PARMLIB) until you've migrated to DB2 10 and are likely past the point of needing to fall back to DB2 9. If you want to be conservative, migrate to DB2 10 and THEN configure your z/OS system for 1 MB page frames.
Consider robbing Peter to pay Paul, if Peter has more than he needs and Paul doesn't have enough. As I mentioned up front, this first of my three-part memory-for-MIPS blog entry is focused on making the best use of the memory already allocated for your DB2 buffer pools. That doesn't mean that you can't make a pool larger -- you can, if you decrease the size of another pool by the same amount. Suppose that buffer pools BP2 and BP3 are both sized at 40,000 buffers each in your system. If the read I/O rate for BP2 is 20 per second and the rate for BP3 is 2000 per second, I'd seriously consider taking a big chunk of BP2's buffers away and giving them to BP3 (for example, you might take BP2 from 40,000 down to 20,000 buffers, and take BP3 from 40,000 buffers to 60,000. Such a move might put a serious dent in the high read I/O rate seen for BP3 without substantially increasing the read I/O rate associated with BP2.
As for these read I/O rates I've mentioned, you can get those from a DB2 monitor statistics detail report, or perhaps from a monitor's online display of buffer pool activity. You can also get this information from the output of the DB2 command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL (and I specify ACTIVE in the command to get information for pools that are actually being used). To get per-second figures using the command output, do the following:
Whatever the source of your numbers (DB2 monitor or DB2 command output), what you want, for each pool, is the TOTAL read I/O rate per second. That's the sum of synchronous read I/Os (random + sequential) and asynchronous read I/Os (sequential prefetch + list prefetch + dynamic prefetch). This rate is your guide to appropriate use of buffer page-fixing, and your measuring stick to gauge the impact of, for example, increasing VPSEQT for a work file-dedicated pool, or increasing the size of a high-read-I/O pool by adding buffers taken from a low-read-I/O pool. In part two of my three-part memory-for-MIPS blog entry, we'll revisit read I/O rates in the context of boosting the aggregate size of a buffer pool configuration. Look for that next entry to be posted in a week or two.
I want to start out this memory-for-MIPS series with information on using the buffer pool virtual storage resource that you already have more beneficially, from a performance perspective, for three reasons: 1) the buffer pool configuration is typically the primary DB2 consumer of a system's memory resource, 2) you can really do some good by tuning what you've got, and 3) before asking for more memory for DB2 buffers, you ought to be making good use of the memory you've got.
Following, then, are some of my favorite hints and tips for getting your DB2 buffer pool house in order:
A buffer pool dedicated to the work file table spaces is a different animal. Let me start by saying that it's a REALLY good idea to have buffer pools that are dedicated to your work file table spaces (one for the 4K table spaces, and one for the 32K table spaces -- the latter being, often, MUCH more heavily utilized in a DB2 9 or 10 environment, versus a DB2 V8 system). In configuring these buffer pools, keep a couple of things in mind:
- Almost all of the reads of data in work file table spaces will be of the prefetch variety. Why should you care about this? Because the default value for the virtual pool sequential steal threshold, VPSEQT, is 80. Basically, that setting means that only about 80% of the buffers in a pool dedicated to work file table spaces will get a lot of use, with the other 20% being reserved for relatively infrequent random (single-page) reads. Raise that threshold value significantly, and it's like getting more memory for free (you should see fewer prefetch read I/Os for the pool in question, and fewer read I/Os means less CPU spent in driving I/O operations). I've often seen a VPSEQT value of 95-99% for a work file-dedicated buffer pool used to good effect.
- The motivation for externalizing updated work file table space pages to disk is not the same as it is for application table spaces and indexes. For application data (in table spaces and indexes), you don't want too much time to pass between the updating of a page and the externalization of that changed page to disk. Why? Because if the DB2 subsystem should fail, restart processing will be elongated if a large number of pending writes have to be processed (part of restart following abnormal termination involves DB2 making sure that committed updates made to pages that were in memory and not yet externalized at the time of the failure are reflected in the data on disk; otherwise, the data on disk would be inconsistent). It's a different story for pages belonging to work file table spaces. Those table spaces are conceptually like scratch pads and don't have to be processed during restart as do application table spaces and indexes and the DB2 catalog. So, for the work file table spaces, page externalization is about ensuring that the associated buffer pools don't get too full of non-stealable pages. That being the case, the deferred write and vertical deferred write queue thresholds (DWQT and VDWQT) can be raised for work file-dedicated buffer pools without adversely affecting DB2 restart performance. Raising these thresholds for the pools used for work file table spaces reduces disk write activity and that reduces CPU consumption. Some folks specify 70 and 40 for DWQT and VDWQT, respectively, some go with 80 and 50, and some go with even higher values (the defaults are 30 for DWQT and 5 for VDWQT). A word of caution: at one site I saw that DWQT and VDWQT for a work file-dedicated buffer pool were both set to 90, and the data manager threshold (indicating that 95% of a pool's buffers are unavailable) was hit a great many times per hour for that pool. That is REALLY BAD for performance (big spike in GETPAGE activity), so watch that you don't go overboard with choosing elevated values for the deferred write thresholds for your work file buffer pools (a DB2 monitor will tell you if the data manager threshold has been reached for a buffer pool; so, too, will output of the DB2 command -DISPLAY BUFFERPOOL DETAIL).
Steal smart. By default, DB2 utilizes a least-recently-used (LRU) algorithm in identifying buffers that are to be stolen to accommodate new pages being brought into memory from disk. That's the right algorithm to use in most cases; however, if you have a DB2 9 (or earlier) system and you "pin" some objects in memory by assigning those objects to a pool that has as many or more buffers than the objects have pages (e.g., you assign index A, with 200 pages, and index B, with 300 pages, to a pool that has 500 or more buffers) then you should use the first-in, first-out (FIFO) page steal algorithm for the pin-pool. Why? Because FIFO is a simpler, and therefore less costly (CPU-wise), page steal algorithm versus LRU. For a pool used for object pinning, little to no buffer stealing will occur (depending on whether or not you actually have enough buffers in the pool to hold all the pages of the objects assigned to the pool), so why go with the more expensive page steal algorithm?
In a DB2 10 environment you have a new choice here: PGSTEAL(NONE). When that option is specified for a buffer pool, here's what happens:
- When an object assigned to a PGSTEAL(NONE) pool is first accessed, DB2 will, in the background, prefetch every page of that object into the pool (the application process that first accessed the object doesn't have to wait for this prefetch-all process to finish -- it gets the pages that it needs and goes on from there).
- For further access to the now-fully-in-memory object, the DB2 optimizer will assume that no I/Os will be required, and this assumption will factor into SQL statement access path selection.
- If in fact the pool does not have enough buffers to hold all of the pages of an object assigned to the pool, buffers will be stolen as needed based on the FIFO page steal algorithm.
Use page-fixing of buffers appropriately. Since DB2 V8, you've had the option of fixing a pool's buffers in memory via the PGFIX(YES) option of the -ALTER BUFFERPOOL command. Page-fixing a pool's buffers (meaning that the page frames holding those buffers can't be stolen by z/OS) delivers CPU savings by eliminating the need to fix in memory and then release a buffer every time it receives a page read in from disk or provides a page to be written out to disk (those page fix and release actions become unnecessary when the buffers are always in a fixed-in-memory state). Page-fixing then, can boost a DB2 system's CPU efficiency when it's used for pools that have a high level of I/O activity (and the focus here is usually on read I/O activity). If there are 10 or 20 read I/O operations per second for a buffer pool, fixing that pool's buffers in memory won't have much of an impact on CPU consumption. If the rate of read I/O activity for a pool is hundreds or thousands per second, page-fixing buffers can deliver a nice CPU savings benefit. You might think that it would be easier to specify PGFIX(YES) for all of a DB2 subsystem's buffer pools, but unless you're really awash in mainframe memory I wouldn't recommend that approach. I think that it's a good idea to have some pools -- those with lower rates of read I/O activity -- for which buffers are not page-fixed. That approach provides z/OS with something of a safety valve, referring to stealable page frames holding DB2 buffers, that can be utilized if the system memory resource becomes overtaxed.
You may have heard that DB2 10 will use 1 MB page frames (if available) for page-fixed buffer pools. That's true, and this feature of DB2 10 further boosts the CPU savings effect of fixing buffers in memory by improving the efficiency of virtual-to-real storage address translation (versus a 4 KB page frame usage scenario). Keep this in mind, though: DB2 9 can't use 1 MB page frames for buffer pools, whether page-fixed or not. What this means is that you don't want to configure an overly large portion of your z/OS system's real storage resource to be managed using 1 MB page frames (that's done via the LFAREA parameter of the IEASYSxx member of PARMLIB) until you've migrated to DB2 10 and are likely past the point of needing to fall back to DB2 9. If you want to be conservative, migrate to DB2 10 and THEN configure your z/OS system for 1 MB page frames.
Consider robbing Peter to pay Paul, if Peter has more than he needs and Paul doesn't have enough. As I mentioned up front, this first of my three-part memory-for-MIPS blog entry is focused on making the best use of the memory already allocated for your DB2 buffer pools. That doesn't mean that you can't make a pool larger -- you can, if you decrease the size of another pool by the same amount. Suppose that buffer pools BP2 and BP3 are both sized at 40,000 buffers each in your system. If the read I/O rate for BP2 is 20 per second and the rate for BP3 is 2000 per second, I'd seriously consider taking a big chunk of BP2's buffers away and giving them to BP3 (for example, you might take BP2 from 40,000 down to 20,000 buffers, and take BP3 from 40,000 buffers to 60,000. Such a move might put a serious dent in the high read I/O rate seen for BP3 without substantially increasing the read I/O rate associated with BP2.
As for these read I/O rates I've mentioned, you can get those from a DB2 monitor statistics detail report, or perhaps from a monitor's online display of buffer pool activity. You can also get this information from the output of the DB2 command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL (and I specify ACTIVE in the command to get information for pools that are actually being used). To get per-second figures using the command output, do the following:
- Issue -DISPLAY BUFFERPOOL(ACTIVE) DETAIL at the beginning of some busy hour of the day (referring to workload being processed by the DB2 subsystem). The output of this issuance of the command does not have to be retained.
- Issue -DISPLAY BUFFERPOOL(ACTIVE) DETAIL a second time, one hour after issuing the command the first time. Retain the output from this issuance of the command.
- A few minutes after issuing the command the second time, issue it a third time. All you want from the output of this issuance of the command is the timestamp value that appears in the DSNB409I part of the output (e.g., "INCREMENTAL STATISTICS SINCE 10:00:48 MAR 19, 2012"). That's the "to" time for the interval of activity captured in the second issuance of the -DISPLAY BUFFERPOOL(ACTIVE) DETAIL command. The "from" time for this interval is the timestamp value found in the output of the second issuance of the command. Subtract the "from" timestamp value from the "to" value, and you have the number of seconds in the interval. Divide the read I/O figures in the output of the second issuance of the -DISPLAY BUFFERPOOL(ACTIVE) DETAIL command by this number of seconds, and you have per-second rates of activity.
Whatever the source of your numbers (DB2 monitor or DB2 command output), what you want, for each pool, is the TOTAL read I/O rate per second. That's the sum of synchronous read I/Os (random + sequential) and asynchronous read I/Os (sequential prefetch + list prefetch + dynamic prefetch). This rate is your guide to appropriate use of buffer page-fixing, and your measuring stick to gauge the impact of, for example, increasing VPSEQT for a work file-dedicated pool, or increasing the size of a high-read-I/O pool by adding buffers taken from a low-read-I/O pool. In part two of my three-part memory-for-MIPS blog entry, we'll revisit read I/O rates in the context of boosting the aggregate size of a buffer pool configuration. Look for that next entry to be posted in a week or two.
Subscribe to:
Posts (Atom)