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 | |
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 |