December 03, 2007

Taking Advantage of DB2 9 Takes Little Effort

I’ve been traveling around the country talking to people about the great benefits of upgrading to DB2 9. My presentation with Roger Miller--“Top 10 Reasons To Deploy DB2 9 For z/OS Now!”-- offers more details than I have room for here.

However, if you don't get it from the presentation, I want to emphasize that--in contrast to DB2 8--little to no effort is needed to take advantage of DB2 9 performance improvements.

This week and next, we'll look at some of V9's key features. The features that take no work or effort to implement are covered here; next week we'll look at the features that take some effort to implement.

The features that require no effort on your part to implement are:

  • Utility CPU reduction--This includes improvements in imagecopy, recover index, load, reorg, rebuild index, check index, load partition, runstats index, reorg index and load replace partition with dummy input file. (See slide 18 in the presentation.) As you may have noticed, a lot of these CPU enhancements are related to improvements in index management.
  • Logging--Performance improvements are based on changes in how DB2 defines log reference sequence numbers (LRSN). This has reduced the number of latches on the log and the use of striped archive logging. With early testing in a data sharing environment, the logging rate improved nearly two times. A 10-time reduction was seen with log latch contention.
  • Larger prefetch, write and preformat quantities--Instead of using sequential prefetch for index scans, V9 compatibility mode (CM) uses dynamic prefetch, which is more intelligent and robust. Sequential prefetch is still used for table space scans in V9, and it's been enhanced--larger prefetch quantity and deferred write quantity are used for large buffer pools. The maximum prefetch goes to 256 KB (from 128 KB) with V9 in SQL for table space scan. The size goes to 512 KB (from 256 KB) in utilities. DB2 has increased the number of pre-formated pages to 16 cylinders (from two cylinders) when at least 16 cylinders are being allocated. The changes in index scan have shown up to a 50-percent reduction in elapsed times, while the preformatted pages have reduced elapsed times by up to 47 percent on heavy inserts. And when reading a table space using sequential prefetch, the throughput can increase up to 11.4 percent when reading from DASD and up to 30.9 percent when reading from data cache.
  • LOB performance--Two enhancements can significantly impact overall performance of queries that process LOBs. The first is LOB file reference. Applications can insert LOBs into DB2 tables and write LOBs into files without having to acquire application memory. The second, fetch continue. addresses the problem that programmers had when trying to figure out what size of buffer needs to be allocated. If the buffer isn't large enough to hold the entire LOB or XML column, then DB2 returns the information about which column was truncated and what the actual length is. To enable this on the fetch, the application must add the WITH CONTINUE clause. The application would then allocate enough buffer to hold the columns and issue the FETCH statement with the CONTINUE clause, to retrieve the remaining data for those column(s).
  • Distributed data facility (DDF) virtual storage constraint relief (VSCR)--In V8 DDF ran in 31-bit mode and all virtual storage accessed by DDF was below the bar. The DDF private storage used to maintain communication buffers and control blocks for active and inactive threads was constrained by the 1.5 GB of available address space. In V9 DDF runs in 64-bit mode with all storage use above the bar in the shared memory facility made available in z/OS 1.5. This frees up the system resources being consumed by DB2 DDF and greatly improves the scalability and performance needed with distributed DDF applications.

Note: Some information I’ve used here is from a new IBM Redbook, “DB2 9 for z/OS: Performance Topics” SC24-7473."

Hopefully now you realize that the theme with DB2 9 is performance, performance, performance. More next week.