IBM Support

PM91165: DB2 DBAT INCORRECTLY ALLOWED TO GO INACTIVE AND POOLED WHILE HAVING ACTIVE DGTTS WHEN ATTRIBUTE ON COMMIT DROP TABLE USED

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A DB2 DBAT is incorrectly allowed to go inactive and to be
    pooled while having active DGTTs, where the DB2 thread had used
    a mix of DGTTs defined as ON COMMIT DROP TABLE and DGTTs defined
    as not.
    This can result in a new user inheriting  DGTTs  that were left
    active by this prior DBAT. This new user may have no active DGTT
    usage but as a result of inheriting the prior DBAT DGTT usage,
    the new user may not be allowed to go inactive leading to
    unexpected DBAT status and related conditions.
    .
    The problem ONLY occurs when the DBAT has a mix of DGTTs defined
    with ON COMMIT DROP TABLE and without.
    .
    Potential symptoms may include:
    o Over utilization of DBATs, perhaps leading to an erroneous
      MAXDBAT limit reached condition.
    
    o DSNL027I/DSNL028I message condition with a 00D3003B
      (Idle Thread Time-out exceeded) reason code for a DBAT that
      has no active DGTT usage
    
    o A quiesce stop of DDF may not complete due to the erroneous
      presence of an active DBAT. A -STOP DDF MODE(FORCE) may be
      necessary in order for DDF to complete its stop processing.
    
    
    Additional keywords and symptoms:
    *********************************
    DB2DDF DB2INACTIVE
    00D3003B SQLDGTT ACEDBDTT
    

Local fix

  • If applicable, either use ON COMMIT DROP TABLE for all of DGTTs
    defined in applications run on that DBAT, or don't use ON COMMIT
    DROP TABLE but instead explicitly SQL DROP TABLE for the DGTTs.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All Distributed Data Facility ( DDF ) users  *
    *                 of Declared Global Temporary Tables (DGTTs)  *
    *                 where DB2 is configured with                 *
    *                 DDF THREADS=INACTIVE specified ( DSN6FAC     *
    *                 CMTSTAT INACTIVE )                           *
    ****************************************************************
    * PROBLEM DESCRIPTION: A user of a DB2 for z/OS remote server  *
    *                      thread ( DBAT ) inherits Declared       *
    *                      Global Temporary Tables ( DGTTs ) that  *
    *                      were created by a previous user of      *
    *                      this same DBAT, and the current user    *
    *                      of the DBAT encounters the following    *
    *                      unexpected DBAT symptoms and conditions *
    *                      due to those 'inherited' DGTTs:         *
    *                                                              *
    *                       o Messages DSNL027I and DSNL028I with  *
    *                         reason code 00D3003B .               *
    *                                                              *
    *                       o Over utilzation of DBATs, perhaps    *
    *                         leading to an erroneous MAXDBAT      *
    *                         limit condition.                     *
    *                                                              *
    *                       o A quiesce stop of DDF may not        *
    *                         complete due to the erroneous        *
    *                         presence of an active DBAT.          *
    *                         A -STOP DDF MODE(FORCE) may be       *
    *                         necessary in order for DDF to        *
    *                         complete its STOP processing.        *
    *                                                              *
    *                       o The DBAT, now being accessed by      *
    *                         a new user, shows continued          *
    *                         lock on and usage of DGTT tablespace *
    *                         in the workfile database.            *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    A user configured DB2 for inactive threads and connection
    pooling for remote DB2 server threads (DBATs) via
    DDF THREADS=INACTIVE (or DB2 installation parm / zparm
    DSN6FAC CMTSTAT INACTIVE). On this same DB2 (as a server),
    a user ran one or more remote applications via a DBAT and these
    applications resulted in a mix of Declared Global Temporary
    Tables (DGTTs) defined with and without ON COMMIT DROP TABLE.
    These applications ended and intentionally left some DGTTs
    remaining, but DB2 then incorrectly allowed the associated DBAT
    (with its remaining DGTTs) to go 'inactive' at a COMMIT point
    and be pooled in the connection pool to be reused later as a
    DBAT to service other remote work/applications.  When DB2 later
    reused this same DBAT to service remote work for a 'different'
    user who did not use DGTTs, this new user 'inherited' the
    'previous' user's remaining DGTTs and experienced the reported
    DBAT symptoms and conditions previously described.
                                                                   .
    The unexpected DBAT conditions resulted from both
    (1) the 'new' user inheriting the remaining DGTTs created by
        a previous user of that same DBAT, and
    (2) DB2 then enforcing on this 'new' user the restriction that
        a DBAT that has active DGTTs cannot go inactive,
    thus giving this 'new' user the DBAT conditions that should have
    been issued for the 'previous' user of the DBAT but were not.
    Those DBAT conditions and messages would be valid for 'previous'
    user of this DBAT, but not for the 'new' user of the DBAT.
                                                                   .
    The problem occurred because after DB2 implicitly dropped
    at COMMIT those DGTTs defined as ON COMMIT DROP TABLE, DB2 did
    not check if other DGTTs remained for that DBAT; so therefore,
    DB2 did not mark the DBAT as 'cannot go inactive at COMMIT'.
                                                                   .
    This problem only occurs when using DGTTs defined as ON COMMIT
    DROP TABLE while other DGTTs created from the workload do NOT
    use ON COMMIT DROP TABLE -- basically, only if there is a 'mix'
    of the two types. So to circumvent the problem and those DBAT
    conditions, if possible, make all the DGTTs be the same type
    even across different applications by doing one of
    the following:
    (1) remove the ON COMMIT DROP TABLE attribute and, instead,
        explicitly do SQL DROP TABLE for the DGTTs before COMMIT, OR
    (2) make all the DGTTs created by all of the applications /
        workloads run on this DBAT use ON COMMIT DROP TABLE
                                                                   .
    This problem with ON COMMIT DROP TABLE only occurs for DB2 9/10
    for z/OS, not for DB2 11.
                                                                   .
    A second problem discovered is when an SQL ROLLBACK occurs for
    a DBAT that still has remaining DGTTs 'after' the DB2 ROLLBACK
    process is done, DB2 did not mark the DBAT as 'cannot go
    inactive at ROLLBACK'.  Thus, the same previously described DBAT
    conditions and symptoms could occur following an SQL ROLLBACK
    for the DB2 thread. However, this second problem specific to
    ROLLBACK can occur regardless of usage of ON COMMIT DROP TABLE,
    and applies to DB2 9/10/11 for z/OS.
    

Problem conclusion

  • For DB2 9/10, DB2 COMMIT processing was corrected to prevent
    a DBAT going inactive and being pooled if DGTTs still remain
    for this DB2 thread after DB2 implicitly drops the DGTTs defined
    as ON COMMIT DROP TABLE.
                                                                   .
    For DB2 9/10/11, DB2 ROLLBACK processing was corrected to
    prevent a DBAT going inactive and being pooled if DGTTs still
    remain 'after' DB2 ROLLBACK processing.
                                                                   .
    Note that even after applying this PTF, if you did not make
    changes to your remote application to ensure *all* DGTTs are
    dropped before a COMMIT or ROLLBACK point, you will still
    validly encounter the reported DBAT conditions/symptoms/messages
    but they will occur for the DBAT user who actually created/
    defined the DGTTs rather than for a later user of the DBAT.
                                                                   .
    Additional search keywords: DB2DDF DB2INACTIVE SQLDGTT
                                RC00D3003B
    

Temporary fix

  • AM91165
    

Comments

APAR Information

  • APAR number

    PM91165

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-06-14

  • Closed date

    2013-08-15

  • Last modified date

    2013-10-04

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

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

    UK96651 UK96653 UK96654

Modules/Macros

  • DSNXECW  DSNXECWA DSNXMIT2
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK96651

       UP13/09/11 P F309 ½

  • RB10 PSY UK96653

       UP13/09/11 P F309 ½

  • R910 PSY UK96654

       UP13/09/11 P F309 ½

Fix is available

  • Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
04 October 2013