Recently we've been analyzing which of the DB2 LUW Performance Blogs are the most popular. This presents us with an opportunity to weed out the clutter (news and announcements) and organize the most helpful blogs into a single indexed resource for your convenience. Here's most everything you need to know about DB2 LUW monitoring and tuning...

---

Opening Remarks

We used to assert that the 80/20 rule applied to physical design versus parameter and memory tuning. With many memory related configuration parameters becoming "automatic", and with the help of the configuration advisor, the ratio has now shifted towards 90/10 with 90% of your tuning benefits coming from workload analysis and physical design tuning. In light of this, we'll focus on workload analysis and physical design first, then highlight relevant database and monitoring aspects later.

DB2 LUW Workload Performance Analysis

Where is the pain? What makes a database suffer? Does a performance issue belong to the database, or not? What is the SQL that is hurting the database performance the most? We have answers to these important questions in these very popular blog posts.

• The DNA Test of Performance Accountability. Before we tear our hair out trying to solve a performance problem, wouldn't it be wise to know, first, if the performance problem belongs to the database? You can't fix it if you don't own it!
  • If the calculations described above are too daunting for you, either get DBI's Brother-Thoroughbred™ or check out these simpler sanity checks in blog DB2 is ALIVE and WELL and IT'S NOT YOUR FAULT!
• Table Read I/O and Overflows. Note: In DB2 V10.5, there is a new Online REORG option that will cleanup overflow problems.
• Once you know which tables are causing the database the most pain, next you should perform an aggregated cost analysis of the SQL that is contributing I/O to your most highly read tables. See Identifying Mosquito Swarms - SQL Workload Cost Analysis.
  • Mosquito Swarms? This is analogous to "death by a thousand cuts" and in stark contrast to "Elephant Hunting". Are people screaming What's Happening Right NOW!?!?!? Happy Elephant Hunting to you - but, just remember, what is happening right now may never happen again. And, whatever is happening right now might distract you from real, root cause problems.

DB2 LUW Physical Design / Index Solutions

You've determined that opportunity for improvement in a database genuinely exists, you've identified some tables that are causing the database pain, and you've isolated some costly SQL and reviewed Explains. We have many blogs and resources on indexing for optimized performance.

DB2 LUW INDEXES

• Index Design Tips 1
• Index Cardinality. While this blog suggests that index FULLKEYCARD should be at least 60-75% of a table's cardinality, improvements in DB2 and practical experiences more recently suggest that Index FULLKEYCARD should be at least 5-10% of a table's cardinality. At 5%, there are 20 index RIDs for each table row.
• DB2 LUW Index Design Best Practices! This is an awesome blog!        
  • If video is more your thing, then watch a replay of a popular DBI webinar on DB2 LUW Index Design Best Practices. This video has been viewed over 20,000 times!
• DB2 LUW Performance: Clustering Indexes

DB2 LUW Table Physical Design

• DB2 LUW Performance: a VOLATILE TABLE topic
  • The volatile table blog got some volatile feedback: DB2 LUW Performance: a VOLATILE TABLE topic - Part 2
• Tuning PCTFREE: Joe the DBA and the lost art of PCTFREE
• In addition to index design and marking a table volatile, DBAs should also consider Multi-Dimensional Clustering tables, Automatic Summary Tables, pureScale, keeping statistics up to date, and, of course, the possibility that new DB2 V10.5 BLU Column Organized tables might be helpful. We have several recorded episodes of The DB2Night Show™ on these topics. Check out the DB2 LUW REPLAYS for more information.

DB2 LUW Database Performance Monitoring and Tuning

Presuming you've followed the prescriptions above for identifying, isolating, and curing the most adverse and costly database workload problems, it is now appropriate to consider fine tuning I/O and memory configurations. Also, by taking some top level database performance measurements, you can get a good understanding of database health and fitness overall.

DB2 Database Performance KPIs and Tuning

• What kind of database do you have? Do you THINK you have? DB2 LUW Performance: Average Result Set Size (ARSS)
• Is the database retrieving rows efficiently? Examine Database Index Read Efficiency (IREF)
• Are indexes being used effectively, or is the database doing too much scanning? Examine the Synchronous Read Percent (SRP)
• Check out this series of blogs on measuring key costs:
  • DB2 LUW Performance: Key Cost Measures
  • DB2 LUW Performance: More Key Costs
  • DB2 LUW Performance: The MOST Important Cost Measurement
  • DB2 LUW Performance: The MOST Important Cost Measurement - BPLRTX - Q&A
  • Detecting Index Leaf Page Scans
    • In DB2 LUW V10.1, IBM introduced Index Jump Scans. Jump scans are supposed to help mitigate the high costs of leaf scans by using imperfect indexes more efficiently. One of DBI's customers upgraded from DB2 9.7 to V10.1 for their e-commerce database. Out of a workload of over 4,267 distinct SQL statement patterns, 13 of these SQL statements benefitted from Index Jump Scans. Check out The DB2Night Show Episode #112 REPLAY on "DB2 LUW 10 Index Jump Scans & Index Best Practices" (this replay was the top downloaded replay of 2013 with over 33,000 downloads), and check out The DB2Night Show Episode #118 REPLAY to learn more about this customer's V9.7 to V10.1 upgrade experience.
• Of course, no discussion on database performance monitoring would be complete without mentioning DB2 Bufferpool Performance. After you read this very popular blog on DB2 Bufferpool Hit Ratios and Folly, bear in mind that you can only hide so many problems behind massive bufferpool memory before CPU saturation will occur and your database, and its applications, will fall on their faces. Folks, we run into this very frequently. DBI's performance methodology and patented tools will help you find costly memory scans that you are not aware of!
• On the subject of Bufferpools, one important, but often overlooked, performance tuning technique involves using the parameter NUMBLOCKPAGES. Read DB2 Bufferpool NUMBLOCKPAGES and Asynchronous Pages Read Per Request (APPR) .
• By some miracle, in our experience, very few DB2 LUW databases have problems with Catalog Cache performance. Still, it doesn't hurt to check yours.
• Measuring Read I/O Performance: I/O Read Times (ORMS)
• Measuring Write I/O Performance: I/O Write Times (OWMS)
• Measuring Direct I/O Performance: Direct I/O Times
• Write I/O Performance Optimization:
  • Write I/O Optimization - Part 1
  • Write I/O Optimization - Part 2
  • Tuning LOGBUFSZ. Blog also includes a terrific recipe for baked salmon! With DB2 9.7 and higher, a new isolation level Currently Committed also benefits from increased LOGBUFSZ values. Pay attention to the Log Read Hit Ratio (LGRHR) to make sure your LOGBUFSZ is optimally set.
  • We meet a lot of DB2 customers that want their LOAD and ETL jobs to run faster. Index Design is very important, particularly ensuring that you don't have any indexes with low FULLKEYCARD ratios. See index topics above.
• Measuring DB2 Sort Performance KPIs including Costs, Times, and Transaction Times. See Sorts - The silent performance killer.
• DB2 LUW Performance: Fighting Over Data - LOCKS and MORE on LOCKS.
  • PS - DBI customers don't have problems with locks because all their SQL runs so fast and efficiently! Just had to put that out there.

Miscellaneous and Popular Topics

If you've made it this far, congratulations! However, there's more you should be aware of.

• DB2 LUW Health and Fitness Monitoring
• One of our favorites: DB2 LUW Performance: Are you REALLY ready for Production?
  • Related: DB2 LUW Performance: Following my own Advice and Thankful
• A splash of humor: Orickle Announces 13G - Promises DB3 LUW Enablement
• Using the vi (Very Intuitive) editor - Part 1
• Using the vi (Very Intuitive) editor - Part 2

Have you found this blog compilation helpful?

See the "Mail this" link at the bottom? Tell your friends. Better yet, give a shout out to your followers on twitter:
Tweet

DBI Top News Releases

A compilation of our most popular, most helpful, DB2 LUW Performance blogs wouldn't feel complete without highlighting some of our favorite news announcements.

• Hewlett-Packard Company and DBI Software announce new partnership
• DBI Software announces new IBM DB2 LUW performance tool releases
• BOLT System Gains Increased DB2 Performance, Efficiency, and Improved SLAs
• Breaking News: PartsSource selects DBI DB2 tools, cuts I/O by over 75%
• DBI DB2® solutions deliver increased database performance and efficiency to Wasserstrom E-Commerce and SAP systems
• DBI helps SafeAuto Insurance improve customer-facing DB2 database performance and reduce IT costs
• DBI Software and Triton Consulting expand partnership globally for the benefit of worldwide IBM DB2 LUW customers
• Full list of DBI News Releases

Closing Remarks

We hope you find this resource of DB2 LUW Performance Blogs to be helpful in your monitoring and tuning endeavors. No company is more committed to helping to grow the DB2 LUW community than DBI Software. As the saying goes with Syms Clothing on the USA East Coast, "A well educated consumer is our best customer!" Or, as we like to think, "People don't know what tools they need to buy until they know what they need to know." DBI offers "Smarter Tools for Smarter People."

If you have any questions or comments, or if you would like to see what you're missing and experience 10X ROI, kindly Contact DBI. We're here to help!