Category: SQL

3

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. select * from table where col1=’text’ is different from...

9

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

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

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

12

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

2

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. Recently, I had the output of statement event monitors while...

8

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. As I learned more about performance tuning and the limitiations...

2

SQL Tip: COALESCE

I haven’t generally been known for my SQL tips. I can find my way around SQL decently enough, but for years, I didn’t do much of it. I’ve used it more and more as the years have gone by as I have moved out of a stictly physical/system DBA role and into a more mixed role where I interact and...