Recursive SQL in DB2

Problem
Playing around in DB2 I wanted to know how to create a hiearchical query. This because I wanted to create view for explain plans, a bit resembling the explain plans which oracle provides.

First step is understanding how to make the recursive SQL’s

The setup:

drop table relations;

create table relations (id int, parent int);

insert into relations values(0,NULL);
insert into relations values(1,0);
insert into relations values(2,1);
insert into relations values(3,1);
insert into relations values(4,3);
insert into relations values(5,0);
insert into relations values(6,5);
insert into relations values(7,5);
insert into relations values(8,6);
insert into relations values(9,7);
insert into relations values(10,0);
insert into relations values(11,1);

commit;

Just to be sure,it looks like:

ID          PARENT     
----------- -----------
          0           -
          1           0
          2           1
          3           1
          4           3
          5           0
          6           5
          7           5
          8           6
          9           7
         10           0
         11           1

What I would like to see is something resembling following:

ID   Parent
0    -
1      0
2         1
3         1
4            3
11        1
5      0
6         5
8            6
7         5
9            7
10     0         

First try: (Recursive) Common Table Expressions (r)CTE
DB2 has a nice way to have recursion in r(CTE) let’s see if I can get that working:

with recur(id, parent, level) as
  (
    select rel.id id, rel.parent parent, 1 level 
    from   relations rel
    where  rel.id=0
    union all
    select rel.id, rel.parent, rec.level+1 
    from   recur rec,
           relations rel
    where  rec.id=rel.parent
    and    rec.level<10
  )
select id, 
       cast(lpad(parent, level*2, ' ') as varchar(20)) parent,
       level 
from   recur;

This gives me the following result:

ID          PARENT               LEVEL      
----------- -------------------- -----------
          0 -                              1
          1    0                           2
          5    0                           2
         10    0                           2
          2      1                         3
          3      1                         3
         11      1                         3
          6      5                         3
          7      5                         3
          4        3                       4
          8        6                       4
          9        7                       4

This is somewhat resembling what I want. However, the query shows the rows all per level. After some searchinf I came to know that this is calles "Search Breadth First", what I need is "Search Dept First".

Tried a lot of things but to no avail.
On the net I came across the "Search Dept First" clause. This should then look like:

with recur(id, parent, level) as
  (
    select rel.id id, rel.parent parent, 1 level 
    from   relations rel
    where  rel.id=0
    union all
    select rel.id, rel.parent, rec.level+1 
    from   recur rec,
           relations rel
    where  rec.id=rel.parent
    and    rec.level<10
  ) search depth first by parent set ordering
select id, 
       cast(lpad(parent, level*2, ' ') as varchar(20)) parent,
       level 
from   recur
order by ordering;

But ...NO....No luck there.

Second try: Hiearchical SQL
Another approach is using tha naticve hiearchical capabilities from the SQL implemantation:

select id, lpad(parent, level*2, ' ') parent, level
from   relations
start with id=0
connect by prior id=parent;

This didn't work at first. We first need to tell DB2 that it can use also these type of features:

db2set DB2_COMPATIBILITY_VECTOR=08
db2stop
db2start

Since I'm on my development environment, I can stop and start the database whenever it suits me 🙂

Following output was generated by running this query:

ID          PARENT           LEVEL      
----------- ---------------- -----------
          0 -                          1
          1    0                       2
          2      1                     3
          3      1                     3
          4        3                   4
         11      1                     3
          5    0                       2
          6      5                     3
          8        6                   4
          7      5                     3
          9        7                   4
         10    0                       2

Exactly what I want. So at least we have a solution.

(r)CTE revisited
I could not let go of the idea of CTE's so I posted on stackoverflow.com: Question.

"The Impaler" came up with the following:

with
n (id, parent, lvl, ordering) as (
  select id, parent, 1, lpad(id, 3, '0') || lpad('', 30, ' ')
  from relations
  where parent is null
 union all
  select r.id, r.parent, n.lvl + 1, trim(n.ordering) || '/' || lpad(r.id, 3, '0')
  from n, relations r where r.parent = n.id
)
select id, lpad(parent, lvl * 2, ' ') as parent, lvl, ordering
from n
order by ordering;

Which delivers:

ID          PARENT               LVL         ORDERING                         
----------- -------------------- ----------- ----------------------
          0 -                              1 000                              
          1    0                           2 000/001
          2      1                         3 000/001/002
          3      1                         3 000/001/003      
          4        3                       4 000/001/003/004
         11      1                         3 000/001/011
          5    0                           2 000/005        
          6      5                         3 000/005/006
          8        6                       4 000/005/006/008
          7      5                         3 000/005/007
          9        7                       4 000/005/007/009
         10    0                           2 000/010

Hehe that works like a charm!!!
Thank you