DB2 for LUW is a very extensible database engine and one of things I do love to recommend to customers and IBMers alike is to use these capabilities whenever they encounter mismatches between e.g. Oracle and DB2 which need to be overcome. or, alternatively, perhaps you want to add some "libraries of function" on your own. Let's for example assume you are implementing the INSTRB function. INSTRB is just a synonym for INSTR(VARCHAR, VARCHAR) so perhaps you write the following: CREATE OR REPLACE FUNCTION ORALIB.INSTRB(VARCHAR(),... [More]
One of the FAQs I see in newsgroups is the request on how to aggregate strings. That is, how do I collapse rows of a string columns into e.g. a comma separated list. DB2 (as of 9.7.3) does not have any built-in function for this, but there are interesting ways to get the job done. Let's use an employee table as an example:
CREATE TABLE emp( empid INT NOT NULL PRIMARY KEY, name VARCHAR(20), salary DECIMAL(10, 2), dept VARCHAR(20)); INSERT INTO emp VALUES (1, 'Jones' , 20000, 'Research'), (2, 'Schmidt' , 23000, 'Research'),... [More]
Late during DB2 9.5 for LUW development a small set of functions was added without much fanfare which I always feel has been under appreciated. So this post is dedicated to explain it. I am speaking here of DB2's bit manipulation functions. How many columns do you have in tables whose values can only be 'Y' and 'N' or 1 and 0? A lot, a few, none? If it's only a few or none, then you probably do not care too much, but if you have a lot then bit functions may just be for you. In a nutshell DB2 9.5 introduced the ability to to interpret whole... [More]
Having demonstrated how to aggregate strings using XML in an earlier post I have been asked about how to do the inverse. Giving a a string and a defined separator, produce a table with one row per substring. So here is what I came up with: CREATE OR REPLACE FUNCTION PK_BASE.SPLIT(text VARCHAR(32000), split VARCHAR(10)) RETURNS TABLE(column_values VARCHAR(60)) RETURN WITH rec(rn, column_value, pos) AS (VALUES (1, VARCHAR(SUBSTR(text, 1, DECODE(INSTR(text, split, 1), ... [More]
DB2 provides several means to generate unique identifiers depending on ones need. For example: IDENTITY column That's a column of a table which is generated automatically, typically in an ever increasing fashion.
CREATE TABLE emp(empid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, name VARCHAR(20), salary DECIMAL(10, 2)); INSERT INTO emp VALUES(DEFAULT, 'Jones', 20000); VALUES IDENTITY_VAL_LOCAL(); 1 --------------------------------- 1. 1 record(s) selected.
Without much fuss Large Object (LOB) support has gotten a big overhaul in DB2 9.5 and DB2 9.7. In this article I'll try to bring all the various improvements together. Cursor blocking When DB2 does cursor blocking that means that instead of shipping one row at a time upon every fetch DB2 will send a number of rows in one chunk. Subsequent fetches by the application are then served from this cache of resultset rows until the set is exhausted and the next block is delivered. The benefit is obvious. In most cases the number of network trips can be... [More]
PIVOT The meaning of pivoting a table is to turn n-rows into n-columns. For example given a ales table with a composite primary key of year and quarter you may want to get a result that shows only one row for each year, but a separate column for each quarter. CREATE TABLE Sales(Year INTEGER NOT NULL, Quarter INTEGER NOT NULL, Results INTEGER); CREATE UNIQUE INDEX PK_IDX ON Sales(Year, Quarter) INCLUDE(Results); ALTER TABLE Sales ADD PRIMARY KEY (Year, Quarter); INSERT INTO Sales VALUES (2004, 1, 20),... [More]
In this entry I'll illuminate techniques on how to pick out specific rows from a set. Sound easy right? Well we shall see. Let's start simple. What is the biggest salary made by an employee in my department? CREATE TABLE emp(empid INTEGER, name VARCHAR(20), salary INTEGER, dept INTEGER); INSERT INTO emp VALUES (1, 'Jones', 20000, 100), (2, 'Smith', 25000, 100), (3, 'Friedman', 22000, 101), (4, 'Muller', 21000, 101), (5, 'Holmes', 30000, 101);
To get the maximum salary we use MAX(),... [More]
I recently got asked whether DB2 support the INTERVAL data types. My answer was, no, it does not. But DB2 does support so called "labeled durations"! Labeled durations are a transient type that can only appear when you are doing datetime arithmetic. An example for a labeled duration is: "7 months". So: VALUES CURRENT TIMESTAMP + 7 MONTHS Adds seven months to the current moment in time. Now, labeled durations predate the SQL Standard and they do have the downside that you cannot store them in a column. You can only store the... [More]
It is by now reasonably known that DB2 9.7 speaks to a high degree Oracle. and there are articles of mine on developer works which speak to that. The question I keep hearing often from DB2 customers however is: What's in it for me? Can I mix Oracle and DB2 dialects? So in this entry I'll try to separate what is acceptable Oracle-speak around your DB2 applications and what is, well, language you should not use around the dinner table. You can classify DB2 9.7 Oracle speak into four categories: Extensions to DB2's functionality These are... [More]
Over the years DB2's SQL Procedure Langiage (SQL PL) has taken on a more and more prominent role and those who know how to use it properly can do some amazing things. The next couple of posts are dedicated to SQL PL and to giving some advice on the do's and don'ts. First, in part one, let's start with a history brief lesson to get some perspective: DB2 V 2.1 for CS Supports for inline SQL PL Triggers . SQL PL means consists of exactly one construct: BEGIN ATOMIC .... END. Inside the compound you can do SIGNAL, INSERT, UPDATE, DELETE, VALUES and... [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]
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]
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]
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]
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]
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]
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]
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]
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]