DB2 9.7 FP4 is out: Trigger support has been enhanced and some other goodies

A new fixpack 4 is now available for DB2 9.7. An overview of new features and enhancements is on the Information Center. Many of the enhancements are designed to make migration from other database systems, namely Oracle, simpler, saving even more on migration costs.

What stands out from my perspective, are the enhancements to the trigger functionality. Now you can lump the definition of update, delete, and insert triggers together into a single DDL statement. Speaking of statement, support for statement triggers that fire only once per execution has been added to DB2's PL/SQL functionality, too.

Many of you will like (pun intended!) a new LIKE feature. It is now possible to use a column reference as pattern expression, i.e., to look up the actual pattern in a different table.

Note that the fixpack has already been upload and the Information Center been updated. However, it seems that the fix list overview page and the fixpack summary page still need to be updated.

New IBM Redpaper: Highly Available and Scalable Systems with IBM eX5 and DB2 pureScale

A new IBM Redpaper discussing DB2 pureScale on System x eX5 machines has been published this week. The paper has a high-level introduction to pureScale and its benefits and talks about the situation at a specific customer. It's not that deeply technical, but a good introduction and with some links to more resources.

My resource page has been updated

I keep a DB2 and pureXML Resources page as part of this blog. I added some more links and will continue with this as I find time.

Pure and free: Test pureXML on pureScale

You probably already know that there is a feature for XML data processing in DB2 which is called pureXML. You also know there is a continuous availability and scale-out feature for DB2 called pureScale. And I won't talk about pureQuery today. But did you know that pureXML is supported on pureScale? Did you know that you can request access to the so-called pureScale Acceleration Kits and try it out yourself (for free)?

I recently was instructor for a pureScale Workshop and one of the attending companies reported that they built their own small pureScale system. Such a mini system - we often refer to them as nanoClusters (or here) - has all the features of a real cluster, but only costs few hundred dollars/Euros in hardware and you have your own pureScale system. If you want to "go high-end", you could request a proof of concept/proof of technology at one of the IBM locations. But in either case, test drive  pureXML on pureScale...

Some more time, some XML functions

I had written about some details regarding current time and timezone for the regular DB2 and the Oracle mode. Now I had some time to play with built-in XQuery functions.

There are quite many that deal with time, date, timestamps, and timezones. For the XML and XQuery processing an implicit timezone of UTC (Coordinated Universal Time) is assumed. This is something to keep in mind when processing XML data - different semantics again (who said life is easy?). XQuery defines a good number of functions and operators on durations, dates, and times. DB2 supplements that with some more functions to make life simpler (not easy) in DB2. Most of them provide the local value, i.e., the one related to where your database server is located.

xquery current-dateTime()

--------------------------------------

2011-04-08T10:12:48.70082Z

Note the "Z" behind the timestamp, indicating Zulu (UTC) time, not necessarily the one of your location. DB2 adds its own function to deliver that:

xquery db2-fn:current-local-dateTime()

--------------------------------------

2011-04-08T12:13:44.026531

Now the "Z" is missing and we have a timestamp without a timezone. How about some experiments with timezones?

xquery db2-fn:local-timezone()

--------------------------------------

PT2H

xquery db2-fn:current-local-dateTime() - current-dateTime()

--------------------------------------

PT2H

With the first call we can obtain the timezone at our place. In my example it is the Central European Time with Daylight Savings being active. Not surprisingly, subtracting the (global) current time from the current local time, we receive the same difference in hours.

xquery adjust-dateTime-to-timezone(current-dateTime(),  db2-fn:local-timezone())

--------------------------------------

2011-04-08T12:18:09.078883+02:00

XQuery has a function to adjust a timestamp to a specific timezone. In the example above, we use the already known functions as input and the result gives another timestamp. The interesting part about is that now a timezone indicator is returned, "+02:00".

My time is up for today. If you have time, try out the other time-related functions in XQuery...

(Updated) Times are changing: DB2 vs. Oracle mode

If you expected a show off between the two database systems as part of this blog article, you will be disappointed. I only wanted to show you some - on first sight strange - behavior you can run into, based on whether you are using the regular DB2 date and timestamp semantics or the Oracle compatibility mode.

Let's start with a regular DB2 database:

db2 => values current timestamp

1                        
--------------------------
2011-04-07-16.07.55.194069

  1 record(s) selected.

db2 => values current timestamp - current timezone

1                        
--------------------------
2011-04-07-14.08.13.495288

  1 record(s) selected.

Now we switch to a database with the DB2_COMPATIBILITY_VECTOR set to ORA (and date_compat enabled):

db2 => values current timestamp

1                        
--------------------------
2011-04-07-16.09.47.311791

  1 record(s) selected.

db2 => values current timestamp - current timezone

1                        
--------------------------
1956-07-04-16.09.59.043262

  1 record(s) selected.

The year 1956? This looks strange. But when looking into documentation for date values in Oracle mode, we learn that we are operating on timestamp(0) semantics and that adding or subtracting values mean dealing with days. Be aware or you are turning the wheel of time faster than you imagined...

Update: I thought I should point out how I solved the puzzle. The following gives the same result, regardless of what mode you are working in.

db2 => values current timestamp - (current timezone / 10000) hours

1                        
--------------------------

2011-04-07-14.08.13.495288

  1 record(s) selected.

Get the hint: How to enable support for Oracle-style hints in DB2

One of the more frequently asked question of DBAs coming from Oracle to DB2 is about how optimizer hints work in DB2. The standard answer we give is that there are no hints of that kind in DB2 and that a cost-based optimizer is used. Keep your statistics up-to-date (and maybe use automatic runstats), try out different optimization levels, and maybe set some of the documented (and undocumented) registry variables.

Well, I just mentioned undocumented registry variables. When you run "db2set -lr",  you get all supported registry variables listed - about 190 in my installation of DB2 9.7. A list of many of them and some additional links is in the DB2 Information Center. When you go to the overview of performance variables, you will notice the variable DB2_EXTENDED_OPTIMIZATION. Now here comes the trick of enabling Oracle-style hints:

db2set DB2_EXTENDED_OPTIMIZATION=OHINTS

Make sure, it is really set. Calling just "db2set" should show the new value. Then try it out. For my test, I created two tables with about 100,000 rows. Then I executed my query the first without hints to measure the time and to look at the access plan.
Then I tried it with hints:

select /*+ Make this really fast, please */ a.id, a.desc, ...., b.colfoo, b.doc
from hlhints a, mytest b
where a.id=b.nr and b.colfoo LIKE '%!!argh!!%'

Just based on the measured time, I can say that it really works. Try it yourself.