|
Problem IC98174
|
Status: Closed |
AN ACCESS PLAN WITH NESTED MDTQ OPERATORS WITHOUT A SORT OR TEMP OPERATOR IN BETWEEN MAY HANG |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - 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.5 Fix Pack 4. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
First fixed in DB2 Version 10.5 Fix Pack 4. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 06.12.2013
06.11.2014
06.11.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.4
|
 |
