DB2 LUW Performance: a VOLATILE topic

Do you have a table that has statistical mood swings? Sometimes the table has a few rows, and sometimes it has many? Or would you like to strongly encourage the DB2 optimizer to use available indexes? VOLATILE isn't a nice description for a person, but you can assign this label to a moody table and often get excellent results.

---

We've worked with a number of customers and clients that have successfully altered a table to indicate that it is volatile with outstanding results. The syntax is:

• alter table SCHEMA.TABLENAME volatile;

It's that easy and the change takes effect immediately for all statements that are subsequently prepared. If you think you might have statements in the package cache that reference your volatile table, remember to flush the package cache with the command:

• flush package cache dynamic;

When should you use volatile?

By altering a table to be VOLATILE, you are basically telling the DB2 Optimizer NOT to trust the catalog statistics. This is appropriate when a table frequently grows and shrinks in size. We have found that the effect is somewhat akin to using DB2 Optimization Level 0 (zero) for SQL that accesses the table. Catalog statistics are ignored, and if an index is available with matching columns, then it will be used.

Many applications have tables that grow and shrink in size. One example might be a table that maintains a list of logged on users - the number of rows could fluctuate from a handful to several hundred or thousand. You probably want DB2 using an index on USERID to avoid costly scans and lock contention problems.

During a recent Webinar, one of our participants asked if RUNSTATS should still be executed against a table marked VOLATILE. Well, since VOLATILE tells DB2's optimizer NOT to trust the statistics, then running RUNSTATS seems like an exercise in futility and a waste of time. Don't bother.

A shameless marketing moment

Are you watching The DB2Night Show?

This webinar series is sponsored by DBI. Our simple mission is to Educate, Inform, and Entertain the DB2 LUW community. Dry boring webinars are now a thing of the past. We've had shows on SQL Snapshots, Oracle Enablement, DB2 PureScale, Top Ten Lists, DB2 HADR versus Xkoto Gridscale, the DB2 Workload Manager, Free DB2 Application Development tools (a head to head comparison of Data Studio versus Quest Toad), DB2 LUW versus SQL Server and Oracle Express, and many others.

Our 10th show on 8 January 2010 at 10am CST is themed "DEAR IBM..." and we've lined up some top shelf IBM Executives for our guests - meet and greet Sal Vella (Vice President, Development, Distributed Data Servers and Data Warehousing), Tim Vincent (IBM Fellow and Chief Architect DB2 LUW), and Drew Bradstock (DB2 LUW Product Manager).

In our 11th Episode, special guest John Hornibrook, Manager - Query Optimization Development, from the IBM Toronto Lab will share with us details of DB2 9.7 optimization improvements and new Explain features made available in FP1.

Just for Fun

I did it. I got a Wii for Christmas. It told me my BMI was too high. Now I've been called "overweight" by my wife and friends, but this is the ultimate insult to have a toy game tell you that you need to shed some weight. Enter stage left - New Years resolutions. You can probably guess what's on my list. I wonder if you'll recognize me at IDUG North America in Tampa!

Happy 2010 to everyone!
Cheers,
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant & Data Management Champion