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]
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]
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]
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]
Background Have you ever seen: "SQL0670N The row length of the table exceeded a limit of "<length>" bytes. (Table space "<tablespace-name>".)" When creating or altering a table DB2 imposes limits on the total worst case size of a row. This size depends on the page size of the tablespace in which the table resides: 4005 bytes in a table space with a 4K page size 8101 bytes in a table space with an 8K page size 16293 bytes in a table space with an 16K page size 32677 bytes in a table space with an 32K... [More]
Motivation Fact: "John's birthday is Oct 12, 1965" If you know John and you know his birthday to be Oct 12, 1965, then never mind. If you know John and you thought his birthday was a different one, then there is a conflict. If you know John and you didn't know his birthday, then you just learned something new about John. If you didn't know John then you now know of his existence and his birthday. How does this relate to databases, DB2 and SQL? When ingesting new data into a table it is very common that you encounter duplicates and... [More]
Motivation Imagine you are running a website like online banking. Imagine further you want to get as close to zero downtime as possible. So you will invest into DB2 pureScale, HADR, or perhaps you choose a replication solution. All these capabilities will get you close, protecting you against unplanned outages as well as outages related to hardware and OS upgrades. What none of them do is helping you deploy application upgrades! Traditionally when you upgrade an application you will take a planned outage. Then you tear down the existing... [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]
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]
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. An... [More]
But first, what's up with Serge? OK, I haven't been posting for a long time, but I do have a good excuse. Some six months ago I switched jobs from being " SQL Architect for DB2 for LUW " to being " Chief Architect for OpenPages " . OpenPages is a product IBM acquired some two years ago that is leading in the eGRC (enterprise Governance, Risk, and Compliance) space. Coming from deep in the bowels of DBMS development covering a three tier architecture, Java and a host of Web UI interfaces is challenging, but just what... [More]
There is another DB2 Tech Talk coming up this week on Thursday June 20, 2013 at 12:30 PM EDT. Transaction system availability is always a key issue, and the all-new DB2 10.5 for Linux, UNIX and Windows includes a series of enhancements that make it faster and easier for you to ensure the level of availability that stakeholders in your organization expect. Join IBM architect Aamer Sachedina for an in-depth discussion of the enhancements in DB2 10.5 that help ensure 'always available' transactions. DB2 pureScale provides two major... [More]
Introduction A table function, as you will likely know, is a function that returns a number of rows instead of a single scalar value. Table functions can therefore be invoked in the FROM clause of an SQL statement, while scalar functions get invoked in expressions. Table functions are nothing new to DB2. They have been around with C and Java implementations since DB2 V5.1. And in DB2 7.1 we made them available in inline SQL PL by using the RETURN statement as the body of the table function. The C and Java table function implementation however,... [More]
Intro Anyone who has ever attended a talk of mine on SQL compatibility has heard my claim that "to support Oracle applications in DB2 we had to add a completely new date-arithmetic library of functions since no two functions operating on date appeared to be the same in DB2."
Well, it appears PostgreSQL has its own set of functions yet again.
So, sooner or later a request for the DATE_PART() function in DB2 was bound to pop up.
Not having PostgreSQL available to test for an exact match, here is what I came up with:
Join Chris Eaton and myself for a discussion of DB2 Compression capabilities. DB2 has been supporting compression for several releases and we have
continuously improved the story and stayed ahead of the competition. He will compare DB2 10 for LUW to Oracle Database and other industry databases, explaining the features and advantages that help to maximize valuable storage resources. September 27th, 12:30 PM ET. Register: HERE
In this episode of the DB2 Tech Talk Series Cliff Leung and Holly Hayes will discuss how you can use Optim Query Workload Tuner and Optim Performance Manager to easily get to the bottom of common database problems. For those who have not looked at OPM and OQWT in a while, prepare to be amazed. We hope to see you at this very informative Tech Talk on Thursday August 30th at 12:30PM - 2PM ET!
Background The DB2 family is comprised of three products: DB2 for Linux, Unix and Windows (LUW) and Infosphere Warehouse That's the product this blog mostly deal with and within which I have grown up as a developer. Some folks still like to call it "UDB" which is wrong on so many levels, but we know what you mean anyway. DB2 for z/OS That's the product that runs on the mainframe's native OS. (Not to be confused with DB2 for LUW running on z/Linux). Some folks like to call it the "big" DB2. Again wrong, but I won't hold it... [More]
The IBM Canada Lab in Toronto is the biggest Software Lab in Canada.
As part of our engagement with Universities and also to vet potential new hires we have an extensive program for interns. Once in a while we ask them to sum up their experiences in videos. A few years ago the following gem (http://www.youtube.com/watch?v=uc2O9p3btqI) was produced which I rediscovered a couple of days ago during a sleepless night . Enjoy the video .
Motivation There are multiple ways in which DB2 supports recursion in SQL. One of which is recursion using the ANSI SQL recursive UNION ALL approach. The other approach is using the CONNECT BY clause. Actually I like to add explanation of recursion into my "SQL on Fire" talks because the topic never seems to get old. In this blog however I will not talk about recursion in SQL itself. Instead I want to answer a frequent email question about how to achieve recursion in SQL PL (or PL/SQL). Fibonacci number A commonly used example when... [More]
Motivation A few years ago I sat in a customer briefing and the customer representative summed up their database philosophy as "pack rats". What she meant was that the data warehouse is ever growing at a high rate and nothing ever gets deleted. Over the years DB2 has introduced numerous features which allow for ever growing databases such as: Large table spaces These alone allow tables to reach TB sizes Large Row iDs Large row ids allow more rows in a table Range partitioning This feature allows you to compose composite tables where... [More]