Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    1

    Unanswered: warning message on recursive select

    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

    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');
    Last edited by sergee; 02-15-04 at 15:05.

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: warning message on recursive select

    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

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

Posting Permissions

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