The next DB2 Tech Talk is one that should be of interest to those that follow this blog. On April 10, 2014, Nick Ivanov, an IBM Senior Consultant, will present a talk called: SQL Tips and Techniques, Leverage the Power of SQL Those who follow this blog will already realize that SQL is a powerful language. The objective of this talk is to help you learn to use more of that SQL power. Join us to explore some of the less well-known features of SQL and see how they can help in some practical situations. We will also look at some common mistakes and... [More]
Motivation Have you ever received an error message that left you scratching your head? Of course you have. Matter of fact I for one cannot recall any software product that ever outright impressed me with the quality of their error messages. DB2's messages, overall tend not to be too bad compared to what I have seen with competitors when it comes down to regular SQL. But things get really interesting when you add SQL PL (or PL/SQL) to the mix. "My" product OpenPages is chock full of tens of thousands of lines of PL/SQL and recently... [More]
Are you writing SQL applications that need to be portable across DB2 platforms? Are you looking for information to help ensure your DB2 applications are portable? Back in June 2012, Serge posted an entry called " Family matters: The SQL Reference for Cross-Platform Development " where he wrote about the DB2 family and the availability of Version 4 of the SQL Reference for Cross-Platform Development . I want you to be aware of some related cross-platform summary information that is also available. A developerWorks article with... [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]
Background When our team added compatibility for DB2 with Oracle applications we learned that no two functions were compatible between Oracle and DB2 as far as date-time arithmetic was concerned.
We ended up adding an entirely new library to achieve compatibility.
It appears the difference between DB2 and MS SQL Server is equally profound in this area given that I was recently asked to provide a match for the DATEADD() function. DATEADD() This TSQL is a bit of an odd one.
It takes three arguments: A "measure" The measure can be... [More]
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]
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]
Introduction Recently I discussed the motivation behind using anonymous blocks. The reasoning was centered around the need to execute complex scripts on the server without being able or willing to define a routine in the database's catalog. Inside of an anonymous block you can then define all the logic flow you need. You can also declare local variables to hold temporary data. DB2 10 expands beyond that by allowing you to declare local types and local procedures. Local types When ARRAY types were introduced in DB2 9.5 we discussed whether there... [More]
Register today for May 31, 12:30 EST to 2PM EST Join me in this DB2 Tech Talk where I will provide an update on DB2's Oracle Compatibility features. I will: Give an overview over the architecture and feature set Provide an update about what has been added since DB2 9.7 Do a quantitative assessment on the success of the strategy Look at a case study. This technical tour is the third DB2
Tech Talk on the DB2 10 and InfoSphere Warehouse 10 product releases.
Additional webcasts will be offered throughout 2012 to provide an
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]
Motivation Some of you may have been using DB2 as far back or even longer than DB2 7.1, or DB2 UDB V7.1 to be correct. In that release we introduced SQL Procedures, and SQL Functions. When we shipped the features there was a bit of a confusion because everyone from sales to marketing to customers seemed have a dire need for a name of logical constructs we supported. It's just SQL we said. ANSI SQL comprises more than just SELECT. In includes IF, BEGIN.. END and so forth. All we do is implement the SQL standard! I learned and important lesson... [More]
Motivation It is very common nowadays for SQL statements to be generated by the application itself for by some productivity middle ware which abstracts the database. On the server however, within routines, triggers or anonymous blocks most SQL is fixed and will execute just the way the definer of the object has specified it. There are, however exceptions to this rule: Executing DDL statements such as CREATE TABLE or DROP INDEX While DB2 supports select DDL statements, generally DDL is not part of the SQL PL syntax Execution of highly... [More]
When I started leading the project to add Oracle's PL/SQL to DB2 it became quickly clear that perhaps amongst the top five features needed to claim a reasonable level of compatibility were autonomous transactions . An autonomous transaction ("#PRAGMA AUTONOMOUS" in PL/SQL speak) is a transaction which executes in total isolation from its invocation context. While this feature has seen a lot of uptake with the Oracle to DB2 realm I have rarely seen it used in a traditional DB2 environment. The reasons for that may be: Poor... [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]
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]
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]
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]