home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC98021 Status: Closed

AN ACCESS PLAN WITH NESTED MDTQ OPERATORS WITHOUT A SORT OR TEMP OPERATOR
IN BETWEEN MAY HANG

product:
DB2 FOR LUW / DB2FORLUW / A10 - DB2
Problem description:
An access plan with nested MDTQ operators without a SORT or TEMP 
operator in between may hang, when there is the potential of 
spilling on the table queues. Communication between subagents 
encounters a certain timing and pattern of activity that results 
in all agents getting stuck while waiting to send and receive 
with each other, but not making any progress. 
Normally, a TQ spill is required to resolve this situation, 
however there exists a logic problem where it fails to detect 
this case and the agents end up waiting. 
 
Example of nested MDTQ operators in an access plan: 
 
                                                   397.941 
 
                                                  MDTQ 
 
                                                  (   2) 
 
                                                  474.57 
 
                                                  101.305 
                                                    | 
 
                                                  33.1617 
 
                                                  GRPBY 
 
                                                  (   3) 
 
                                                  469.95 
 
                                                  101.305 
                                                    | 
 
                                                  33.1617 
                                                  MDTQ 
 
                                                  (   4) 
 
                                                  469.788 
 
                                                  101.305 
 
In an application snapshot, you will see, that the number of 
rows processed for the subagents involved, does not change 
anymore. Only things like elapsed time and CPU time are still 
changing: 
 
  Subsection number                                  = 1 
  Subsection database member number                  = 9 
  Subsection status                                  = Waiting 
to send on tablequeue 
  Node for which waiting on tablequeue               = 0 
  Tablequeue ID on which agent is waiting            = 1 
  Execution elapsed time (seconds)                   = 113991 
  Total user CPU time (sec.microsec)                 = 0.229806 
  Total system CPU time (sec.microsec)               = 0.062660 
  Current number of tablequeue buffers overflowed    = 0 
  Total number of tablequeue buffers overflowed      = 0 
  Maximum number of tablequeue buffers overflowed    = 0 
  Rows received on tablequeues                       = 857 
  Rows sent on tablequeues                           = 855 
  Rows read                                          = 0 
  Rows written                                       = 0 
  Number of agents working on subsection             = 1 
    Agent process/thread ID                       = 23905 
  Subsection number                                  = 2 
  Subsection database member number                  = 9 
  Subsection status                                  = Waiting 
to send on tablequeue 
  Node for which waiting on tablequeue               = 12 
  Tablequeue ID on which agent is waiting            = 2 
  Execution elapsed time (seconds)                   = 113991 
  Total user CPU time (sec.microsec)                 = 0.259330 
  Total system CPU time (sec.microsec)               = 0.062624 
  Current number of tablequeue buffers overflowed    = 0 
  Total number of tablequeue buffers overflowed      = 58 
  Maximum number of tablequeue buffers overflowed    = 23 
  Rows received on tablequeues                       = 5642 
  Rows sent on tablequeues                           = 888 
  Rows read                                          = 5488 
  Rows written                                       = 4458
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10.1 Fix Pack 4.                      * 
****************************************************************
Local Fix:
available fix packs:
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

Solution
First fixed in DB2 Version 10.1 Fix Pack 4.
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC98174 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
29.11.2013
23.06.2014
23.06.2014
Problem solved at the following versions (IBM BugInfos)
Problem solved according to the fixlist(s) of the following version(s)
10.1.0.4 FixList