IBM Support

IT02239: SQL AGAINST A TABLE FAILS WITH ERROR SQL0901N , WHEN COMPLEX TRIGGERS/FUNCTIONS WITH MERGE ARE DEFINED ON THAT TABLE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • The specific conditions to hit this error are:
    -Any Update, insert, Delete or Merge SQL statement containing
    complex triggers may fail with error SQL0901N
    - a MERGE statement with a WHEN MATCHED clause and without WHEN
    NOT MATCHED clause.
    - a large number of triggers invoked or a large number of
    statements in the triggers or involved in some user defined
    function with a large number of statements
    - The MERGE statement can be either within the function /
    triggers or the top level statement being executed.
    
    Following example SQL shows the failure:
    $ db2  "delete from abc where ACTIONID=141"
    
    DB21034E  The command was processed as an SQL statement because
    it was not a
    valid Command Line Processor command.  During SQL processing it
    returned:
    SQL0901N  The SQL statement failed because of a non-severe
    system error.
    Subsequent SQL statements can be processed.  (Reason "RID=<EXP>,
    bad EXP".)
    SQLSTATE=58004
    

Local fix

  • Workarounds would include reducing the MERGE statement to
    underlying UPDATE or DELETE operation, since there is no WHEN
    NOT MATCHED clause, or simplifying the triggers / functions to
    reduce complexity.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Customer using DB2 using complex triggers or functions with  *
    * MERGE                                                        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V9.7fp10                                      *
    ****************************************************************
    

Problem conclusion

  • Fixed in V9.7 fp10
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT02239

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2014-06-05

  • Closed date

    2014-11-18

  • Last modified date

    2014-11-18

  • APAR is sysrouted FROM one or more of the following:

    IC95623

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R970 PSY

       UP

  • R980 PSN

       UP

  • R980 PSY

       UP

  • RA10 PSN

       UP

  • RA10 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IT02239

Modified date: 18 November 2014