Counting Down the DB2 Performance Top 40

The title of this blog posting is the title of one of my IDUG NA presentations this year. I'm blogging about it (briefly) today to solicit input and comments. I have my own ideas about the things I'll be covering in this presentation, but if you've got your own favorite performance "thing" that you think should be covered in a Top 40 presentation like this, please share it as a comment here on the blog.

Keep in mind that the presentation is a DB2 for z/OS presentation, so I won't be covering LUW or iSeries stuff.

VOLATILE: A Useful Little Keyword

Just a short blog entry today to remind everyone about the VOLATILE keyword. This keyword was added in DB2 Version 8 and it can be specified on a table using CREATE TABLE and/or ALTER TABLE statements.

OK, so what will VOLATILE do? Basically, this keyword is used to indicate that the volume of data in the table is volatile and is likely to fluctuate. One common scenario where VOLATILE will be helpful is for tables that are emptied nightly and then repopulated the next day, such as an input queue.

When you specify the VOLATILE keyword on a table, BIND will favor using indexed access paths, even if the table was empty when RUNSTATS was run.

ERP environments (e.g. SAP, Peoplesoft) with thousands of tables typically have some tables that meet these criteria. Even worse, it is not uncommon for DBAs to have no idea of the actual content or use for many of those thousands of tables generated by the ERP installation. Some are not used based on which modules of the ERP system you implement, but the tables get created anyway. Many DBAs simply maintain all of the tables provided with the ERP system, whether they are used or not, including running image copies and gathering RUNSTATS for them... and many are empty tables.

Collecting statistics on an empty table populates the catalog with stats indicating that the table contains no data. And, of course, when access paths are generated using those statistics DB2 will probably favor a scan because the table is small (how much smaller can you get than empty?) But some of those tables are volatile, going from empty to perhaps hundreds of thousands of rows during processing.

Of course, if the table is actually empty (or contains only a small amount of data), and VOLATILE is specified, DB2 will use an index if one exists, which can degrade performance a bit. But that is a smaller price to pay than scanning thousands of rows, isn't it?

So the answer is to use the VOLATILE keyword for these type of tables... your users will be glad you did.

Recovery AssuranceExpert for DB2 z/OS: Automating the IT risk management of business availability

Business availability is more than just having a reliable hardware and database platform in place. Even the best high availability environment cannot safeguard itself from logical errors. Since most companies cannot afford downtime, it is important that the enterprise data on which they depend is always available.

Well-planned recovery procedures should be able to assure a complete recovery of enterprise-critical data within a pre-defined time window that provides for minimum disruption of the business. However, within complex environments, it is nearly impossible to perform recovery tests without disrupting the production system. Therefore, even the best-planned recovery scenarios fail because of operational risks resulting from unforeseen and typically immeasurable vulnerabilities.

If you are interested in minimizing the risk associated with DB2 for z/OS availability and recoverability, read this white paper by Brenda Honeycutt to learn about the value of regular, periodic health checks to assure your recovery time objectives for DB2 recoverability.

Hectic

Just a quick post to apologize for not posting here as often as I would like. I have been traveling quite a lot the past month or so and just haven't had the time to keep up with everything while I've been on the road. But don't give up on me... I'll be back and blogging here again after the New Year.

In the meantime, be sure to check in regularly on my other blog, Data Management Today.

And if you live in Richmond or Detroit, you can see me present at your local user group next week... Tuesday in Richmond (12/9/08) and Thursday in Detroit (12/11/08).

There is Still Time to Attend IDUG Regional Forums

Just a quick entry today to remind everyone that IDUG has started conducting regional events. The first one was held last week in San Ramon, California, and next week two events will be held: in Camp Hill, Pennsylvania, and Kansas City, Missouri, (actually Lenexa, KS). The forums offer you an opportunity to obtain DB2 education rather quickly and inexpensively.

Each Forum offers 2 days of education with 2 tracks: one covering DB2 for z/OS and another covering DB2 for LUW. IDUG is offering full two day registrations for $425 and single day registrations for only $225.

Here are the scheduled dates:

* Camp Hill, PA - November 17 and 18
* Kansas City, MO - November 19 and 20

Check out the links above for the full list of sessions in your area.

I'll be delivering my presentation titled "DB2 9: For Developer's Only" at both of these forums. And there will be many other great speakers there, too!