I have been playing a fair amount lately with clustering indexes and have been rehashing my views on reorging tables on an index. This is still a work in progress, but thought I’d share some details and see if others out there have any thoughts to share with me and others on it.… Read the rest
Continue reading »Tag: SQL
Three Different Ways to Write the Same Join in SQL
This was really a revelation for me when I took DanL‘s SQL workshop before the IDUG conference in Phoenix.
I started out as a physical or systems DBA at IBM, and until studying for certification, I hardly wrote a statement more complicated than select * from syscat.bufferpools… Read the rest
Runstats Issue With Columns Where There is a Long Common Prefix in Character Data
This technote caught my eye when @db2_support tweeted it the other day ago. It was titled as “DB2 might choose a sub-optimal query execution plan due to distribution statistics”. The title alone intrigued me. As I read through, I thought that I needed to do a bit of investigation to better understand the issue.… Read the rest
Continue reading »DB2 Basics: Capitalization
When does case matter in DB2? Well, it doesn’t unless it does. Nice and clear, huh?
When Text Must be in the Correct Case
Text must be in the correct case whenever it is part of a literal string. Things that you have to enclose in single quotes are case sensative.… Read the rest
Continue reading »Redesigning Tablespaces in an Existing Database
Like many applications, WebSphere Commerce puts all tables in USERSPACE1 unless they need larger page sizes. This actually works just fine for smaller and midrange implementations, but we have about one build a year that requires something else – either because of standards that client DBAs adhere to or because they actually are busy enough for I/O and separate buffer pools to matter.… Read the rest
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 was suddenly pegged at 100% CPU utilization.… Read the rest
Continue reading »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 a rather busy database.… Read the rest
Continue reading »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 would be well documented and easy to figure out
I had the first misconception proven wrong pretty immediately.… Read the rest
Continue reading »SQL Tip: Avoid Applying Functions to Table Data
One of the top SQL tips on many lists for developers of SQL is to avoid applying a function to the table data. I think of this as avoiding funcitons on the left of my comparison operator (=, !=, , <, etc) – though that’s a simplification that may not always be correct.… Read the rest
Continue reading »Tips on Using db2advis with Multiple Queries
Many times, the SQL analysis I do is extremely focused – mostly on SQL that is a proven resource hog or a suspected problem. Analyzing all SQL used in a particular process can be a bit different, and may not be something that is frequently done in WebSphere Commerce databases.… Read the rest
Continue reading »Explain Part 2 – Command Line Explain Plans Using db2exfmt
This series started with Explain Part 1 – Explain and the DB2 Optimizer. This is the second post in the series. I want to cover my favorite method for generating Explain Plans, and in the process, discuss some of the elements.… Read the rest
Continue reading »Explain Part 1 – Explain and the DB2 Optimizer
Generating an Explain Plan is easy…
Properly reading an Explain Plan is harder…
Taking appropriate action is harder still.
When I started my career as a physical DBA with IBM Global Services, generating Explain Plans or even general analysis of SQL was not really a part of my job description.… Read the rest
Continue reading »