DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.

Share tech news, updates, or what's on your mind.

Sign up to Post

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence)


A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people unaccustomed to the technique, but it's an extremely powerful tool.  When used properly it can solve problems that can't otherwise be easily solved.

There are many common definitions for recursion.  The one that perhaps describes it best with regards to SQL is, "defining an infinite statement using finite components".

Of course, understanding that statement is only slightly less difficult than understanding a recursive query.  (In plain English, the statement simply means "joining an arbitrary number of rows into a single result".)


The Problem (why would I want to use recursive SQL?)

It's often desirable to take data in consecutive rows and display it on a single row.  That's an integral part of modern SQL and happens all the time when two tables are joined.  Throw in a concatenation and items that were once in separate rows are in a single column in one row.  If you want to compress three rows to a single row, join twice.  If there is doubt that there will always be three rows a NULL result must be handled.  Use the COALESCE function to make sure that the optional data is handled correctly.

But what happens when there can be a large number of rows that need to be compressed to a single row?  The query …
14
 

Expert Comment

by:ttta83
Comment Utility
Very helpful. Thanks a lot!!!
0
 

Expert Comment

by:Subrata Chakraborty
Comment Utility
Hi,

A very good article on DB2 recursive query. I also need to write a recursive query (in DB2) and while searching in Google I got this web page. This concept is very new to me. I got a requirement where I need to convert below rows to columns using recursive query..

City_Name          State_Name
Montgomery       Alabama
Wilmington         Delaware
Birmingham        Alabama
Anchorage           Alaska
Dover                   Delaware

Query result should be.. (rows will become columns)

Alabama           Delaware      Alaska
Montgomery
Birmingham    
                           Wilmington
                           Dover
                                                  Anchorage          

Just wanted to know your suggestion if this can be achieved using recursive query?
Thanks
0
Efficient way to get backups off site to Azure
LVL 1
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Recursive SQL in UDB/LUW (it really isn't that hard to do)

Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of that and describe converting columns to rows.  A review of the original article may be helpful as some of the examples that will be used here will tie directly to the examples in the original article.


One of the common misperceptions about recursive SQL is that it requires a join operation.  While this is true to convert rows to columns it's absolutely false when converting columns to rows.  The very nature of the join operation is to put data from different rows (usually rows from different tables ) into the same row and that doesn't change just because the SQL is recursive.


The Problem (why can't I just use plain old SQL to solve this?)

It's often desirable to take data in one row and display it on multiple rows.  Standard SQL doesn't have a built-in mechanism to do that, but the flexibility of SQL makes it possible.  The power of recursive SQL makes it downright easy.

The simplest case is to just want a data item split and shown on two lines of a report.  The SUBSTR, LEFT and RIGHT functions can be used to split the …
6
November 2009

Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm not a UDB LUW expert, so don't take my word on that!

DB2 UDB for AS/400 is different!

DB2/400 is just a little bit different (and superior in this one particular case, though that is certainly not always the case!).

The good news is that in DB2/400, you can easily control the date format for any given connection to the database, and you can even change it from one format to a different one between subsequent queries in the same session.

The original question showed the output from this query:

SELECT date('01/12/2009')
  FROM sysibm/sysdummy1                

Open in new window


Returns: 01/12/09, but the user wanted to see the date in mm/dd/ccyy format (01/01/2009).

First of all, understand that the SQL date() function is not dropping the century in the example above.  The session that this query ran in is just configured for *MDY date format.  It really has nothing to do with the DATE() function itself.

The century is still there, you just can't see it on screen or paper when the date format is set to *MDY, as it is apparently in the example query.

Bear with me and I'll explain what I mean.

DB2/400 DATE data type

The SQL DATE() function converts an expression representing a date (a string in …
3

DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.