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]
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]
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]
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]
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]
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]
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.
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]
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]
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]
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]