Happy Holidays to everyone! Another year is coming to a close and this will be the last entry of 2011 in this blog. Did you know that DB2 supports the rich cast functions TO_CHAR , TO_DATE , TO_TIMESTAMP and TO_NUMBER ? What makes this functions interesting compared to standard CAST expressions is that they allow you to specify a formatting. For example if you are in Germany you may want to pretty print dates like this: VALUES TO_CHAR(CURRENT DATE, 'DD.MM.YYYY'); 1 ----------------------- 25.12.2011 1 record(s) selected. While in the United... [More]
DB2 provides a whole set of tools to analyze locks, but what is missing is a way to connect LOCKs easily with actual rows. So in this post I will try to fill that gap. As always we start with a simple running example. Given a PRODUCTS table with some rows in it we will lock a row and then try to find it again. CREATE TABLE products(prodid INTEGER NOT NULL PRIMARY KEY, prodname VARCHAR(20), prodline VARCHAR(20)) PARTITION BY RANGE (prodline) (PARTITION A_TO_L STARTING MINVALUE ENDING AT 'L'... [More]
Just recently in DB2 9.7.5 we introduced the HEXTORAW function. This function converts a hex-string into a VARCHAR FOR BIT DATA and is thus the inverse of the HEX function. But is it really? Not quite. the HEX function dumps the internal representation of all sorts of types, not just strings. So the question arises: How hard would it be to add other conversion functions which re-constitute values from their hex-dump? Perhaps I'll write a little library and publish it here in the near future, but for now let's look at one such function only:... [More]
Do you know what the Halloween Problem is? I learned about it first in 1997 when learning SQL from Don Chamberlin.
Learning from Don meant I implemented inline SQL Function support. He designed and implemented the quality assurance breaking within minutes what I believed to be bullet proof design. "Learning from failure" is how you call that I think. The story of the Halloween Problem is nicely recounted on Wikipedia . In it's essence it involves: A scan operation over a table or index An action within that scan which changes a... [More]
You may know that I lead the team enhancing DB2 to simplify Oracle application conversion to DB2. A fair number of features we have added, such as VARCHAR2 are not terribly interesting for true blue customers. But there are also quit a few features which add value for everyone. One such feature commonly called multi-action trigger support was added in DB2 9.7.4 released in April 2011. A triggers, as I''m sure you know, consists of procedural logic specified in SQL PL (or PL/SQL) which is executed when the trigger subject table is modified.... [More]
A few days ago I did an internal presentation on improvements to DB2's compatibility with Oracle applications. Doing so I stumbled upon one change I actually implemented myself that had nearly slipped my mind. The change is very minor, and, on the face of it has nothing to do with Oracle applications. What I implemented for DB2 9.7.3 were comparison operators for " small LOBs " A small LOB in itself is a bit of an oxymoron, given that the L stands for Large. Also Oracle allows a lot less native operations on LOBs than DB2 to begin... [More]
OK, it's not exactly "at" Open World, but I just discovered that IBM is offering a Workshop for Oracle Professionals free of charge in San Francisco, just prior to Oracle Open World. So if you happen to be an Oracle DBA or Application Developer and you want to expand your skills then please drop by. And of course if you are already a DB2 user, show some compassion, pass along the news, and show you colleagues to the light. Friends get friends DB2 certified!
I spent the time around the July 4th weekend at the IT building a major bank in Jersey City assiting in the "going-live" of their .com website on DB2 after moving their core internet application away from Oracle. Needless to say there were the usual hiccups compounded by the fact that the app is non trivial and has stringent Service Level Agreements (SLA) to fullfill. So, naturally, like any good tradesperson I brought my tools including the SQL PL Profiler I posted about a while ago. When I wrote this profiler however I had left some... [More]
After a long hiatus just a small entry in the hopes of a bigger one soon. Today I got a question from a co-worker originating from a DB2 DBA: "DB2 has a LAST_DAY() function which returns the last day of the month given a date or timestamp. But where is the matching FIRST_DAY() function?" Good question! First let me explain that LAST_DAY() was introduced to simplify enabling Oracle applications to DB2. It is a mere convenience function since simple date arithmetic will produce it. Since no other vendor has FIRST_DAY() and that function... [More]
A co-worker just walked in and asked how to subtract timestamps in SQL. Easy, you just use the TIMESTAMPDIFF() function. Now, personally I just plain hate this function because it has some serious shortfalls: It approximates the difference between timestamps assuming 365 day years and 30 day months It takes some really odd arguments. So, I think there is a need for an exact and generic function which subtracts dates or timestamps. There are two ingredients which DB2 supplies natively which come in handy: JULIAN_DAY() Given a DATE or TIMESTAMP... [More]
I should know better than you answer "Sure!" when someone asks me if I have a "few minutes" for a "quick SQL question". But then again, once in a while these not so quick SQL questions turn into nice posts. This "quick" SQL question turned out to be a request to implement PERCENTILE. My first counter question of course was "Please define PERCENTILE". The request went something like this: Given a table with three columns: An object (such as "server") An instance (such as a "point... [More]
UPDATE: Feb 11, 2012 I have added a TRACE procedure and renamed ENABLE/DISABLE_EXPLAIN_ACTUALS to ENABLE/DISABLE_ACTIVITY_MONITOR. The TRACE procedure returns a formatted trace of all SQL monitored by the activity monitor. This includes nested compiled triggers, procedure calls and function invocations as well as error conditions. Next on my list: trace-backs for errors and also tracing of parameters.
For the past months I have been tinkering on and of with the "new monitoring API" which has been introduced in DB2 9.7. Here are the... [More]
In quite regular intervals some well meaning soul somewhere in the DB2 user-verse sends me an email that roughly goes like this: I have come across a strange error in DB2. By accident I omitted the predicate in a WHERE clause and DB2 actually compiled it. This should be a bug, shouldn't it? Well, of course it would be a bug if that is what really happened - but it didn't - or did it? CREATE TABLE T(c1 INT); INSERT INTO T VALUES 1; SELECT * FROM T WHERE; C1 ----------- 1 1 record(s) selected. Hmm, what's going on here? I claim there... [More]
Yesterday I got an interesting request for a MEDIAN function in DB2. After a quick look at Wikipedia MEDIAN I figured this can't be too hard and the result may be of interest to others. At the beginning there is a table, preferably with some data CREATE TABLE T(c1 INT); INSERT INTO T VALUES 10, 12, 30, 33, 50; Now the MEDIAN is the middle value of a set of values. So in the case above that would be 30. We can get to the middle value by counting the number of values, number them while we do that and then take the one in the middle. SELECT c1 ... [More]
The most satisfying work in developing SQL to me is whenever we add infrastructure to DB2 which makes DB2 more extensible. For example adding a function like DECODE which is syntactic sugar for CASE expressions is nice, but it only solves a very specific issue. Contrast that with what we did in DB2 9.7 when we introduced global variables. Here are some of the key properties of global variables They are schema qualified That is you can have multiple variables with the same name in different schemas Variables are resolved by PATH So you can make... [More]
Years ago name resolution for so called identifiers was an easy thing to do. All there was were columns. But then along came routines. Routines had parameters, local variables and FOR loops. Next came global variables, then modules and ultimately pseudo-columns. All of these objects share one and the same name space.. In this post I will try to illuminate the rules by which DB2 decides who is who. Let's start with a simple example: CREATE TABLE T(c1 VARCHAR(10)); INSERT INTO T VALUES 'Table T'; SELECT c1 FROM T; C1 ----------- Table T 1... [More]
What seems like eons ago we introduced IDENTITY columns into DB2. This was in DB2 UDB V7.1 to be precise. As you may know IDENTITY columns are special forms of SEQUENCEs which are tight to a specific column in a specific table. The purpose of an IDENTITY column is to supply an abstract primary key for the table which generally is maintained by DB2. For example: CREATE TABLE emp (empid INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(20), salary DECIMAL(10, 2)); You can now insert employees and let DB2 take care of... [More]
Do you write SQL or SQL PL code that you want to provide to others? For example you are a vendor who sells code to customers, or perhaps you want to provide share-ware libraries of DB2 extensions, but that doesn't mean you want to provide open source code? Before DB2 9.7 there really wasn't a way to protect your SQL assets once they were deployed on a database. The TEXT column in the SYSCAT.ROUTINES, SYSCAT.TRIGGERS and SYSCAT.VIEWS catalog views made it impossible to hide your hard mental gymnastics from the prying eyes of the DBA and thus... [More]
Some of you may know that I am engaged in enabling Oracle applications to DB2. One of the frequent "issues" we see are differences between the way how indexing works in Oracle and DB2 and, more specifically about different semantics for unique indexes. In DB2 a unique index requires that no two keys are the same. Sounds simple and clear on the surface, but not so clear when you consider NULLs. Generally speaking a NULL cannot be compared to a NULL and thus it is debatable whether one should allow more than one NULL key in a unique... [More]
First of: Happy New Year to everyone. I hope your holiday season was as pleasant as mine. In the first post of this "mini-series" I introduced inline SQL PL and compiled SQL PL; when they were introduced along with a time line of added capabilities. In general most of us believe choice is good. Oftentimes however choice can cause confusion. Do I buy the red shoes or the black shoes? Life is so much easier when there is less choice, or at least when it's clear which choice is better. So in this post I'll make an attempt to describe... [More]