25.10.2007

DB2: Automatic line numbering

When generating reports with lots of lines, sometimes it's nice to have them numbered automatically. For this DB2 offers the ROW_NUMBER() OVER() functionality. The first part numbers the rows in the order they are passed from the retrieving process. Therefore, if you're using the ORDER BY clause for displaying the resultset, you may get line numbers but in no special order. You'll need to pass the ordering directive to the second part OVER().

Here's an example:

Select
ROW_NUMBER() OVER(order by NMBR asc) as ROW,
NMBR
from
lucky.numbers
order by
NMBR asc

ROW NMBR
-------------------- ----------
1 123
2 456
3 2345
4 12345

4 record(s) selected.

Diese Seite zu Mister Wong hinzufügen

21.10.2007

Case insensitive indices

In my experience DB2 is one of the most correctly working databases. Where others are, lets say, a bit more flexible, DB2 follows the given path down to the last bit. An example we just fell over: Case sensitivity.

These days we installed a new product which usually works with a database system made in Redmond. We wanted it to work with DB2 and were told that - in theory - there should be no big problem. Praxis showed that there's always Murphys law awaiting you.

At one point the software does a query with condition like „WHERE upper(NAME)='ABCD' “. While in the beginning there was no problem, we found that several data imports later this little phrase slowed down the whole system. The cause: Although there was a (case sensitive) index on the NAME column, DB2 had to do an upper() on every single row.

While DB2 v9 for z/OS allows you to build an index using the upper() function, DB2 v8 doesn't give you this possibility (yet). But there's a workaround. Here's how we got around.

First, We added a generated column, containing the upper case text:

-- alter table
SET INTEGRITY FOR A001.FOOBAR OFF ;

alter table A001.FOOBAR
add column NAME_UP
GENERATED ALWAYS AS ( UPPER(NAME) ) ;

-- enable integrity again and fill column; may take a while
SET INTEGRITY FOR A001.FOOBAR
IMMEDIATE CHECKED FORCE GENERATED ;

Then, we added an index on the newly created column:

-- drop old index; create an index using new upper_column
DROP INDEX A001.FOOBAR_IDX1;
CREATE INDEX A001.FOOBAR_IDX1 ON FOOBAR ( NAME_UP, XID, YID );

After that, the internal optimizer recognized that it could use the GENERATED column and its index, and the import jobs returned to the high velocity they had shown in their very first moments: Instead of hours or even days, everything was done within a couple of minutes.
Diese Seite zu Mister Wong hinzufügen

15.10.2007

Long running transactions

Frequently there are jobs to do on the database which affect quiet a couple of rows, like deleting yesterdays log events from the log table, turning into a long running transaction. While being executed, this may lead to certain lock events, blocking other processes. So what to do about those long runners?

Rule #1 above all others: Avoid long runners. This may be done by turning them into a couple of short runners. Based on the above example, a delete of yesterdays events could be broken down to 24 steps, starting with a
"DELETE ... WHERE event_date=(current_date-1 day) AND event_time>='23:00:00' "
and ending with
"... even_time>='00:00:00' ".

If there's no possibility to go that way, the execution may gain speed by one or more of the following steps:
  1. Try to do this in off peak hours to have more resources for your long runner.
  2. Increase the size of the buffer pool(s) and the NUM_IOCLEANERS if necessary/possible.
  3. Log files should not be placed on the same physical file system as the database itself.
  4. Sometimes increasing the parameter LOGBUFSZ has a possitive effect. (Attention: LOGBUFSZ is allocated within DBHEAP so you have to change this value, too.)
For point 2-4 you'll need DB2 admin rights on the database. Besides, moving the log files to a different disk often improves all DB2 write actions to the specific database, and so does adding memory to the buffer pools. But when adding memory to those, try to add it step by step and monitor the gain of performance. Usually you'll get to a breaking point somewhere when adding memory doesn't earn you much performance any more. You may also use the high water marks and the log_space_used value when monitoring the usage of memory.