IBM Support

IT18136: INSERT QUERY THAT HAS A COLUMN VALUE GENERATED USING TRIGGER COULD PRODUCE WRONG RESULTS OR SQL0407N

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • There is a trigger on the table which will cause the column's
    value to get generated when the insert statement is executed.
    The insert query could return a wrong result (not generate
    values properly) or fail with SQL0407N .
    
    Steps to reproduce the SQl0407N error:
    create table test_null (col1 int not null, col2 int not null,
    col3 int not null ) IN TPNE_TXN_DT_TBS INDEX IN TPNE_BST_IDX_TBS
    
    CREATE OR REPLACE TRIGGER TEST_NULL_B1
      NO CASCADE BEFORE INSERT
        ON TEST_NULL
          REFERENCING
              NEW AS NEW
                FOR EACH ROW
                BEGIN ATOMIC
                    SET (NEW.col3) = ( SELECT COALESCE(MAX( col3),
    0) + 1 FROM test_null WHERE col1 = NEW.col1 AND col2 = NEW.col2)
    ;
                      END
    
    
    insert into test_null(col1, col2) values (1,1)
    commit
    
    
    $ db2 "insert into test_null(col1, col2) values (1,1)"
    DB21034E  The command was processed as an SQL statement because
    it was not a
    valid Command Line Processor command.  During SQL processing it
    returned:
    SQL0407N  Assignment of a NULL value to a NOT NULL column
    "TBSPACEID=3,
    TABLEID=4, COLNO=2" is not allowed.  SQLSTATE=23502
    

Local fix

  • Workaround is to rewrite the trigger. For example:
    CREATE OR REPLACE TRIGGER TEST_NULL_B1
        NO CASCADE BEFORE INSERT
        ON TEST_NULL
        REFERENCING NEW AS NEW
        FOR EACH ROW
        BEGIN ATOMIC
          DECLARE temp int;
          SET temp = ( SELECT COALESCE(MAX( col3), 0) + 1 FROM
    test_null WHERE col1 = NEW.col1 AND col2 = NEW.col2) ;
          set new.col3 = coalesce( temp, 1 );
        END;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 11.1.1 fix pack 2                             *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 11.1.1 fix pack 2
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT18136

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-11-28

  • Closed date

    2018-07-16

  • Last modified date

    2018-07-16

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

  • 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

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 June 2020