PI39512: INCORRECT OUTPUT AFTER DELETE ON BITEMPORAL TABLE WITH INDEX OR NLIST ACCESS PATH

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• The DELETE removes the row. So, we should INSERT a row with the
  piece remaining for  the time duration. But, the INSERT into
  base table is not generated.
  

Local fix

• n/a
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of      *
  *                 application-period temporal tables.          *
  ****************************************************************
  * PROBLEM DESCRIPTION: A delete from an application-period     *
  *                      temporal table can delete an entire row *
  *                      although the statement specified a      *
  *                      period-clause.                          *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  An incorrect result can be returned for a query that references
  a temporal table following a DELETE operation against it that
  contains an IN-List and uses IN-List access.
  
  The following example helps to illustrate a failing case.
  
  Step 1. Create a temporal table.
  
    CREATE TABLE INFO (
     INFO_ID DECIMAL(11, 0) NOT NULL
    ,INFO_STARTDATE DATE NOT NULL
    ,INFO_ENDDATE DATE NOT NULL
     ...
    ,PERIOD BUSINESS_TIME(INFO_STARTDATE, INFO_ENDDATE)
    ,PRIMARY KEY (INFO_ID,
     BUSINESS_TIME WITHOUT OVERLAPS) ) ;
  
  
  Step 2. Insert a row of data into the table.
  
    INSERT INTO  INFO
       VALUES(1234567.,'2014-03-01','2014-09-01',...);
  
  So, we start with the INFO Table containing a single row.
  
      +----------------------------------------------
      | INFO_ID | INFO_STARTDATE | INFO_ENDDATE   |
      +----------------------------------------------
    1_| 1234567 | 2014-03-01     | 2014-09-01     |
      +----------------------------------------------
  
  
  Step 3. Create an additional table and insert a row of data.
  
    CREATE TABLE INFO_CTL (
          INFO_ID DECIMAL(11, 0) NOT NULL
          ...
          , PRIMARY KEY (INFO_ID) );
  
    INSERT INTO  INFO_CTL VALUES(1234567., ... );
  
  
  Step 4. Perform the following DELETE against the temporal table
  that contains an IN-List.
  
    DELETE FROM INFO FOR PORTION OF BUSINESS_TIME FROM
       '2014-06-03' TO '2014-08-15'
      WHERE INFO_ID IN (SELECT B.INFO_ID FROM INFO_CTL B) ;
  
  
  Step 5. Check the results of the temporal table.
  
    SELECT * FROM INFO
    WHERE INFO_ID IN(
      SELECT B.INFO_ID
      FROM INFO_CTL B,
      ...
      WHERE ...    );
  
  This query incorrectly returns 0 rows.
  
  
  The expected results should be two rows as follows.
  
     +-----------------------------------------------
     | INFO_ID  | INFO_STARTDATE | INFO_ENDDATE   |
     +-----------------------------------------------
   1_|  1234567 | 2014-03-01     | 2014-06-03     |
   2_|  1234567 | 2014-08-15     | 2014-09-01     |
     +-----------------------------------------------
  
  The problem occurs when performing the DELETE operation against
  the temporal table with IN-List access.  DB2 is treating the
  'delete' as a normal table delete instead of a temporal table
  delete.
  
  Please note:  This problem was reported when the INFO Table has
  an access type of "IN-List" for the DELETE operation.  Other
  incorrect results can occur depending on how the 'deleted
  portion' brackets the version dates.  Similar problems can occur
  when performing an UPDATE instead of a DELETE.
  

Problem conclusion

• The code in DB2 bind time is modified to correct the problem
  when deleting from or updating a temporal table when using
  IN-List access.  This will allow the proper result to be
  returned when querying the temporal table following a delete
  from the table.
  
  Additional Keywords: SQLINCORR INCORROUT SQLINCORROUT
                       DB2INCORR/K SQLBITEMPORAL SQLDELETE
                       SQLUPDATE SQLINLIST SQLINSUBQ
                       SQLTEMPORAL
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UI27781 UI27782

Modules/Macros

• DSNXGRTM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI27781

     UP15/06/04 P F506

• RB10 PSY UI27782

     UP15/06/04 P F506

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.