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 |
- 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'; |
