Just a short blog entry this week to alert everyone to a newly published IBM redbook titled "DB2 UDB for z/OS: Design Guidelines for High Performance and Availability." This redbook is an essential read for anyone who is interested in squeezing the highest levels of availability and optimum performance from a mainframe DB2 application.
In just under 500 pages the authors share a wealth of tuning techniques that all DB2 shops should have access to. Download the manual (SG24-7134) for free from the web by clicking here.
News, views, and items of interest on IBM's Db2 database management system and mainframes.
Sunday, January 15, 2006
Monday, January 09, 2006
Basic DB2 Buffering and Memory Guidelines
One of the most important areas for tuning DB2 subsystem performance is memory usage. DB2 for z/OS uses memory for buffer pools, the EDM pool, RID pool and sort pools to cache data and structures in memory. The better memory is allocated to these structures, the better DB2 will perform.
DWQT –this value is the deferred write threshold; it is expressed as a percentage of the virtual buffer pool that might be occupied by unavailable pages. When this threshold is reached DB2 will start to schedule write I/Os to externalize data. The default is 50%, which is likely to be too high for most shops.
VDWQT – this value is the vertical deferred write threshold; it is basically the same as DWQT, but for individual data sets. The default is 10%, which once again is quite likely to be too high for many shops.
VPSEQT – this value is the sequential steal threshold; it is a expressed as a percentage of the virtual buffer pool that can be occupied by sequentially accessed pages. Tune buffer pools for sequential access such as scans and sorting by modifying VPSEQT to a larger value. The default is 80%.
VPPSEQT – this value is the sequential steal threshold for parallel operations; the default value is 50%.
VPXPSEQT – this value is assisting parallel sequential threshold; it is basically the VPPSEQT for opertaions from another DB2 subsystem in the data sharing group.
These parameters can be changed using the ALTER BUFFERPOOL command. Additionally, prior to DB2 V8, hiperpools can be created to back up DB2 virtual buffer pools with additional memory. DB2 provides several tuning knobs for hiperpools, too, including HPSIZE to adjust the size of hiperpools and HPSEQT to adjust the hiperpool sequential steal threshold. Hiperpools are obsolete as of V8 though, so if you don't use them today you should probably spend your time migrating to V8 instead of implementing soon-to-be-obsolete hiperpools.
With the advent of DB2 V8, there is more memory at your disposal for DB2's use. V8 is able to surmount the limitation of 2GB real storage that was imposed due to S/390's 31-bit addressing. Theoretically, with 64-bit addressing DB2 could have up to 16 exabytes of virtual storage addressability to be used by a single DB2 address space. Now there is some room for growth!
In addition to buffer pools, DB2 uses memory for the EDM pool. The EDM pool is used for caching internal structures used by DB2 programs. This includes DBDs, SKCTs, CTs, SKPTs, and PTs. It also includes the authorization cache for plans and packages, as well as the cache for dynamic SQL mini-plans.
With V8, DB2 breaks the EDM pool into separate pools: one for DBDs, one for the dynamic statement cache, and the final one for program elements (CTs, SKCTs, PTs, SKPTs).
As a general rule of thumb, shoot for an 80 percent hit rate with the EDM pool; this means that only one out every five times should a structure need to be loaded from disk into the EDM pool.
Finally, remember that buffer and EDM pool tuning are in-depth subjects that cannot be adequately covered in a high-level blog entry such as this. So, study those IBM DB2 manuals - and learn by doing. Additionally, there is much more to proper DB2 system performance tuning than memory tuning. Other system elements requiring attention include allied agent setup (CICS, TSO, etc.), network configuration, locking, logging, and Parallel Sysplex configuration and management for DB2 data-sharing shops.
When allocating DB2 buffer pools, keep these rules of thumb in mind:
- Don't allocate everything to a single buffer pool (e.g., BP0); use a multiple buffer pool strategy.
- Explicitly specify a buffer pool for every table space and index.
- Isolate the DB2 Catalog in BP0; put user and application DB2 objects into other buffer pools.
- Consider separating indexes from table spaces with each in their own dedicated buffer pools.
- Consider isolating heavily hit data into its own buffer pool to better control performance.
- Consider isolating sorts into a single buffer pool and tuning for mostly sequential access (e.g. BP7).
- Consider separating DB2 objects into separate buffer pools that have been configured for sequential verses random access.
DWQT –this value is the deferred write threshold; it is expressed as a percentage of the virtual buffer pool that might be occupied by unavailable pages. When this threshold is reached DB2 will start to schedule write I/Os to externalize data. The default is 50%, which is likely to be too high for most shops.
VDWQT – this value is the vertical deferred write threshold; it is basically the same as DWQT, but for individual data sets. The default is 10%, which once again is quite likely to be too high for many shops.
VPSEQT – this value is the sequential steal threshold; it is a expressed as a percentage of the virtual buffer pool that can be occupied by sequentially accessed pages. Tune buffer pools for sequential access such as scans and sorting by modifying VPSEQT to a larger value. The default is 80%.
VPPSEQT – this value is the sequential steal threshold for parallel operations; the default value is 50%.
VPXPSEQT – this value is assisting parallel sequential threshold; it is basically the VPPSEQT for opertaions from another DB2 subsystem in the data sharing group.
These parameters can be changed using the ALTER BUFFERPOOL command. Additionally, prior to DB2 V8, hiperpools can be created to back up DB2 virtual buffer pools with additional memory. DB2 provides several tuning knobs for hiperpools, too, including HPSIZE to adjust the size of hiperpools and HPSEQT to adjust the hiperpool sequential steal threshold. Hiperpools are obsolete as of V8 though, so if you don't use them today you should probably spend your time migrating to V8 instead of implementing soon-to-be-obsolete hiperpools.
With the advent of DB2 V8, there is more memory at your disposal for DB2's use. V8 is able to surmount the limitation of 2GB real storage that was imposed due to S/390's 31-bit addressing. Theoretically, with 64-bit addressing DB2 could have up to 16 exabytes of virtual storage addressability to be used by a single DB2 address space. Now there is some room for growth!
In addition to buffer pools, DB2 uses memory for the EDM pool. The EDM pool is used for caching internal structures used by DB2 programs. This includes DBDs, SKCTs, CTs, SKPTs, and PTs. It also includes the authorization cache for plans and packages, as well as the cache for dynamic SQL mini-plans.
With V8, DB2 breaks the EDM pool into separate pools: one for DBDs, one for the dynamic statement cache, and the final one for program elements (CTs, SKCTs, PTs, SKPTs).
As a general rule of thumb, shoot for an 80 percent hit rate with the EDM pool; this means that only one out every five times should a structure need to be loaded from disk into the EDM pool.
Finally, remember that buffer and EDM pool tuning are in-depth subjects that cannot be adequately covered in a high-level blog entry such as this. So, study those IBM DB2 manuals - and learn by doing. Additionally, there is much more to proper DB2 system performance tuning than memory tuning. Other system elements requiring attention include allied agent setup (CICS, TSO, etc.), network configuration, locking, logging, and Parallel Sysplex configuration and management for DB2 data-sharing shops.
Monday, December 19, 2005
Minimizing Lock Contention Issues
I frequently get e-mails with DB2 questions and I plan to start posting answers to some of the more common ones up here.
One issue that comes up a lot is dealing with locking issues. Usually it is posed by someone who is experiencing timeouts in an online environment and they want to know how to minimize them. Here is some guidance.
When you experience a timeout, it means that another process holds a lock on the data that you are trying to modify. So, it stands to reason that you should try to minimize the duration of locks that are being held in your system. There are some approaches you can take to achieve this, but for the most part, they require programming changes.
First of all, make sure that all of your batch processes -- especially any that run during the same timeframe, but really all batch process -- have a COMMIT strategy. This means that your programs should issue a COMMIT after processing "a set number of" inserts, updates, and deletes. A COMMIT will tell DB2 to make the changes permanent and releases locks.
A good approach is to set a counter that is incremented after every modification. Then, check it and when it exceeds a predefined threshold -- say 25 or 50 or 100 modications -- then issue a COMMIT. You should make the threshold an input parameter so that you can change it as the workload in your system changes. For example, make it 25 when concurrent activity is high, but ramp it up to 100 or higher when it is low. Failure to issue COMMITs will result in timeouts, as well as possibly deadlocks and lock escalation.
(Also, please note that these are just sample numbers and not necessarily the correct numbers to start with at your shop.)
A good article to read regarding COMMIT strategies is "The Woes of Commitment" by Bonnie Baker.
Next, look at all of your programs, batch and online, and try to save the data modification statements to as close to the COMMIT as possible. By saving the data modification until right before you issue a COMMIT, you reduce the overall average lock duration. This should result in reducing contention and therefore, the number of timeouts.
If you want to investigate the timeout details, be sure to examine the statistics trace class 3 and IFCID 0196 for timeouts (IFCID 0172 is for deadlocks).
One issue that comes up a lot is dealing with locking issues. Usually it is posed by someone who is experiencing timeouts in an online environment and they want to know how to minimize them. Here is some guidance.
When you experience a timeout, it means that another process holds a lock on the data that you are trying to modify. So, it stands to reason that you should try to minimize the duration of locks that are being held in your system. There are some approaches you can take to achieve this, but for the most part, they require programming changes.
First of all, make sure that all of your batch processes -- especially any that run during the same timeframe, but really all batch process -- have a COMMIT strategy. This means that your programs should issue a COMMIT after processing "a set number of" inserts, updates, and deletes. A COMMIT will tell DB2 to make the changes permanent and releases locks.
A good approach is to set a counter that is incremented after every modification. Then, check it and when it exceeds a predefined threshold -- say 25 or 50 or 100 modications -- then issue a COMMIT. You should make the threshold an input parameter so that you can change it as the workload in your system changes. For example, make it 25 when concurrent activity is high, but ramp it up to 100 or higher when it is low. Failure to issue COMMITs will result in timeouts, as well as possibly deadlocks and lock escalation.
(Also, please note that these are just sample numbers and not necessarily the correct numbers to start with at your shop.)
A good article to read regarding COMMIT strategies is "The Woes of Commitment" by Bonnie Baker.
Next, look at all of your programs, batch and online, and try to save the data modification statements to as close to the COMMIT as possible. By saving the data modification until right before you issue a COMMIT, you reduce the overall average lock duration. This should result in reducing contention and therefore, the number of timeouts.
If you want to investigate the timeout details, be sure to examine the statistics trace class 3 and IFCID 0196 for timeouts (IFCID 0172 is for deadlocks).
Monday, December 12, 2005
Help Defining DB2's Business Value
Every now and then those of us working on mainframe platforms are confronted by folks who are looking to eliminate mainframes. This can happen for any number of reasons. Maybe the confronter view mainframes as “old technology,” or “mainframes are too costly,” or some other trumped up charge. Too often, however, we respond to such claims emotionally instead of with reason and knowledge.
Fortunately, IBM has come up with some help for those of us working with DB2 for z/OS. IBM has published – and made available for free download – a great little manual titled The Business Value of DB2 for z/OS. In this book you can learn all about the ROI that mainframe DB2 can offer your organization. Instead of all of those technical details that DBAs tend to savor, this document will assist you in analyzing and communicating the many business benefits provided by the combination of DB2 running on z/OS.
The book covers topics such as up-to-date details on how DB2 can integrate with, and enable sharing of information across multiple platforms. It discusses how the mainframe scales to manage large volumes of data. And the manual also describes how IBM’s on-going “on demand” initiative has improved the manageability of DB2.
If you use DB2 for z/OS, you should download a copy of this redbook. It is free, after all. And the next time that someone challenges the mainframe as an on-going, viable platform for business computing you will have a powerful, unemotional tool to help battle that misconception.
Fortunately, IBM has come up with some help for those of us working with DB2 for z/OS. IBM has published – and made available for free download – a great little manual titled The Business Value of DB2 for z/OS. In this book you can learn all about the ROI that mainframe DB2 can offer your organization. Instead of all of those technical details that DBAs tend to savor, this document will assist you in analyzing and communicating the many business benefits provided by the combination of DB2 running on z/OS.
The book covers topics such as up-to-date details on how DB2 can integrate with, and enable sharing of information across multiple platforms. It discusses how the mainframe scales to manage large volumes of data. And the manual also describes how IBM’s on-going “on demand” initiative has improved the manageability of DB2.
If you use DB2 for z/OS, you should download a copy of this redbook. It is free, after all. And the next time that someone challenges the mainframe as an on-going, viable platform for business computing you will have a powerful, unemotional tool to help battle that misconception.
Tuesday, December 06, 2005
The Aging Mainframer
A continuing, lingering perception that the mainframe is dead continues on in some parts of the IT industry. It seems that we constantly hear that big IT shops are getting rid of their mainframes. But rarely do we ever hear about it after the fact. No, it is usually reported right when someone thinks that it is a good idea.
Now don't get me wrong. I'm sure there are some shops that have removed their mainframe. But I'm also sure that there are many more that thought about it but couldn't do it -- as well as those who wouldn't even consider it.
A bigger problem for the mainframe than the misguided notion that it is more costly than other computing platforms is the aging of the mainframe workforce. This is a reality. If you don't believe me, go to a SHARE conference and fix your eyeballs on some of the dinosaurs attending mainframe sessions there (myself included).
Basically, the problem is that mainframe experts are getting older and slowly retiring. And who will replace them? Most young IT professionals do not choose to work on mainframe systems, instead choosing to concentrate on the latest technology bandwagons -- things like Windows and Linux, open source and so on. Put one of these newbies in front of a terminal and introduce them to the joys of JCL, ISPF and COBOL, then watch them scream out the door yelling "I want my Java!" (And who can blame them?)
But this is actually an inaccurate perception. You see, mainframe no longer means ugly old green screens. Today's mainframe environment is quite different from the mainframe of yesteryear. That hulking, water-cooled beast you may remember has been replaced with chip-based, CMOS, air-cooled systems. Today's mainframes are easier to hook together using Parallel Sysplex technology. And all of the "modern" technology used on Windows and Linux platforms works on the mainframe, too. Yes, that means XML, TCP/IP, Java and so on all work on the mainframe, too.
Nowadays, the biggest mainframe "problems" are training and PR. Let's focus on training first. Mainframe technology is not taught by most universities these days; this really needs to change. What is needed is a comprehensive educational program delivered through major universities, as well as IT-focused institutions like DeVry and NorthFace universities. The program should be sponsored by major mainframe vendors, which could provide hardware and software, as well as a conduit for hiring graduates. Actually, IBM is doing something just like this nowadays. An ongoing mainframe program in the universities will help to further promote and extend the mainframe. And that is goodness.
And why would universities be interested in such a program? Employability of their graduates! As the current crop of mainframe experts retire, companies will have to replace them. I'd venture to guess that 10 years or so down the line, it will be easier for an IMS DBA, for example, to get a job offer than an Oracle DBA. The demand will be greater for the IMS talent because the supply is so low.
The publicity component is a bit more difficult. So much has been written and implied about the mainframe being dead that a lot folks believe it. But the mainframe continues to be a robust, viable component of today's IT infrastructure. Organizations continue to add more MIPS, deploy more applications and run their most important, mission-critical applications on mainframe computers. Until this aspect of the mainframe is publicized more, the existing perception is likely to linger.
Or maybe we should just give the mainframe a new name and pretend that it is a new technology with better availability, scalability and performance than the existing platforms - how about a name like the "AlwaysAvailable"?
This posting is a slightly revised version of a piece I wrote for Search390.com.
If you'd like to read the original, click here (registration required).
Now don't get me wrong. I'm sure there are some shops that have removed their mainframe. But I'm also sure that there are many more that thought about it but couldn't do it -- as well as those who wouldn't even consider it.
A bigger problem for the mainframe than the misguided notion that it is more costly than other computing platforms is the aging of the mainframe workforce. This is a reality. If you don't believe me, go to a SHARE conference and fix your eyeballs on some of the dinosaurs attending mainframe sessions there (myself included).
Basically, the problem is that mainframe experts are getting older and slowly retiring. And who will replace them? Most young IT professionals do not choose to work on mainframe systems, instead choosing to concentrate on the latest technology bandwagons -- things like Windows and Linux, open source and so on. Put one of these newbies in front of a terminal and introduce them to the joys of JCL, ISPF and COBOL, then watch them scream out the door yelling "I want my Java!" (And who can blame them?)
But this is actually an inaccurate perception. You see, mainframe no longer means ugly old green screens. Today's mainframe environment is quite different from the mainframe of yesteryear. That hulking, water-cooled beast you may remember has been replaced with chip-based, CMOS, air-cooled systems. Today's mainframes are easier to hook together using Parallel Sysplex technology. And all of the "modern" technology used on Windows and Linux platforms works on the mainframe, too. Yes, that means XML, TCP/IP, Java and so on all work on the mainframe, too.
Nowadays, the biggest mainframe "problems" are training and PR. Let's focus on training first. Mainframe technology is not taught by most universities these days; this really needs to change. What is needed is a comprehensive educational program delivered through major universities, as well as IT-focused institutions like DeVry and NorthFace universities. The program should be sponsored by major mainframe vendors, which could provide hardware and software, as well as a conduit for hiring graduates. Actually, IBM is doing something just like this nowadays. An ongoing mainframe program in the universities will help to further promote and extend the mainframe. And that is goodness.
And why would universities be interested in such a program? Employability of their graduates! As the current crop of mainframe experts retire, companies will have to replace them. I'd venture to guess that 10 years or so down the line, it will be easier for an IMS DBA, for example, to get a job offer than an Oracle DBA. The demand will be greater for the IMS talent because the supply is so low.
The publicity component is a bit more difficult. So much has been written and implied about the mainframe being dead that a lot folks believe it. But the mainframe continues to be a robust, viable component of today's IT infrastructure. Organizations continue to add more MIPS, deploy more applications and run their most important, mission-critical applications on mainframe computers. Until this aspect of the mainframe is publicized more, the existing perception is likely to linger.
Or maybe we should just give the mainframe a new name and pretend that it is a new technology with better availability, scalability and performance than the existing platforms - how about a name like the "AlwaysAvailable"?
This posting is a slightly revised version of a piece I wrote for Search390.com.
If you'd like to read the original, click here (registration required).
Subscribe to:
Posts (Atom)