The DB CFG parameter LOGBUFSZ cannot be changed online, and it is not a participant in DB2 9 autonomic STMM memory tuning. The autoconfigure command will make an earnest effort to provide a tuning suggestion for LOGBUFSZ (which is allocated from DBHEAP memory), but you will want to take autoconfigure's suggested values with a grain of salt and additionally apply the guidelines that follow. After making any changes to the LOGBUFSZ with the UPDATE DB CFG command, you will need to stop and restart the database for the changed value to take effect.

Guidelines:

I generally recommended a minimum LOGBUFSZ of 128 4K pages. If your database executes a large amount of INSERT, UPDATE, and DELETE SQL (collectively "DML") per transaction, you might consider increasing LOGBUFSZ to 256-512 4K pages. By doing so, most clients observe measurable performance increases in transaction throughput.

The Log Read Hit Ratio (LGRHR)

LGRHR = 100 – ((Number read log IOs * 100 ) / Log pages read)

For input to this formula, use a Database Snapshot as found in your preparation file "dbsnap2.txt".

Generally, if LGRHR is less than 98%, then you should consider increasing the size of LOGBUFSZ further. DB2 will read its recovery logs (via the buffer as much as possible) to facilitate completion of ROLLBACK commands. The objective here, for this measurement, is to facilitate achieving a very high percentage of log reads from LOGBUFSZ memory, thus improving response times for ROLLBACKS.

While ROLLBACK performance may be important, the big win with LOGBUFSZ comes from being able to do a single write from LOGBUFSZ memory to disk to facilitate COMMIT processing. When the LOGBUFSZ memory is too small, DB2 may have to do multiple physical write I/Os within the scope of a single transaction to the performance detriment of response times and throughput.

INDEX PHYSICAL DESIGN TECHNIQUES

That headline probably woke a few of you up - for several weeks now many of the blog posts have discussed key cost and ratio measurements to help you identify and quantify the existence of, or need for, physical design improvements. Well, here we go, we're going to get into this topic via a new DBI Webinar "DB2 LUW Index Physical Design & DBI Performance Solutions: Your Roadmap to Becoming a Performance Hero" on September 25th at 3pm CDT.

The Shameless Marketing Moment

Don't wait until 2008 for the blog posts on index physical design. Get a jump start today, complete with visual aids, graphs, charts, and discussion, by attending DBI's Webinar on DB2 LUW Index Physical Design: Register HERE for the 2007-09-25-15.00.00.000000 CDT Webinar. One lucky participant will be randomly selected to win a $50.00 Amazon.com gift certificate.

Just for Fun

As a kid, there were a lot of things I didn't like to eat: mushrooms, bell peppers, liver, and fish were at the top of the nasty list. Well, I suppose tastes change as one becomes older and wiser. I now enjoy mushrooms, bell peppers, and fish. Salmon, which I've read is supposed to be very good for you, was particularly difficult to develop a liking for until I learned this recipe:

Oven: 475 degrees
Prep: Rinse your salmon fillet with cold water, then pat dry with paper towel. Place your fish skin/scale side down on a baking tray or dish. Generously apply a coating of Grey Poupon mustard, then sprinkle with tarragon, basil, and thyme spices. If you like your food spicy, you can also sprinkle on some cayenne pepper.

Bake: About 20-25 minutes (assumes your salmon fish fillet is normal thickness)

Serve: Hot out of the oven with rice (which also takes about 25 minutes to cook), steamed broccoli (5 minutes of steam), and a side of sliced bell pepper medley (get green, yellow, red, and orange peppers for optimum color) and mushrooms sauteed with 2 tablespoons extra virgin olive oil.

Until next time,
Cheers,

Scott

Scott Hayes
President & CEO, DBI
www.Database-Performance.info
www.Database-Auditing.info