A fix is available
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