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.

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.

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.

DB2: Internal functions won't run

They say that time and space are relative. If knowing Einstein and having a voice, my computer would confirm this, for that at times its disc space is relatively short.

DB2 sometimes has a rather complex relationship to time itself, too. Once we changed the system time on a Linux based DB2 test server, and subsequently forgot about this. Next day, a developer called us and told us that some of his sql statements failed where they still had been working several days ago.

Our investigations showed that when containing an internal function, like in "db2 values ucase('hello') ", DB2 gave us the following error message:

SQL0440N No authorized routine named "UCASE" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

This error appeared on all existing databases in that instance. After creating a new database, we found that only the old ones were affected.

We opened a PMR with IBM and the support told us that sometimes this may happen when changing the system time. (Maybe we moved it too far?) There's a DB2 tool called 'fixfunc' available at IBM support. We tried it and it succeeded - all databases were usable afterwards.

DB2 Express-C v9 for Power architecture

Since v8, the freely distributable version of IBMs database software, DB2 Express-C, is available for Windows and Linux, the latter supporting both intel and power architecture. Several days ago, I was looking for v9 to download, but couldn't find it. All the download list offered was v9 for Windows and Linux on x86 and ia64 systems.

After writing an email to db2x@ca.ibm.com, Ryan Chase and Ian Hakes of the db2 express-c community team reacted and added the v9 ppc64 (power) version to the list again. It's available in the Express-C download area. You 'll need to login before selecting the download.