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]
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]
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]
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]
Prelude I have have been engaged in discussions on how to use DB2 for LUW for as long as I have been employed with IBM. Until not too long ago, before I started blogging, the primary platform for such discussion was usenet. And one of the very oldest debates I recall was with Bernard D. For at least a decade he has been tirelessly trying to convince us to extend DB2 for LUW to support comparisons of "row value constructors" (rvc). In fact the oldest record I can find on Google on this debate dates back to October 21, 2000. Well,... [More]
Motivation Have you ever had the task to write a script for a database? Often users write scripts to monitor the database in a host language such as PERL or bash. But these host languages have some downsides: The language environment must be installed on the server. This creates a dependency on a specific environment and one more moving part The environment may not be portable. For example if you move the database from Windows to Linux a shell script is unlikely to work unchanged If a lot of data needs to be processed in the script performance... [More]
Register today for April 26, 12:30 EST to 2PM EST Join us for a technical tour of the
details behind these IBM DB2 and IBM InfoSphere Warehouse new product
releases as announced on April 3 rd . This technical tour
will cover powerful new features including: Storage Optimization advances ,
such as Adaptive Compression and Multi-Temperature Data Storage,
which are designed to optimize both the storage environment and
system performance. SQL Compatibility enhancements that make it even faster and easier to migrate from Oracle... [More]
Background In January 2011 I posted Inline SQL PL vs compiled SQL PL . One of the points I made in this entry was that inline SQL PL is preferable for simple SQL PL logic because it is faster. This implies of course that compiled SQL PL has some headroom when it comes to performance improvements. There is always room for improvement just about anywhere, so it can be interesting to talk about what motivated investments in this area. In this case an IBM partner was enabling an Oracle application to DB2. Most times we find that applications... [More]
The DB2 for LUW team has been busily driving down the cost of enabling Oracle applications since that effort started with DB2 9.7. Every DB2 9.7 fixpack up to DB2 9.7.5 has contained features to incrementally increase the level of compatibility allowing users to break free of Oracle. Now, finally it's time to unleash DB2 10 with yet another set of goodies. Here are some: Local Types This feature allows PL/SQL and SQL PL blocks to locally declare types for consumption in variables within the BEGIN .. END block. Previously when such type... [More]
Data Archiving: best practices to improve database performance and reduce costs Date: March 29, 2012 12:30 - 2:00 PM EST Presenter: Eric Naiburg, Program Director, Information Governance Solutions " When it comes to data, more isn’t always better. Users demand that
applications perform at their best, yet system complexity and data
volumes continue to grow. This causes application and database
performance to slow" I hope you will join us for the next DB2 Tech Talk, Eric will be talking about data archiving best practices... [More]
Motivation A major DB2 partner acquired a subsidiary a while back which sells an application that needs to me enabled from Oracle to DB2. The team is making good progress, but recently ran into a snag. There was a difference between Oracle and DB2 which caused DB2 to raise a run time error. With deeply nested routines which are being driven by an application homing in on the root cause was not trivial. Tracing This is a prime example for tracing. Given a tracing facility the team could have attached to the application, traced the failing... [More]
In this post I want to introduce a handy procedure which can be used to suspend a session for a specified amount of time.
A naive approach to wait is to simply execute a tight loop in a stored procedure such as this:
--#SET TERMINATOR @ CREATE OR REPLACE PROCEDURE SLEEP(seconds INTEGER) BEGIN DECLARE end TIMESTAMP; SET end = CURRENT TIMESTAMP + seconds SECONDS; wait: LOOP IF CURRENT TIMESTAMP >= end THEN LEAVE wait; END IF; END LOOP wait; END @ --#SET TERMINATOR ; VALUES CURRENT TIMESTAMP; 1 --------------------------... [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]
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]