How to get rid of "warning" messages in db2cmd?

grofaty · #1 (**permalink**) 07-22-10, 03:38

Hi,
using db2 v9.5 on Linux I have written recursive SQL, that is under control and never goes into infinitive loop. But SQL always returns warning message at the top of output:

"SQL0347W The recursive common table expression "ADMIN.TEMP" may contain an
infinite loop. SQLSTATE=01605".

How to get rid of this warning message in db2cmd? Like turning off warnings?

Thanks

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

I don't know wheather there is a way to turn off the warning.
But, you can suppress SQL0347W warning message by including some code(or specific syntax) to your recursive SQL.

Sqlcode = 347, warning: The recursive common table expression

Note: The description in the manual wrote an integer column.
Not bigint, nor smallint.

tonkuma · #3 (**permalink**) 07-22-10, 08:21

Example 1(No warning):

Code:

------------------------------ Commands Entered ------------------------------
WITH r(k) AS (
VALUES 1
UNION ALL
SELECT k + 1
  FROM r
 WHERE k < 3
)
SELECT * FROM r
;
------------------------------------------------------------------------------

K          
-----------
          1
          2
          3

  3 record(s) selected.

Example 2(Warning SQL0347W):

Code:

------------------------------ Commands Entered ------------------------------
WITH r(k) AS (
VALUES SMALLINT(1)
UNION ALL
SELECT k + 1
  FROM r
 WHERE k < 3
)
SELECT * FROM r
;
------------------------------------------------------------------------------

K     
------
SQL0347W  The recursive common table expression "DB2ADMIN.R" may contain an 
infinite loop.  SQLSTATE=01605

     1
     2
     3

  3 record(s) selected with 1 warning messages printed.

Example 3(Warning SQL0347W):

Code:

------------------------------ Commands Entered ------------------------------
WITH r(k) AS (
VALUES BIGINT(1)
UNION ALL
SELECT k + 1
  FROM r
 WHERE k < 3
)
SELECT * FROM r
;
------------------------------------------------------------------------------

K                   
--------------------
SQL0347W  The recursive common table expression "DB2ADMIN.R" may contain an 
infinite loop.  SQLSTATE=01605

                   1
                   2
                   3

  3 record(s) selected with 1 warning messages printed.

grofaty · #4 (**permalink**) 07-23-10, 01:39

tonkuma,
example 1 solved my problem.
Thanks a lot.

Internet Services

Web Development

Digital Marketing

Consumer Tech