Team with the best
Db2® LUW Performance Tools
company in the World

DB2 LUW Performance Blogs Have a New Home

Rumor has it that www.ibmdatabasemag.com will be going off the air on 30 April 2009. We are sad to see this valuable resource to the IBM Database community go.

Hundreds of database professionals from around the world have driven thousands and thousands of hits to the DB2 LUW Performance blog series. The good news is that we have preserved this valuable content and reference material on our site for your continued reference and benefit. You can find the blogs at:

www.dbisoftware.com/blog/db2_performance.php

Thank you,
Scott

by Scott in

DB2 LUW Smarter Planet IT Cost Optimization & Saving Your Job

It was about a year ago that a DBA in Sydney Australia asked me if I knew of a way to correlate database tuning efforts to Energy savings. IBM's Smarter Planet campaign gave me renewed inspiration to research this important topic, for the fourth pillar of Smarter Planet involves seeking new ways to improve efficiency and lower costs of existing processes. What you are about to learn might be a real eye opener.

[ more .. ]
by Scott in DB2 Performance Best Practices

DB2 LUW Health and Fitness Monitoring

I pushed my father in a wheel chair for the first time last month. As I looked at the man who was my Scout Master, who helped me achieve Eagle rank, who taught me to bowl, and inspired me with strong core values of helpfulness and community service, these wheel chair excursions marked a pivotal inspiration point for contemplating my own current health, and have given rise to my own self-improvement goals that you can read about in my personal blog. This year's new IDUG presentations and Ed Seminars will also include new information on DB2 Health monitoring. So, in this blog post, let's look into the health and fitness of your databases.

[ more .. ]
by Scott in DB2 Performance Best Practices

DB2 LUW Performance: A Self Tuning Memory Manager Case Study

I was recently contacted by a DBA at a large insurance company. After upgrading from DB2 V8.2 to 9.1.0.3, the DBA activated the Self Tuning Memory Manager (STMM) in the hope that it would solve all performance woes. To the contrary, performance got worse, and the DBA noted a marked increase in SORT overflows. He even opened a PMR with IBM. But this isn't a case of STMM gone crazy; something else is going on, but what? Let's take a look at the numbers and discover the truth.

[ more .. ]
by Scott in DB2 Case Studies

DB2 LUW Performance: An IDUG Ed Seminar Case Study

Congratulations to the IDUG Europe Conference Planning Committee (CPC)! IDUG Europe 2008 was one of the best IDUG conferences ever! On 17 October 2008, I taught the one day Ed Seminar "DB2 LUW Performance Diagnosis Learning Lab." This post contains a review of one of the participant's data and findings. The data comes from a busy production database that runs a banking application.

[ more .. ]
by Scott in DB2 Case Studies

DB2 LUW Performance: Identifying Mosquito Swarms

Congratulations on hunting your elephants. The prior blog post was the most popular ever; it has received well over 3,000 hits and continues to ascend in popularity. I readily concede that elephants can wreak havoc in your databases, but there exists a much more dangerous predator of your database's performance: mosquitoes.

Mosquitoes are, on an individual basis, small, inexpensive statements with an apparently low timeron cost, but, when they are executed with high frequency, they will suck the life out of your system and degrade the performance of your business. Two blog posts ago, the topic of statement workload analysis was introduced. If you have not read it yet, please do so now.

[ more .. ]

by Scott in DB2 Performance How-To

DB2 LUW Performance: Let the Elephant Hunt Begin

Hopefully you read the prior blog post on elephants and mosquitoes which discusses different approaches to statement performance analysis. If you haven't read it yet, please do so now. In this post, we will look at ways to hunt and kill your elephants. And, by no means do I favor cruelty to animals in any way, this is just a metaphor. Let us imagine that your phone just rang and your boss is screaming "What's happening RIGHT NOW?!?!?!!?!?" ...

[ more .. ]
by Scott in DB2 Performance How-To

DB2 LUW Performance: Write I/O Optimization Part 2

It's said that there is more than one way to skin a cat, meaning there are multiple ways to accomplish the same objective. As for the saying, I don't think this is very kind to cats even though I prefer dogs. The prior blog post discussed making adjustments to CHNGPGS_THRESH to reduce, avoid, or mitigate transient "brown outs" in transaction throughput. There is another way...

[ more .. ]
by Scott in DB2 Performance How-To

DB2 LUW Performance: Write I/O Optimization

A student who took this class last week at WDUG emailed me the day after to thank me. He said he enjoyed the class and successfully reduced the elapsed time of a troublesome query from two hours to three minutes! If you missed the WDUG session, I hope you will be able to join my Ed Seminar at IDUG "DB2 LUW Performance Diagnosis Learning Lab". This post covers topics related to optimizing Write I/O performance.

[ more .. ]
by Scott in DB2 Performance How-To

DB2 LUW Performance: Table Read I/O and Overflows

Understanding Table I/O performance is critically important to properly diagnosing the health and efficiency of a database and pinpointing problems. In fact, if I only had just a few minutes to quickly assess a database, I'd look at 3 key measurements...

[ more .. ]
by Scott in DB2 Performance Metrics

DB2 LUW Performance: Direct I/O Times

Returning our attention to the question "Where does the time go?", we need to look at Direct I/O times. Direct I/O is I/O that occurs directly to disk without an intermediate visit or presence in the Bufferpools. Direct I/O is used by DB2 in support of LONG and LOB objects. Even if you think you are not using LONG and LOB objects, you are implicitly using them as these data types are found throughout the DB2 catalog.

[ more .. ]
by Scott in DB2 Performance Metrics

DB2 LUW Performance: More on Locks

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 .. ]
by Scott in General

DB2 LUW Performance: Fighting Over Data - LOCKS

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 .. ]
by Scott in General

Statement Analysis Intro - Hunting Elephants and Mosquitoes

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 .. ]
by Scott in General

DB2 LUW Performance: Sorts - The silent performance killer

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 .. ]
by Scott in General

DB2 LUW Performance: The DNA Test of Performance Accountability

The database is often presumed guilty if there is a performance issue. But your database seems fine; all the symptoms of good performance are present, so now how do you prove the innocence of your database?

In the prior blog post, I provided a checklist of some metrics that would help you assemble your defense if it was asserted that your database was the source of performance problems. But those ratios and indicators are just circumstantial evidence of probable innocence. Here comes the DNA test. It's hard. It's time consuming. It's complex. But the analytical effort just might help get you out of the hot seat and properly direct a performance issue to application or networking teams...

[ more .. ]

by Scott in DB2 Performance How-To

DB2 LUW Performance: I/O Write Times (OWMS)

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 .. ]
by Scott in General

DB2 LUW Performance: I/O Read Times (ORMS)

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 .. ]
by Scott in General

DB2 LUW Performance: DB2 is ALIVE and WELL and IT'S NOT YOUR FAULT!

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 .. ]
by Scott in General

DB2 LUW Performance: Building Trust with DB2 9 Autonomic Tuning

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 .. ]

by Scott in General

DB2 LUW Performance: NUMBLOCKPAGES and APPR

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 .. ]
by Scott in General

DB2 LUW Performance: Index Cardinality

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 .. ]
by Scott in General

DB2 LUW Performance: Asked and Answered (BPLRTX)

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 .. ]
by Scott in General

DB2 LUW Performance: Catalog Cache

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 .. ]
by Scott in General

DB2 LUW Performance: Index Design Tips 1

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 .. ]
by Scott in General

DB2 LUW Performance: Tuning LOGBUFSZ

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 .. ]
by Scott in General

DB2 LUW Performance: Bufferpool Hit Ratios and Folly

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 .. ]
by Scott in General

DB2 LUW Performance: Progress Review plus Closing Files

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 .. ]
by Scott in General

DB2 LUW Performance: Detecting Index Leaf Page Scans

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 .. ]
by Scott in General

DB2 LUW Performance: The Most Important Cost

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 .. ]

by Scott in General
Page :  1 2