warning message on recursive select

sergee · #1 (**permalink**) 02-15-04, 13:57

Hi all!

i have a problem:

i'm using DB2 v7.2

when i trying to perfom recursive query from DB2V72CK.PDF - DB2 CookBook (page 203 - Stop After "n" Levels):

Code:

WITH PARENT (CKEY, LVL) AS
  (SELECT DISTINCT PKEY, 0
      FROM TROUBLE
      WHERE PKEY = 'AAA'
      UNION ALL

      SELECT C.CKEY, P.LVL +1
      FROM TROUBLE C,PARENT P
      WHERE P.CKEY = C.PKEY
      AND P.LVL+1 < 4
   )
SELECT CKEY, LVL
FROM PARENT;

DB2 Raises warning

Code:

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

That's Ok when i practicing from db2cctr,
BUT! when i try to perfom this query from perl script using DBD::DB2 via DBI
DBD treat this warning message as error, and fails to prepare this query, so i can't execute it and fetch some result.

PHP Code:


			
$dbh = DBI->Connect($dsn,$user,$password);

$sth = $dbh->prepare($sql_query);

$sth->execute;

I've read here: http://webdocs.caspur.it/ibm/web/udb...m0/sql0300.htm

Quote:

This warning is based on not finding specific syntax as part of the iterative portion of the recursive common table expression. The expected syntax includes:

* incrementing an INTEGER column in the iterative select list by 1.

* a predicate in the where clause of the iterative portion of the form "counter_col < constant" or "counter_col < :hostvar".

The absence of this syntax in the recursive common table expression may result in an infinite loop. The data or some other characteristic of the recursive common table expression may allow the successful completion of the statement anyway.

But those query already includes both.
How can i turn off this warning? Are there any quiet/silenced mode?

Help me please.
Will be very thankfull for any help.

------------------------------------------------------------------
Additional information:

Code:

CREATE TABLE TROUBLE
(PKEY CHAR(03) NOT NULL
,CKEY CHAR(03) NOT NULL
,CONSTRAINT TBX1 PRIMARY KEY(PKEY, CKEY)
,CONSTRAINT TBC1 CHECK (PKEY <> CKEY)
,CONSTRAINT TBC2 CHECK (LOCATE('>',PKEY)=0)
,CONSTRAINT TBC3 CHECK (LOCATE('>',CKEY)=0));
CREATE UNIQUE INDEX TBLE_X1 ON TROUBLE
(CKEY, PKEY);
INSERT INTO TROUBLE VALUES
('AAA','BBB'),
('AAA','CCC'),
('AAA','DDD'),
('CCC','EEE'),
('DDD','AAA'),
('DDD','EEE'),
('DDD','FFF'),
('FFF','GGG');

jsander · #2 (**permalink**) 02-16-04, 04:41

Hi,

I never had this problem with recursive queries from Perl/DBI/DBD, probably because I use statements like

$r = $dbh->fetchall_arrayref( $sql_query );

If you can do it this way but not the other, via statement handles, you should file a pmr with IBM.

Another option is to play around with CLI settings in your db2cli.ini file, e.g. IGNOREWARNINGS or IGNOREWARNLIST. Please read up on this in the CLI Guide and Reference.

Johann

Quote:

Originally posted by sergee
Hi all!

i have a problem:

i'm using DB2 v7.2

when i trying to perfom recursive query from DB2V72CK.PDF - DB2 CookBook (page 203 - Stop After "n" Levels):

Code:

WITH PARENT (CKEY, LVL) AS
  (SELECT DISTINCT PKEY, 0
      FROM TROUBLE
      WHERE PKEY = 'AAA'
      UNION ALL

      SELECT C.CKEY, P.LVL +1
      FROM TROUBLE C,PARENT P
      WHERE P.CKEY = C.PKEY
      AND P.LVL+1 < 4
   )
SELECT CKEY, LVL
FROM PARENT;

DB2 Raises warning

Code:

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

That's Ok when i practicing from db2cctr,
BUT! when i try to perfom this query from perl script using DBD:

B2 via DBI
DBD treat this warning message as error, and fails to prepare this query, so i can't execute it and fetch some result.

PHP Code:


			
$dbh = DBI->Connect($dsn,$user,$password);

$sth = $dbh->prepare($sql_query);

$sth->execute;

I've read here: http://webdocs.caspur.it/ibm/web/udb...m0/sql0300.htm

But those query already includes both.
How can i turn off this warning? Are there any quiet/silenced mode?

Help me please.
Will be very thankfull for any help.

------------------------------------------------------------------
Additional information:

Code:

CREATE TABLE TROUBLE
(PKEY CHAR(03) NOT NULL
,CKEY CHAR(03) NOT NULL
,CONSTRAINT TBX1 PRIMARY KEY(PKEY, CKEY)
,CONSTRAINT TBC1 CHECK (PKEY <> CKEY)
,CONSTRAINT TBC2 CHECK (LOCATE('>',PKEY)=0)
,CONSTRAINT TBC3 CHECK (LOCATE('>',CKEY)=0));
CREATE UNIQUE INDEX TBLE_X1 ON TROUBLE
(CKEY, PKEY);
INSERT INTO TROUBLE VALUES
('AAA','BBB'),
('AAA','CCC'),
('AAA','DDD'),
('CCC','EEE'),
('DDD','AAA'),
('DDD','EEE'),
('DDD','FFF'),
('FFF','GGG');

Internet Services

Web Development

Digital Marketing

Consumer Tech