What is a “Volatile” table?

There is a flag that can be set on DB2 tables to mark them as “Volatile”. But not everyone knows what a volatile table is or what this does, so I thought I’d write a quick post on it.

A true volatile table is one that is used only for transient data – data that is put into tables and then quickly removed, meaning that a table marked as volatile could have zero rows one minute, 40,000 the next, and then back to zero. There are many applications that use DB2 in this way – Tivoli databases, ESB, and others.

Within DB2, marking a table as volatile pushes db2 to use an index over a table scan even if the optimizer doesn’t recommend usage of an index. This doesn’t mean the best index will necessarily be chosen, but some index will. Some say that marking a table as volatile makes stats completely unused. I’ve seen evidence that it is not true – doing runstats on a volatile table when it has 0 rows and then having larger numbers of rows in the table and activity against that can cause severe performance problems – to the tune of thousands of deadlocks per hour. In this scenario, you must either remove runstats from the table or do an updated runstats on the table. I have a client now trying doing runstats on a volatile table only if the current # of rows in the table is larger than that reported by the last runstats. I’ll post about that later if I end up liking it.

WebSphere Commerce has, I believe, 8 tables marked as volatile in Commerce 6 and 9 marked as volatile in Commerce 7. The only problem with this is that they are not truly volatile tables. They have data that grows steadily over time and only decreases in size with dbclean or other data pruning activities. I’ve actually seen some severe deadlocking problems caused by this, and for a couple of clients, have gone as far as to remove the volatile flag from ordiadjust. I don’t recommend this for all clients, and it’s a change that is not supported by Commerce Support, but it can really help clear things up to have it marked as non-volatile and keep stats up to date on it.

I can only assume that IBM must have decided to make those tables volatile based on forcing DB2 to use and index and not on true volatility, though I’ve not had a problem when removing the volatile flag.

The table added in Commerce 7 is staglog. Which if you clean it properly and stagingprop regularly will stay in the same range as far as the number of rows. Smaller implementations may not clean it appropriately and may just have a table that is continually increasing in size. I’m still undecided on whether I like this change or not.