DB2 - Problembeschreibung
Problem IT02543 | Status: Geschlossen |
UPDATE OF TABLE WITH SPECIAL COLUMN THAT IS INDEXED CAN RESULT IN SQL0964C CONDITION | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
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 | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All Platforms * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 LUW v10.5 Fixpack 3 or Later * **************************************************************** | |
Local-Fix: | |
- 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'; | |
verfügbare FixPacks: | |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows | |
Lösung | |
First Fixed in DB2 LUW v10.5 Fixpack 3 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 13.06.2014 15.12.2014 15.12.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.4 |