Category: Performance

3

The Danger of FETCH FIRST ROW ONLY

There are a a vast array of things you can do in SQL. But just because you can doesn’t always mean that you should. I recently ran into an interesting situation with FETCH FIRST ROW ONLY. How the Problem Manifested The day after new code was deployed, a production database system that usually runs with 40% CPU utilization or less...

0

Activities vs. Requests

This started as a conversation with a couple of DBA friends when we were playing with MONREPORT.DBSUMMARY. That’s a pretty nifty way to get a quick look at a number of performance parameters, AND to limit the time period covered by the report. But the thing we noticed and were talking about is the difference between activities and requests. In...

0

Care and Feeding of Statistical Views

The whole point of statistical views is to improve the performance of SQL that joins tables. Or to help you collect statistics on expressions – though if you’ve got 10.5, consider expression-based indexes for that instead. Giving the DB2 Optimizer more data is generally a good thing for query performance. If you don’t keep runstats current on your statistical views,...

5

Using a Statistical View to Improve Query Performance

Sometimes just using the index advisor and a bit of common sense, you can address SQL performance issues. But other times, you have to dig a bit deeper. I recently found the following query and 300 of its closest cousins (differing only in the length of the second in-list) were eating 30% of the CPU time used by queries on...

11

Using an Event Monitor to Capture Statements with Values for Parameter Markers

Recently, I needed to do some SQL profiling – discovering exactly what SQL a web application was executing against the database. I learned a lot, and when I learn a lot, I try to share with my readers. I went into this exercise with two major misconceptions: A statement event monitor would collect parameter marker values Event monitor table output...

31

Transaction Logging Details

Transaction log files are one of the core strengths of a DBMS, if you ask me. The ‘D’ in ACID refers to ‘Durability’. What Durability means is that once a transaction is committed, it is not lost, even if the database crashes or the server it is on has to be restarted. Transaction Logging in DB2 DB2 writes transactions to...