2013 Dec 20 Update- Winning Ad: #3, Zero to Hero - Survey Winner: Barry Leb, CNN

DBI pureFeat™ Performance Management Suite for IBM DB2 LUW was just honored by DBTA as a Top 100 Trending Product for 2014. No doubt, DBI offers great tools! However, marketing is not our specialty! Take a short 5 question survey and help us choose the best banner ad for web advertising! Please!

[ more .. ]

Bookmark http://www.DBISoftware.com/idugna13.php to view #IDUG or #IDUGNA tweets for the latest news, information, and gossip on the IDUG DB2 Tech Conference in Orlando. Check it out!

In 2010, the "DB2 LUW Performance Analysis and Tuning Workshop" drew top attendance numbers in North America, Australia, and EMEA. In 2011, this IDUG Ed Seminar has been updated and is being offered again as session 1900. Click Here for Details.

In this full day class, you will receive over three dozen SQL Snapshot commands to help analyze YOUR database. Together, we will learn about what to measure, we will discover the problems and "opportunities for improvement" in your database, we will discuss solutions, and you will return to your office fully equipped to make measurable performance improvements.

This blog post contains the specific instructions that you should use to prepare for this workshop...

[ more .. ]

My dad died June 10, 2011, around 1:45pm CDT. I learned of this via text message at the Minneapolis St. Paul airport. While this may not seem relevant to "DB2" at the moment, my dad made many contributions to our DB2 LUW community. This blog is a toast to my dad, your dad, and anyone out there who has a dad. I wrote and delivered this eulogy on June 16th in Harrisburg, PA. Several people asked for copies of the handouts, so, okay, I've shared (grin). Fathers are special people...

[ more .. ]

What's all this fluffy cloud stuff about? Finally someone is explaining it in a way that makes sense to technical humans!

The DB2Night Show™ hosted special guest Mark Wilding, IBM STSM & DB2 Cloud Architect, in episode #26, and replays are available for your enjoyment and education!.

This Friday, 27 August 2010, Mark Wilding joins us again to offer "Part 2" of the technical details - a deeper dive into what this fluffy stuff is about, how it works, and tips and considerations for your success. Registration links are available on www.DB2NightShow.com

Amazingly, whether you realize it or not, your DB2 is already in the Cloud - along a journey of tiers that offer increasing cost savings. So, don't miss episodes #26 and #27!

The DB2Night Show™ is sponsored in part by IDUG, FREE DB2, and www.DBISoftware.com.

Cheers, your host,
Scott Hayes

[ more .. ]

In the wake of recent claims by Larry E, I'm joining Craig Mullins and re-cycling a former blog post with updates...

29 Jan, 2010 - Redrock Shores, CA. In late breaking news today, Orickle announced preliminary plans for the next version of its world dominating database 13G. Version 13G will include new features such as DB3 LUW Enablement and nano compression. A senior Orickle executive who requested anonymity said "We skipped 12 because 13 is a lucky number, and 'G' now stands for Global domination. We lost some serious market share to DB3 9.7 but we believe we can win global database dominance back with our superior marketing, sales, and negotiation tactics." Continued...

[ more .. ]

IDUG is a great conference for learning from other users, consultants, and top shelf IBM people. It is also a great place to meet with vendors to discover the latest products and services that are available. DBI and our Italian partner Expertise4IT look forward to meeting you in the exhibit hall.

And just in case you would like to invite some of your associates or executives who are unable to attend the full conference, just click THIS LINK and print your FREE pass!

Also, have you signed up yet for my Ed Seminar "300 Fast Fabulous DB2 Performance Facts"? The SQL Snapshots I'll be sharing with attendees are worth the price of the class alone!

Cheers, Scott

Keep your skills sharp and your databases optimized - attend IDUG Europe in Rome to advance your career, learn the latest DB2 tips, tricks, and techniques, and improve your business performance. Visit the IDUG Europe web site for details! And be sure to sign up for my Ed Seminar "300 Fast Fabulous DB2 Facts and Performance Tips"!

[ more .. ]

Hello DB2 LUW Performance fans,

Just a quick note to let you know you can save $100 off your IBM IOD 2009 Conference registration by using Promotion Code G09BOND. This promo code is valid for a long as IOD registrations are open and available.

[ more .. ]

First, my apologies for being away from the blog keyboard for so long. Kim Moutsos actually contacted me to see if I was still alive. Truth be known, my grandmother died, my father is in the hospital battling cancer, and I've been traveling the US States quite a bit helping companies save millions in software and hardware costs. Nonetheless, here's a quickie on some lock formulas and other updates. The good news is, I suppose, I'm accumulating a great deal of new material to share with you in future posts.

[ more .. ]

Every once in a while I hear a DBA say they are having Lock problems. Since read-only or read-mostly Data Warehouse databases rarely have lock problems, I quickly assume they have an OLTP database. It is my opinion that locks are rarely, if ever, a PROBLEM. Locks are a SYMPTOM of another very real problem.

[ more .. ]

In the prior blog post, we learned how to determine if your database is CPU bound, lock bound, sort bound, or I/O bound, and how to determine if a performance problem is attributable to the database or not. We will now turn our attention to statement analysis methodologies so that we can discover the sources of bottlenecks. "Statements" is broadly defined to include both classic SQL and newer XML queries.

[ more .. ]

In one of the earlier blog posts "DB2 LUW Performance: Key Cost Measures", we introduced the number of sorts per transaction (SRTTX). In a more recent post "DB2 LUW Performance: The Most Important Cost", we looked at the importance of measuring Bufferpool Logical Reads per Transaction (BPLRTX). If performing excessive and unnecessary logical I/O is the number one performance killer for a database (and it usually is), then performing excessive and unnecessary sorts is the number two performance killer in most databases.

[ more .. ]

If your database updates its data via Inserts, Updates, Deletes, Imports, or Loads, then this blog post is for you. Write times tend to be slower than read times, and synchronous writes can be particularly painful. When tuning your databases, it is desirable to achieve a high percentage of Asynchronous writes as this type of write is faster. We need to learn the average write time for the database overall, and write times for each tablespace.

[ more .. ]

In the next few blog posts, we'll take a look at formulas for time metrics so that we can understand "where the time goes" and uncover bottlenecks. Since both OLTP and Data Warehouse databases perform a great deal of I/O read activity, we'll begin by looking at metrics for computing important read times.

[ more .. ]

It is an unfortunate reality that the database and the DBA are too often presumed guilty by default. Everyone tends to want to blame the database first, even though performance degradation could be caused by network problems, storage problems, the Web server, sun spots, or poor application coding. So, as a database professional, how do you get yourself out of the hot seat and prove your database's innocence? Here's a checklist to assemble your defense:

[ more .. ]

During a recent Webinar, and reiterated by attendees at this weeks IDUG Conference in Athens Greece, we've heard that very few organizations are taking advantage of DB2 9 autonomic tuning as implemented by the Self Tuning Memory Manager, or STMM. WHY? Because they don't trust it.

As with relationships between people, building trust takes time and requires a series of reliable and favorable experiences. In this blog post, we'll consider when and how to best engage STMM, and discuss how to build trust with this new DB2 9 capability.

[ more .. ]

Another DB2 magazine blog reader, Geoff, read the recent post "DB2 LUW Performance: Asked and Answered (BPLRTX)" and inquired about the optimum setting for NUMBLOCKPAGES. Let's take a closer look...

[ more .. ]

In DBI's Webinar "DB2 LUW Index Physical Design & DBI Performance Solutions: Your Roadmap to Becoming a Performance Hero", we discuss some important index physical design guidelines and techniques (the next Webinar is October 30th @ 10:30am CDT). Ideally, the FULLKEYCARD cardinality of an index should be at least 75% of the table's cardinality. Here is a sample SQL query that can help you do a quick cardinality check on your indexes...

[ more .. ]

I want to thank Brian Stewart, Greg Marino, and Marco Bartolli for their comments and questions posted in response to DB2 LUW Performance: The Most Important Cost. These comments include some very good questions that I'll attempt to answer in this blog post.

[ more .. ]

The Catalog Cache is like a special memory bufferpool dedicated to catalog objects; it stores information about tables, indexes, views, and other objects to speed up the BIND process for dynamic and static SQL. It CAN be changed online dynamically, but it DOES NOT participate in DB2 9 Autonomic Tuning - SO, you'll have to tune this one yourself. Here's how...

[ more .. ]

We've looked at several metrics that can help you discover the presence of physical design "opportunities for improvement", or problems. But, as many of you know, I don't like the word "problems" - 1) Problems is too negative sounding, and 2) Sometimes inefficiencies, or tuning opportunities, aren't severe enough to merit the label "problem". "Status Quo" operations for many databases may include many inefficiencies but the performance delivered is "good enough" - well, that is, it's "good enough" until the next hardware upgrade cost comes due or the application falls over and dies when 20 more users are added.

[ more .. ]

The database configuration parameter LOGBUFSZ controls the amount of memory that DB2 uses to buffer I/O to its recovery log files. The default size of 8 4K pages is grossly to small for most databases. This blog post introduces a new metric "Buffer Log Read hit Ratio" and offers tuning suggestions for Performance Heroes.

[ more .. ]

It's difficult to talk about DB2 performance and not have the subject of Bufferpool Hit Ratios come up. It's as if high bufferpool hit ratios are somehow capable of saving the planet from global warming. Yes, bufferpool performance is relevant, but we need to evaluate these with a dose of reality. Performance Heroes will spend much more time on workload analysis and physical design than twiddling memory bits.

[ more .. ]

In this post, I'll attempt to summarize the key metrics we've discussed so far and provide links back to the original posts for your reference. Database Files Closed will also be discussed. In upcoming posts, we'll look at time measurements, important ratios, workload analysis, and physical design techniques to reduce costs and improve performance. It would please me greatly for all of you to become Performance Heroes in your organizations.

[ more .. ]

Let's take a look at a cost measurement that can help you detect the presence of Index Leaf Page scans, BPLITX. While perhaps not as costly as data page scans, index leaf page scans can quickly suck your CPUs dry of processing capacity and rob your organization of performance that it would otherwise be entitled to. Performance Heroes will be successful at reducing the cost of both data and leaf page scans.

[ more .. ]

In this blog post, I will describe the most important cost metric that you MUST measure, and work to improve, to become a Performance Hero in your organization. This cost metrics is "Bufferpool Logical Reads per Transaction (BPLRTX)".

To compute this metric, use Database, Bufferpool, and Tablespace snapshots as described by the preparation instructions.

[ more .. ]

As described in my prior blog post, understanding workload costs is critical to successful database tuning. Performance Heroes diligently work to reduce costs of processing statement workloads. Here are two more important cost metrics.

[ more .. ]

The secret to successful database performance tuning and optimization requires an intimate understanding of workload costs. You can become a Performance Hero in your organization by determining current workload costs, and then making physical design and configuration changes to lower transaction costs - or the costs of doing business in the database.

Too often we find database people being excessively obsessed with rates, or chasing individual queries. Rates can vary day by day depending on the day of the week, the time of the month, or the hour of the day. Successful Performance Heroes will focus on reducing transaction costs to improve efficiency. Be a hero!

Cost Measurements:

[ more .. ]

Besides IREF, another key indicator of a database's health and efficiency is the Synchronous Read Percentage, or SRP. When DB2 has good indexes available to retrieve rows for result sets, it will use synchronous I/O to access precisely just the index and data pages required. When indexes are missing, or the physical design is otherwise sub-optimal, DB2 will resort to using asynchronous prefetch I/O to scan index or data pages. Scans are "evil" (a word borrowed from my teenage daughter) and should be avoided as much as possible, especially for OLTP databases.

The Synchronous Read Percentage (SRP) metric:

[ more .. ]