LI73537: DB2 SERVER CRASHES OR INSERTS INCORRECT VALUE WHEN USING DEFAULT VALUE FOR A NON-UPDATABLE VIEW

APAR status

• Closed as program error.

Error description

• For a view that could not be updated, INSTEAD OF triggers can be
  used to perform a delete, insert, or update request on behalf of
  a view. This problem occurs when all the conditions are met:
  
  1. When insert into a non updatable view and the definition of
  the associated INSTEAD OF INSERT trigger is used for the insert
  operation.
  
  2. The INSERT statement specifies only a subset of the columns
  of the view and those missing columns are to be substituted with
  the default value of the columns.
  
  DB2 server may crash or insert incorrect value.
  
  Example:
  
     create schema test;
  
     create table t1(c1 int with default -100,c2 int with default
  -200);
  
     create table t2(c1 int with default -101,c2 int);
  
     create view v1(t1c1,t2c1) as select t1.c1,t2.c1 from t1 left
                                  join t2 on t1.c1=t2.c1;
  
     create trigger trig_v1 instead of insert on v1 referencing
  new as n for each row mode db2sql insert into t1(c1) values
  (n.t1c1);
  
  (1)
  
     insert into v1(t1c1) values default;
  
     select * from t1;
  
     C1          C2
     ----------- -----------
               0        -200
  
       1 record(s) selected.
  
  There is no default value for the column T1C1 of the non
  updatable view V1. NULL should be used to substitute the default
  value. The value 0 is incorrectly used in the result shown
  above.
  
  (2)
  
     runstats on table test.t2 with distribution;
     insert into v1(t1c1) values default;
  
  After the runstats that includes collecting distribution
  statistics, DB2 server is triggered into a code path that could
  abnormally end with a SIGSEGV in the sqlvCopyIntegerToFloat()
  function.
  

Local fix

• Specify all of the columns of the view in the <column-name>
  clause of the INSERT statement.
  

Problem summary

• Users Affected:  ALL
  
  Problem Description:  Please refer to the 'ERROR DESCRIPTION'
  
  Problem Summary:  Please refer to the 'ERROR DESCRIPTION'
  

Problem conclusion

• Problem was first fixed in Version 8.2 FixPak 17.
  

Temporary fix

• see LOCAL FIX
  

Comments

APAR Information

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

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

  LI73541 LI73543

Fix information

• Fixed component name

  DB2 UDB EXE LIN

• Fixed component ID

  5724E4904

Applicable component levels

• R810 PSN

     UP

• R820 PSN

     UP

• R910 PSN

     UP

• R950 PSN

     UP