Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: Recursive SQL In DB2

    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.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  3. #3
    Join Date
    Jul 2010
    Posts
    2
    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.

  4. #4
    Join Date
    Dec 2005
    Posts
    273
    it avoids an endless-loop ( eg. if a row exists where SOURCE_D = TARGET_D )

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    http://www.dbforums.com/db2/1658407-...xpression.html

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

    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).

  6. #6
    Join Date
    Jul 2009
    Posts
    44
    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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •