DB2 FOR LUW / DB2FORLUW / A10 - DB2

Update of table with special columns, ROW BEGIN, ROW END, 
TRANSACTION START ID or ROW CHANGE TIMESTAMP that is indexed 
will cause update loop and fill logs. 
 
eg. 
CREATE TABLE "DB2INST1"."MYTAB"  ( 
          "ROWID" BIGINT NOT NULL , 
          "DEFN_ROWID" BIGINT NOT NULL , 
          "DELETED_FLAG" CHAR(1) NOT NULL WITH DEFAULT 'N' , 
          "CHANGE_TS" TIMESTAMP NOT NULL GENERATED BY DEFAULT 
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP , 
          "TIME_RAISED" TIMESTAMP , 
          "ENTITY_TYPE" SMALLINT , 
          "ENTITY_VALUE" VARCHAR(20) , 
          "MESSAGE" VARCHAR(255) , 
          "ALERT_STATE" SMALLINT , 
          "OWNER_ROLE_ID" CHAR(3) , 
          "OWNER_ROLE_INSTANCE" SMALLINT ) 
         IN "USERSPACE" ; 
 
-- DDL Statements for Indexes on Table "DB2INST1"."MYTAB" 
CREATE INDEX "DB2INST1"."MYTAB_IDX01" ON "DB2INST1"."MYTAB" 
        ("CHANGE_TS" ASC, 
         "DELETED_FLAG" ASC) 
        COMPRESS NO ALLOW REVERSE SCANS; 
 
Using the above table and then look to perform the following: 
 
UPDATE DB2INST1.MYTAB 
   SET DEFN_ROWID = 5094, 
       MESSAGE = 'Uh Oh' 
 WHERE ( ( ALERT_STATE IS NULL ) OR (ALERT_STATE NOT IN (2,3) ) 
) 
   AND ENTITY_TYPE = 0 
   AND ENTITY_VALUE = '' 
   AND DELETED_FLAG = 'N' 
   AND DEFN_ROWID IN ( 5094, 5095 ) 
   AND ((OWNER_ROLE_ID IS NULL ) OR ( OWNER_ROLE_ID = 'MC' 
   AND OWNER_ROLE_INSTANCE = 1 )); 
 
If we use the Index to perform the above UPDATE, it can result 
in the 
following error : 
 
DB21034E  The command was processed as an SQL statement because 
it was not a 
valid Command Line Processor command.  During SQL processing it 
returned: 
SQL0964C  The transaction log for the database is full. 
SQLSTATE=57011

**************************************************************** 
* USERS AFFECTED:                                              * 
* All Platforms                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 LUW v10.1 Fixpack 5 or Later                  * 
****************************************************************

- Drop the index associated with the special column. 
- The user could use an optimizer profile to force a difference 
index access or TBSCAN. 
- The user could rewrite the update using full-select syntax to 
include the affected temporal column something like: 
 UPDATE ( 
    SELECT DEFN_ROWID, MESSAGE, CHANGE_TS 
      FROM dB2INST1.MYTAB 
     WHERE ( 
             ( ALERT_STATE IS NULL ) 
          OR (ALERT_STATE NOT IN (2, 3) ) 
           ) 
       AND ENTITY_TYPE         = 0 
       AND ENTITY_VALUE        = '' 
       AND DELETED_FLAG        = 'N' 
       AND DEFN_ROWID IN ( 5094, 5095 ) 
       AND ( 
             (OWNER_ROLE_ID IS NULL ) 
          OR ( 
               OWNER_ROLE_ID       = 'MC' 
           AND OWNER_ROLE_INSTANCE = 1 
             ) 
           ) 
       ) 
   SET DEFN_ROWID = 5094, 
       MESSAGE    = 'Uh Oh';

First Fixed in DB2 LUW v10.1 Fixpack 5

not known / see Local fix

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

19.08.2014
13.07.2015
13.07.2015