Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    34

    Unanswered: Sqlcode = 347, warning: The recursive common table expression

    DB2 v9 on z/OS

    recursive query ends with 347 but returns a row .. Can this be considered as a correct result, based on what is DB2 issuing this rc 347 ?


    DSNT404I SQLCODE = 347, WARNING: THE RECURSIVE COMMON TABLE EXPRESSION
    RECURSIVE MAY CONTAIN AN INFINITE LOOP
    DSNT418I SQLSTATE = 01605 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNXODML SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 0 0 25 1180905063 0 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000000' X'00000000' X'00000019' X'46632E67'
    X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
    COL1
    ---------+---------+---------+---------+---------+---------+---------
    8021991.
    DSNE610I NUMBER OF ROWS DISPLAYED IS 1
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
    DSNE617I COMMIT PERFORMED, SQLCODE IS 0

    query is like
    WITH RECURSIVE (COL1,COL2) AS (
    SELECT PARENT.COL1,PARENT.COL2 FROM TABLE1 PARENT
    WHERE PARENT.COL2 IN (8370577 ,
    8021991 )
    UNION ALL
    SELECT CHILD.COL1,PARENT.COL2 FROM RECURSIVE PARENT,
    TABLE1 CHILD WHERE CHILD.COL2 = PARENT.COL1)
    SELECT DISTINCT REC.COL2 FROM RECURSIVE REC,
    (SELECT COL1,COL2,NULLIF(1,1) AS
    COL3,COL4,COL4 FROM TABLE2
    UNION
    SELECT COL1,COL2,COL3,COL4,'N' AS COL4
    FROM TABLE3) ACT
    WHERE ACT.COL4=REC.COL1;

    when the IN list doesn't contain too much values, than we get sqlcode 347
    and a result, if there are too many values in the IN list, than the sqlcode 347 is followed by SQLCODE = -905, ERROR: UNSUCCESSFUL EXECUTION DUE TO RESOURCE LIMIT
    BEING EXCEEDED, RESOURCE NAME = ASUTIME LIMIT = 000000000258 CPU
    SECONDS (000010000000 SERVICE UNITS) DERIVED FROM SYSIBM.DSNRLST01

    if 347 isn't important, than we could try with explain plan to find out if there are indexes missing without increasing SYSIBM.DSNRLST01 .

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    did you notice in your message that it says "WARNING"? The results are fine, DB2 is just warning you that you could get an infinite loop with the way your SQL is written, but it would depend on your data if it actually does though.
    Dave

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    As far as hitting your RLF limits, with a larger IN list. I would think it has to do with having no WHERE clause on tables 2 & 3. You could maybe cut that down by adding the outer where clause into both sides of the union, to cut down how much data is being materialized there. Something like:
    Code:
    (SELECT COL1,COL2,NULLIF(1,1) AS 
    COL3,COL4,COL4 FROM TABLE2
    WHERE ACT.COL4=table2.COL1
    UNION 
    SELECT COL1,COL2,COL3,COL4,'N' AS COL4 
    FROM TABLE3
    WHERE ACT.COL4=table3.COL1) ACT 
    WHERE ACT.COL4=REC.COL1;
    Dave

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can see the following description in the manual "DB2 Version 9.1 for z/OS SQL Reference"
    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 of
    ″counter_col < constant″ or ″counter_col < :hostvar″.
    A warning is issued if this
    syntax is not found.
    So, you can supress the warning by including additional column(k is a counter_col in the following example).
    Code:
    WITH RECURSIVE (COL1, COL2, k) AS ( 
    SELECT PARENT.COL1, PARENT.COL2
         , 1
      FROM TABLE1 PARENT 
     WHERE PARENT.COL2 IN (8370577 , 8021991) 
    UNION ALL 
    SELECT CHILD.COL1, PARENT.COL2
         , k+1
      FROM RECURSIVE PARENT
         , TABLE1    CHILD
     WHERE CHILD.COL2 = PARENT.COL1
       AND k < 1000000000
    ) 
    SELECT DISTINCT REC.COL2
      FROM RECURSIVE REC
         , (SELECT COL1, COL2, NULLIF(1,1) AS COL3, COL4, COL4
              FROM TABLE2
            UNION 
            SELECT COL1, COL2, COL3, COL4, 'N' AS COL4 
              FROM TABLE3
           ) ACT 
     WHERE ACT.COL4 = REC.COL1
    ;

  5. #5
    Join Date
    Jul 2007
    Posts
    34
    Quote Originally Posted by tonkuma View Post
    You can see the following description in the manual "DB2 Version 9.1 for z/OS SQL Reference"


    So, you can supress the warning by including additional column(k is a counter_col in the following example).
    Code:
    WITH RECURSIVE (COL1, COL2, k) AS ( 
    SELECT PARENT.COL1, PARENT.COL2
         , 1
      FROM TABLE1 PARENT 
     WHERE PARENT.COL2 IN (8370577 , 8021991) 
    UNION ALL 
    SELECT CHILD.COL1, PARENT.COL2
         , k+1
      FROM RECURSIVE PARENT
         , TABLE1    CHILD
     WHERE CHILD.COL2 = PARENT.COL1
       AND k < 1000000000
    ) 
    SELECT DISTINCT REC.COL2
      FROM RECURSIVE REC
         , (SELECT COL1, COL2, NULLIF(1,1) AS COL3, COL4, COL4
              FROM TABLE2
            UNION 
            SELECT COL1, COL2, COL3, COL4, 'N' AS COL4 
              FROM TABLE3
           ) ACT 
     WHERE ACT.COL4 = REC.COL1
    ;

    Thanks for the reply .

Posting Permissions

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