DB2 FOR LUW / DB2FORLUW / B10 - DB2

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

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

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;

Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

First fixed in DB2 11.1.1 fix pack 2

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

28.11.2016
16.07.2018
16.07.2018