We are in the process of refreshing free DB2 Express-C to the new level i.e. v9.5.2. There are lots of enhancements that you will get as a result of this refresh. Customers that had DB2 Express-C subscription did have these enhancements delivered as part of FixPack 1 and FixPack 2. Now those of you that are using the free version of DB2 Express-C can also take advantage of all the goodness that the latest version of DB2 Express-C has to offer.
This post is about a very small but sometimes very useful new feature in v9.5.2. At one point in time I heard Bruce Lindsay describe DBMS as basically a combination of disk storage, transaction log and a bufferpool. I know I am not doing justice to Bruce’s description here but that is all I can do in this one liner. Bruce actually made a point that it really is the transaction log that really is “the database”. So, if transaction log is the database, buffer pool is the accelerator pedal of the database. Basically, buffer pool is an in-memory write through cache that sits in front of the typically slow (compared to memory) disk subsystem. Bufferpool allows applications to work with frequently used data in main memory rather than  always going to disk. As a result, bufferpools allow databases to minimize disk I/O and, as a result have a potential to make databases go fast … really fast. So, just as when doing code performance optimization programmers try to optimize processor cache hit ratios, DBAs tuning databases spend considerable effort on tuning bufferpool hit ratios i.e. they try to maximize the proportion of time data is found in the bufferpool so physical I/O can be avoided. And when you do something like that you may want to take a base line measurement when the bufferpool(s) is empty i.e. you may have a need to clear the bufferpool. The bufferpool is empty when the database just starts and before any queries were run against it. So, one can run DB2STOP command followed by DB2START to restart the database and have empty bufferpools. The latest version (9.5.2) of DB2 Express-C has another command that will help you clear the bufferpool(s) without restarting the database:

db2pdcfg -db <dbname> -flushbp

It is a small thing, but sometimes the smallest things make a difference between a joy and a chore.

If you enjoyed this post, then make sure you subscribe to my RSS Feed.

Tagged with →