IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    IT13415

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-01-25

  • Closed date

    2017-03-02

  • Last modified date

    2017-03-02

  • 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

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1"}]

Document Information

Modified date:
25 September 2021