Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: Triggers and SQLSTATE 428F9

    Hello,

    I have problem with triggers. There are two triggers: before (abi) and after (aai) insert. The signalled error is in aai with tokens from abi Both triggers should work separately and should know nothing about each other. I know there are some exceptions when NEXT VALUE cannot be used but it seems it does not cover my problem. What is wrong and how to solve it then?
    (environment: db2 9.5 with no patches on Linux)

    create table a (i integer)!
    create table b (i integer)!
    CREATE SEQUENCE sq AS integer!
    CREATE TRIGGER abi BEFORE INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    SET N.i = NEXTVAL FOR sq;
    END!
    CREATE TRIGGER aai AFTER INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL
    BEGIN ATOMIC
    insert into b (i)
    WITH x(xi) AS (values N.i UNION ALL SELECT xi+1 FROM x WHERE xi<N.i+10)
    SELECT xi FROM x;
    END!
    insert into a values (0)!

    Error: SQL0723N An error occurred in a triggered SQL statement in trigger "DB2INST1.AAI". Information returned for the error includes SQLCODE "-348", SQLSTATE "428F9" and message tokens "NEXTVAL FOR DB2INST1.SQ"

    If we replace a recursive with simple "insert into b (i) values (n.i);" or remove any trigger it will be working properly. Unfortunately on production I have to have a sequence call before and recursive query after insert.
    Last edited by bigmickey; 11-10-09 at 09:09.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you can try to look at the INSERT explain plan to see what is it that DB2 is trying to execute.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    Unfortunately the explain plan in such situation is not accessible. I've got the same error.
    Any other ideas? Is it known problem? Maybe it is an additional nonsensical exception in DB2 making the programmers to be cross?

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Appears to be the way it will/should work ... Sequences are not supported in recursive CTEs.

    Open a PMR and confirm with IBM ...

    The workaround is to use a Stored Proc in the After Trigger

    Cheers
    Sathyaram
    Last edited by sathyaram_s; 11-12-09 at 07:46.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It looks worked well on my DB2 9.7 for Windows.

    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.0
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    create table a (i integer)!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    create table b (i integer)!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE SEQUENCE sq AS integer!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER abi BEFORE INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC
    SET N.i = NEXTVAL FOR sq;
    END!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER aai AFTER INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC 
    insert into b (i)
    WITH x(xi) AS (values N.i UNION ALL SELECT xi+1 FROM x WHERE xi<N.i+10) 
    SELECT xi FROM x;
    END!
    ------------------------------------------------------------------------------
    SQL0347W  The recursive common table expression "DB2ADMIN.X" may contain an 
    infinite loop.  SQLSTATE=01605
    
    ------------------------------ Commands Entered ------------------------------
    insert into a values (0)!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM a!
    ------------------------------------------------------------------------------
    
    I          
    -----------
              1
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM b!
    ------------------------------------------------------------------------------
    
    I          
    -----------
              1
              2
              3
              4
              5
              6
              7
              8
              9
             10
             11
    
      11 record(s) selected.

  6. #6
    Join Date
    Nov 2009
    Posts
    3
    Thank you to both of you. The workaround is working and as for now it's the only solution in my case.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    That's interesting ... Can you post the explain plan please, if at all possible ...



    Quote Originally Posted by tonkuma View Post
    It looks worked well on my DB2 9.7 for Windows.

    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.0
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    create table a (i integer)!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    create table b (i integer)!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE SEQUENCE sq AS integer!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER abi BEFORE INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC
    SET N.i = NEXTVAL FOR sq;
    END!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CREATE TRIGGER aai AFTER INSERT ON a REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL 
    BEGIN ATOMIC 
    insert into b (i)
    WITH x(xi) AS (values N.i UNION ALL SELECT xi+1 FROM x WHERE xi<N.i+10) 
    SELECT xi FROM x;
    END!
    ------------------------------------------------------------------------------
    SQL0347W  The recursive common table expression "DB2ADMIN.X" may contain an 
    infinite loop.  SQLSTATE=01605
    
    ------------------------------ Commands Entered ------------------------------
    insert into a values (0)!
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM a!
    ------------------------------------------------------------------------------
    
    I          
    -----------
              1
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM b!
    ------------------------------------------------------------------------------
    
    I          
    -----------
              1
              2
              3
              4
              5
              6
              7
              8
              9
             10
             11
    
      11 record(s) selected.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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