Category: SQL

sqlplus 1

Playing With CLPPLUS

I’ve played with the clpplus at least once before, but have generally thought of it as a tool created to satisfy those coming from Oracle and looking for Oracle-like features. One of the features I actually liked about Oracle in the class and certification tests that I took for it was the ability to specify values for an SQL statement...

IanB 1

Keeping EXPLAIN under control

Have you ever looked at a list of tables in your database, only to see the set of EXPLAIN tables present in two, five or even 10 different developers’ schemas? I don’t know about you, but this drives me crazy. Very few of the developers that I know pay attention to these tables, and many of them are not even...

select2 1

DB2 Administrative SQL Cookbook: Looking at How Current Statistics Are

Current statistics are vital to performance. If I’m encountering a database I haven’t regularly been supporting, and don’t know the maintenance plans and schedules, I frequently query to get an idea of how current they are. Purpose To report the dates of statistics collection and the number of tables statistics were collected for on each date. Usually a small list...

select2 2

DB2 Administrative SQL Cookbook: Finding Problem SQL in the Package Cache

I wrote a developerWorks article on finding problem SQL in your package cache. But I refine and play with my SQL over time, so I thought I’d share the version I’ve been using recently. Purpose To find problem SQL in the Package Cache across several importance performance categories. Computes how bad a particular statement is in comparison to other statements...

3

Clustering a Table on an Index

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. Clustering a Table on an Index I’ve...

2

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. After being a DBA for probably 5 years or more I started to...

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