JR26726: DECLARE GLOBAL TEMPORARY TABLE CAN FAIL ON ROLLBACK WITH SQLIUNDODELKEY PROBE 206

DB2 Version 9.1 Fix Pack 4a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 4 for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 5   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6   for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 6a for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 7a  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 8  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 9  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 10  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 11  for Linux, UNIX and Windows
DB2 Version 9.1 Fix Pack 12  for Linux, UNIX and Windows

APAR status

• Closed as program error.

Error description

• The problem here is specific to not logged DGTT. The database
  will be brought down during ROLLBACK if you perform the
  following sequence of actions:
  
  0. turn off auto commit
  1. declare DGTT table t1 NOT LOGGED
  2. create index i1 on t1
  3. insert into t1
  4. create index i2 on t1
  5. drop index i2
  6. delete from t1
  7. insert into t1
  8. create index i2
  9. rollback work -> hit the undo failure
  The rollback will fail with sqliUndoDelKey, probe:206 which
  brings down the database and instance.
  
  Other variations of the problem include:
  Scenario 1:
  0. turn off auto commit
  1. declare DGTT table t1 NOT LOGGED
  2. create index i1 on t1
  3. insert into t1
  4. create index i2 on t1
  5. drop index i2
  6. delete from t1
  7. insert into t1
  8. rollback work -> hit the undo failure
  
  Scenario 2:
  0. turn off auto commit
  1. declare DGTT table t1 NOT LOGGED
  2. create index i1 on t1
  3. insert into t1
  4. create index i2 on t1
  5. drop index i2
  6. delete from t1
  7. insert into t1
  8. create index i2
  9. drop index i2
  10. rollback work -> hit the undo failure
  
  Scenario 1 will fail with the same sqliUndoDelKey, probe:206.
  And scenario 2 will fail with sqliUndoDelKey, probe:697.
  Problem should go away with the crash recovery.
  Possible db2diag.log entries look like:
  2007-05-16-11.26.15.703000-240 I9559H482 LEVEL: Severe
  PID : 2236 TID : 2364 PROC :
  db2syscs.exe
  INSTANCE: DB2 NODE : 000 DB : BUH
  APPHDL : 0-29 APPID:
  172.17.192.92.12814.07051615230
  AUTHID : DB2ADMIN
  FUNCTION: DB2 UDB, index manager, sqliUndoDelKey, probe:206
  RETCODE : ZRC=0x87090054=-2029453228=SQLI_PRG_ERR "Program
  error"
  DIA8575C An index manager programming error occurred.
  

Local fix

• N/A
  Defect: wsdbu00193887
  

Problem summary

• DECLARE GLOBAL TEMP TABLE CAN FAIL ON ROLLBACK WITH
  SQLIUNDOODELKEY PROBE 206
  

Problem conclusion

• First fixed in DB2 UDB Version 9.1, FixPak 4
  

Temporary fix

Comments

APAR Information

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

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

Fix information

• Fixed component name

  DB2 UDB EXE WIN

• Fixed component ID

  5724E4901

Applicable component levels

• R910 PSY

     UP