IT13415: Query on Created Global Temporary Table MAY RETURN WRONG RESULTSIN DPF WHEN RUNNING THE SAME STATEMENT ON DIFFERENT PARTITIONS

DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• This problem only occurs in DPF environment and is further
  limited to cases where
  - queries involving Created Global Temporary Tables (CGTT)
  - connections at different partitions in a DPF environment
  - the Created Global Temporary Table is dropped and recreated
  while connections are active
  
  
  When a CGTT is instantiated in two or more connections at
  different partitions, there is a possibility that running the
  same SQL statement at different partitions could end up result
  in incorrect results if the CGTT is explicitly dropped and
  recreated while applications that have previously instantiated
  the CGTT are still connected.
  
  
  For example, consider the following sequence of events
  
  Connection 1 at partition 1:
  insert into GT select i from perm table ---> if GT is a CGTT,
  this statement will instantiate CGTT for connection 1.
  
  
  Connection 2:
  DROP TABLE GT
  CREATE GLOBAL TEMPORARY TABLE GT (i int) on commit preserve rows
  
  Connection 3 at partition 2:
  INSERT INTO GT VALUES 1,2,3,4,5
  
  Connection 1 at partition 1:
  INSERT INTO GT VALUES 1,2,3,4,5  --> same insert statement
  issued as issued by connection 3
  SELECT I FROM GT     // returns 0 records  --> incorrect result
  

Local fix

• Avoid dropping and recreating CGTT when connections are active.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * ALL                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 10.1 Fix Pack 6                               *
  ****************************************************************
  

Problem conclusion

• First fixed in DB2 10.1 Fix Pack 6
  

Temporary fix

Comments

APAR Information

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

  IT13352

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

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels