Sqlcode = 347, warning: The recursive common table expression

swiss01 · #1 (**permalink**) 07-14-10, 03:17

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 .

dav1mo · #2 (**permalink**) 07-14-10, 07:43

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

dav1mo · #3 (**permalink**) 07-14-10, 07:50

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

tonkuma · #4 (**permalink**) 07-14-10, 08:22

You can see the following description in the manual "DB2 Version 9.1 for z/OS SQL Reference"

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 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
;

swiss01 · #5 (**permalink**) 07-15-10, 10:21

Quote:

Originally Posted by tonkuma

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 .

Internet Services

Web Development

Digital Marketing

Consumer Tech