IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 'ERROR DESCRIPTION'
    
    Problem Summary:  Please refer to 'ERROR DESCRIPTION'
    

Problem conclusion

  • Problem was first fixed in Version 9.5 Fix Pack 3.
    

Temporary fix

  • see LOCAL FIX
    

Comments

APAR Information

  • APAR number

    LI73543

  • Reported component name

    DB2 UDB EXE LIN

  • Reported component ID

    5724E4904

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-06-25

  • Closed date

    2009-04-24

  • Last modified date

    2009-04-24

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

    LI73537

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

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

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
24 April 2009