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

Continue reading »

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

  1. A statement event monitor would collect parameter marker values
  2. 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: 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 collaborate with our development team.… Read the rest

Continue reading »