|
Problem IT31628
|
Status: Closed |
SQL STATEMENT WITH ORDER BY ACCESSING COLUMNAR TABLES MAY BE HUNG IN DPF
|
| product: |
DB2 FOR LUW / DB2FORLUW / B50 - DB2 |
| Problem description: |
SQL STATEMENT WITH ORDER BY ACCESSING COLUMNAR TABLES MAY HANG
IN DPF
It could happen if CTQ in the plan is outputting a very wide row
or if degree with is very high.
We can see below access plan. The issue is a deadlock between
the LMTQ (2) and CTQ (3).
db2agents execute query and they execute both the LMTQ and CTQ
operators in a loop.
SORT has to be in the plan below the CTQ.
LMTQ
( 2)
19289.2
NA
|
174207
CTQ
( 3)
19122.1
NA
|
174207
MDTQ
( 4)
19020.2
NA
|
15837
TBSCAN
( 5)
17612.3
NA
|
15837
SORT
( 6)
We can see two specific stacks:
=====
semtimedop
sqkfChannel::WaitSendReady
sqkfChannel::SendDataBuffer
sqlkqsnd
sqlktsnd
sqlkt_pack_tuple
sqlktins
sqlritqb
sqlriSectInvoke
sqlrr_smp_router
=====
=====
semtimedop
ibm_cde::services::ThresholdWaitPost::wait
ibm_cde::query::StreamingResultIterator::getNextVectors
ibm_cde::query::QueryResultIterator::isEnd
cdeInterface::fetch
sqlriCdeFetch
sqlriCdeQuery
sqlriSectInvoke
sqlrr_smp_router
=====
In general, it is normal for threads to have these stacks,
however it is a problem if all of the db2agnts threads executing
the query are in just these two stacks because it means that
they have deadlocked. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* all *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to Db2 11.5m3fp0 or later *
**************************************************************** |
| Local Fix: |
To reduce intrapartition parallelism.
SET CURRENT DEGREE '1';
or
update dbm cfg using MAX_QEURYDEGREE 1 |
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 23.01.2020
24.04.2020
24.04.2020 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
