DBA heroics subdue DB2 performance problems

DB2 LUW DBAs are no different than DBAs who manage other types of databases—they are constantly pulled in many directions at once, and they are asked to keep databases and applications that use those databases running smoothly and tuned.  But unlike DB2 DBAs of the past—and I was one—chances are dwindling fast that DB2 is the only database (or primary database) that they need to be concerned with tuning.

DBAs are increasingly being asked to support additional database platforms.   An Oracle or SQL Server DBA might be asked to watch after DB2 LUW databases, or vice versa.  Or a new hire might need to step in and help on DB2 LUW.

Even for seasoned DB2 pros, the nuances and terminology of the DB2 LUW process model might be new to them if they have previously worked with DB2 on the mainframe.  They won’t necessarily understand all the “dials” that can be turned across the LUW instance or database to make it run better.

The fact is, DB2 LUW provides rich performance metrics that are always ready to query and make use of.  But without a quick, easy way of viewing and understanding those key performance indicators and knowing whether they are “bad” or “good”, performance diagnostics can be a time consuming and frustrating exercise.  Performance management, in general, is likely a low priority until application users or application owners in your organization are screaming.

Who wants that?   Your directors and CIO sure don’t; they know—especially for ecommerce applications—that paying customers may never be heard from when there are application slow-downs or issues.  They simply leave the website and go elsewhere. DBAs can be the heroes in these scenarios—with the right tools.

Speed of diagnostics is essential in Db2 performance tuning

When it comes to diagnosing a performance problem, speed is of the essence.   In a reactive situation, the DBA is being told—perhaps by a monitoring tool, or by an operations team, or by an application user or frantic business unit—that there’s “a problem”.   Fingers get pointed at the database.

What’s the process that a DBA follows in a scenario like that?  Well, first he or she needs to figure out if the “problem” is truly on the database.   Maybe it’s a network problem or a storage infrastructure problem, or something else.  Keep reading to find out what the DBA needs to do to figure out if it is a database problem.

Without a tool how do you know …?

If the DBA team is dependent on running scripts to retrieve performance metrics from DB2 LUW, first the DBA needs to find the right script to run, or one or more queries against DB2 admin views or table functions need to be written.  If scripts do already exist to begin the diagnostic data retrieval, does the DBA understand them – do they know what’s being returned and what each metric really means?

Well, the script/SQL might return a lot of data—all good information from DB2.  The information can be very helpful, once it’s interpreted.

Spotlight® on DB2 identifies bottlenecks

With Spotlight® on DB2, there’s no guesswork.  Open the interface, and immediately the diagram depicting what DB2 LUW is doing “under the hood”—the DB2 process model—shows where the bottlenecks are, if any.   Is it really a DB2 database problem?  If it is, that’s important to know.  The DBA then needs to drill into the problem area (a yellow, orange, or red alarm on the screen) and find out more.   It’s also very important to have immediate answers like, “the problem isn’t on DB2 at all,” when the performance data backs that up.

Figuring out what to tune … quickly

When it comes to fixing the problem that’s been diagnosed, a DBA will hopefully have the time and knowledge to make the underlying cause of it go away.

SQL statements are resource intensive

If it’s something that’s executing against the database that’s causing the problem, results from running queries against DB2 sources like MON_GET_PKG_CACHE_STMT can show the resource intensive SQL statements.  Again, though, we’re in script-running and report-interpretation mode.

Consumable information provides a clear view

Spotlight on DB2 lays out the information so that it’s already consumable.  A clear view through the fog!  Just click on a tab and see the heaviest database resource users.  The faster these statements are found, the faster any possible SQL tuning can take place.

Is the root cause somewhere in the vast DB2 process model?

It’s no surprise that sometimes the root cause of a performance problem is a poorly tuned instance or database.  The DB2 LUW process model abounds with opportunities for the DBA to design the instance and database for top performance initially, but sometimes busy (and cross-platform) DB2 DBAs fail to account for things like table growth, changes to the system, new components of an application, or a sudden jump in the number of users hitting an application.

Below is a sampling of “dials to turn”. With so many choices, it takes a very experienced DB2er to execute and fine tune a DB2 LUW instance and database.

• Agents.  Several instance configuration parameters control connection speed and system resources.

• Prefetchers.  Database configuration parameter and tablespace extent/prefetchsize control read efficiency.

• Package cache.  Database configuration parameter can influence execution speeds.

• Catalog cache.  Database configuration parameter can influence bind (and execution) times.

• Bufferpools.  Database configuration parameters and assignment of objects to particular bufferpools can improve data access efficiency/speed.

• Logs.  Database configuration parameters can influence transaction throughput.

• Sort memory.  Database and instance configuration parameters control unwanted overflows to disk.

• Lock memory.  Database configuration parameters control concurrency.

With a graphical interface, even new DB2-ers can come to the rescue

When your organization’s important applications slow down or stop working, your organization can lose customers.  And money.  Minimize the impact of a DB2 database performance problem by finding the root cause quickly, and getting crucial information to help point you to a resolution.

If you’re not an expert on the DB2 LUW process model yet, you can still diagnose problems if you have a graphical interface that’s showing you immediately where the bottlenecks are happening.  Get the needed data fast and learn what needs tweaking for each particular bottleneck, making small incremental changes to configuration parameters or other control mechanisms.  Resolve the problem and reduce the chances of a recurrence.   If the problem persists, no guessing of where to spot it—Spotlight will show you right away.

Toad DBA Suite for IBM DB2 LUW in an integrated solution for DB2 LUW database administration. Spotlight on DB2 is just one of a few tools that comprise this solution.

Questions?

Start a discussion about this blog in the Toad World® forum, Toad for IBM DB2 . 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!