Recursive SQL In DB2

JN2010 · #1 (**permalink**) 07-16-10, 05:55

Hi,

I'm having problem to produce one SQl in DB2 as i'm novice into it.

Let's consider the sample input data as follows -

Sample Query -

Code:

Select     LINKS.AUDITABLE_LINK.TARGET_REF_REFERENCED_ITM_TM_D,
             LINKS.AUDITABLE_LINK.SOURCE_REF_REFERENCED_ITM_TM_D
From  LINKS.AUDITABLE_LINK;

And, the output is as follows (Also this is my initial input sample data) -

Code:

TARGET_REF_REFERENCED_ITM_TM_D     SOURCE_REF_REFERENCED_ITM_TM_D
_juEr8G7iEd-9NLREWheBlA	                  _hL9UgG7iEd-9NLREWheBlA
_VY7zUG-bEd-0e-e4Zg0prg	                  _VgcJgG-bEd-0e-e4Zg0prg
_hL9UgG7iEd-9NLREWheBlA	                  _VY7zUG-bEd-0e-e4Zg0prg

And, my required output is -

Code:

TARGET_REF_REFERENCED_ITM_TM_D    SOURCE_REF_REFERENCED_ITM_TM_D
_juEr8G7iEd-9NLREWheBlA	                  _hL9UgG7iEd-9NLREWheBlA
_hL9UgG7iEd-9NLREWheBlA	                  _VY7zUG-bEd-0e-e4Zg0prg
_VY7zUG-bEd-0e-e4Zg0prg	                  _VgcJgG-bEd-0e-e4Zg0prg

As, you can see that in my output, i've arranged all the records based on the parent -child relationship where TARGET_REF_REFERENCED_ITM_TM_D (parent) & SOURCE_REF_REFERENCED_ITM_TM_D (Child).

Kindly let me know the possible way to solve this problem. Thanks for your time.

Regards.

tonkuma · #2 (**permalink**) 07-16-10, 07:58

One way is to use recursive query, like this:

(I abbreviated column names to see easily.)

Code:

------------------------------ Commands Entered ------------------------------
WITH
/************************************************************
********** Begin of sample data                    **********
************************************************************/
 AUDITABLE_LINK(target_d , source_d) AS (
VALUES
  ('_juEr8G7iEd-9NLREWheBlA' , '_hL9UgG7iEd-9NLREWheBlA')
, ('_VY7zUG-bEd-0e-e4Zg0prg' , '_VgcJgG-bEd-0e-e4Zg0prg')
, ('_hL9UgG7iEd-9NLREWheBlA' , '_VY7zUG-bEd-0e-e4Zg0prg')
)
/************************************************************
**********   End of sample data                    **********
************************************************************/
, numbering_to_child
  (  root , target_d , source_d , k ) AS (
SELECT target_d
     , target_d , source_d
     , 0
  FROM AUDITABLE_LINK r
 WHERE NOT EXISTS
       (SELECT 0
          FROM AUDITABLE_LINK s
         WHERE s.source_d
             = r.target_d
      )
UNION ALL
SELECT p.root
     , c.target_d , c.source_d
     , k + 1
  FROM numbering_to_child p
     , AUDITABLE_LINK     c
 WHERE k < 1000
   AND c.target_d
     = p.source_d
)
SELECT target_d , source_d
  FROM numbering_to_child
 ORDER BY
       root
     , k
;
------------------------------------------------------------------------------

TARGET_D                SOURCE_D               
----------------------- -----------------------
_juEr8G7iEd-9NLREWheBlA _hL9UgG7iEd-9NLREWheBlA
_hL9UgG7iEd-9NLREWheBlA _VY7zUG-bEd-0e-e4Zg0prg
_VY7zUG-bEd-0e-e4Zg0prg _VgcJgG-bEd-0e-e4Zg0prg

  3 record(s) selected.

JN2010 · #3 (**permalink**) 07-19-10, 04:18

Thanks for your solution. But, still i've one more question.

Code:

k < 1000

What is the purpose of this snippet? Can you please explain?

Regards.

umayer · #4 (**permalink**) 07-19-10, 04:35

it avoids an endless-loop ( eg. if a row exists where SOURCE_D = TARGET_D )

tonkuma · #5 (**permalink**) 07-19-10, 05:38

Even if you know your data doesn't include an endless-loop, DB2 will issue a warning message.
The snippet will suppress the warning message.

Here is an example on DB2 for z/OS.
Sqlcode = 347, warning: The recursive common table expression

You can find similar description on "DB2 Version 9 for Linux, UNIX, and Windows SQL Reference Volume 1".

Quote:

When developing recursive common table expressions, remember that an infinite
recursion cycle (loop) can be created. Check that recursion cycles will terminate.
This is especially important if the data involved is cyclic. A recursive common
table expression is expected to include a predicate that will prevent an infinite
loop. The recursive common table expression is expected to include:
v In the iterative fullselect, an integer column incremented by a constant.
v A predicate in the where clause of the iterative fullselect in the form
″counter_col < constant″ or ″counter _col < :hostvar″.

A warning is issued if this syntax is not found in the recursive common table
expression (SQLSTATE 01605).

rocker86 · #6 (**permalink**) 03-20-11, 04:29

I was going through the IBM link http://publib.boulder.ibm.com/infoce...ivequeries.htm

where right at the bottom it mentions a cool 'CYCLE' clause to avoid infinite loops. However, My DB2 (V 9.7) does not seem to support this

Any idea what is the minimum version needed to use this extremely helpful clause?

thanks

tonkuma · #7 (**permalink**) 03-20-11, 09:00

CYCLE and SEARCH DEPTH FIRST/BREADTH FIRST are supported from DB2 for i5/OS Version 5 Release 4.

AFAIK, they are not supported DB2 (9.7) for LUW nor DB2 (10) for z/OS.

Internet Services

Web Development

Digital Marketing

Consumer Tech