IBM Support

IC89862: SQL0901N WHEN INSERTING INTO TABLE WITH A TRIGGER CALLING MQ UDF

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • SQL0901N could be raised for inserting into a table with a
    trigger which is calling MQ UDF.  This may occur after adding a
    column
    to the subject table.  The problem may also happen if
    the trigger is calling other UDF that can not process
    expressions as input parameter.
    
    1. create a trigger on table, calling MQ wrapper function
    MQSEND.
    
    CREATE OR REPLACE TRIGGER FFFFFFFFF
        AFTER INSERT ON TTTTTTT
        REFERENCING? NEW_TABLE AS NEWT
        FOR EACH STATEMENT
    BEGIN ATOMIC SELECT DB2MQ1C.MQSEND (DIGITS (NEWT.C1) CONCAT
    ':ADD') FROM NEWT ; --
    END;
    
    2. Conduct an insert, which would fire the trigger, intially
    this will not cause error
    
    INSERT INTO TTTTTTT(C1) VALUES (987654322);
    
    
    3. Alter table by adding columns
    
    ALTER TABLE TTTTTTT ADD COLUMN C2 CHARACTER(20) NOT NULL DEFAULT
    ' '
    
    
    4. The table-alter operation could cause incorrect internal
    format of the query and trigger to be changed, which in turn
    causes the query to hit the limitation of DB2MQ1C.MQSEND that
    can not process expressions as input parameter
    
    INSERT INTO TTTTTTT(C1) VALUES (987654322)
    
    DB21034E? The command was processed as a 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 "Internal error: invalid request@MQ_UnfencedServer
    ::get_value".)  SQLSTATE=58004
    

Local fix

  • Recreate the trigger can bypass the error.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * The user is running MQ UDF.                                  *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 V97FP9.                                       *
    ****************************************************************
    

Problem conclusion

  • The problem is firstly fixed on DB2  V97FP9.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC89862

  • 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

    2013-01-25

  • Closed date

    2013-12-16

  • Last modified date

    2013-12-16

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

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

    IC89970 IC96486 IC96707

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC89862

Modified date: 16 December 2013