In an SMP PARALLELIZED setup, that is when the dbm cfg is
configured with intra_parallel setting ON, and also the query
degree is > 1, there is a potential to encounter a hang during
hsjn processing.
There is a set of well-defined properties of this hsjn for it to
be considered eligible for this hang condition, specificially:
- It is a right outer hsjn. Be aware that the query compiler
(query rewrite and/or optimizer) may convert left outer joins
into right outer joins. So the problem can still happen even if
the original query text does not explicitly use a right outer
join.
- There is an "early out" condition, such as a fetch first n
clause, OR, something that defines a maximum cardinality limit
on the hsjn results such as a join against unique indexes or
primary keys (only 1 value is guaranteed to be produced from the
join), so the join may do an early exit once that first row is
produced.
- a certain timing between parallel subagents
If the problem is encountered and stack traceback files are
collected with 'db2pd -stack all' or db2fodc -hang for example,
then you may see the following stacks, among others :
-----------stack 1------------------
thread_wait + 0x98
sqloWaitEDUWaitPost + 0x590
IntrptWaitLock__19@51@319399@sqeAgentFP16SQLO_EDUWAITPOSTiPUiT2
+ 0x638
sqlrihsjn__FP8sqlrr_cb + 0xD4
sqlrihsjn__FP8sqlrr_cb + 0x28
sqlriunn__FP8sqlrr_cbP10sqlri_stob + 0x328
sqlriset__FP8sqlrr_cb + 0x14
sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0x94
-----------stack 2-------------------
thread_wait + 0x98
sqloWaitEDUWaitPost + 0x590
sqlrihsjn__FP8sqlrr_cb + 0x26C8
sqlritaSimplePerm__FP8sqlrr_cb + 0x9C
sqlriunn__FP8sqlrr_cbP10sqlri_stob + 0x328
sqlriunn__FP8sqlrr_cbP10sqlri_stob + 0x328
sqlriset__FP8sqlrr_cb + 0xD0
sqlriSectInvoke__FP8sqlrr_cbP12sqlri_opparm + 0x94 |
