|
Problem IC69004
|
Status: Closed |
QUERIES INVOLVING JOIN PREDICATES ON DECFLOAT COLUMNS MIGHT CAUSE A DB2 SERVER ABEND |
| product: |
DB2 FOR LUW / DB2FORLUW / 980 - DB2 |
| Problem description: |
The problem might occur if the following conditions are
satisfied:
1. The "JOIN" setting of DB2_REDUCED_OPTIMIZATION is set.
2. The query includes a decfloat column participating in a
join.
3. Distribution statistics are collected on both columns
participating in the join.
4. The query block containing the join of concern is eligible
as a star join.
The following is an example stack trace that you will observe if
you encounter this problem:
<StackTrace>
-------Frame------ ------Function + Offset------
0x09000000110FE4A0 sqlvsht2dec + 0x60
0x0900000011C8B0EC sqlvcnum + 0x1CC
0x0900000011C88A68 sqlrxcon2__FP8SQLRXNLSP10sqlz_valueT2 + 0x2C8
0x0900000011C72524 sqlrxcon_simple__FP8SQLRXNLSP10sqlz_valueT2 +
0xAE4
0x0900000011C74B14
sqlv_intcompdec__FCPC14sqlrxCollationP10sqlz_valueT2Ui + 0xD4
0x0900000011C76EC8
sqlrxcp2__FCPC14sqlrxCollationP10sqlz_valueT2Ui + 0x4A8
0x0900000014F437EC
sqlno_compare_literal__FP13sqlno_globalsP10sqlz_valueT2CPC14sqlr
xCollation + 0x2C
0x0900000014F43BEC
sqlno_nud_compare_literal__FP13sqlno_globalsP10sqlz_valuePdUiT3T
411sqlz_varlenPUcP9sqlnq_cstCPC14sqlrxCollationP13SQLNN_BOOLEANC
P19sqlno_ff_essenti
als + 0x24C
0x0900000014F43DC4
sqlno_nud_compare_literal__FP13sqlno_globalsPdUiP10sqlz_valueP9s
qlnq_frqP9sqlnq_cst17sqlno_prefix_typeCPC14sqlrxCollationP13SQLN
N_BOOLEANCP19sqlno_
ff_essentials + 0xA4
0x0900000014F47898
sqlno_ff_nud_col_eq_col__FP13sqlno_globalsP19sqlno_ff_essentials
P9sqlnq_cstfT4T3N24PfT9 + 0xDD8
0x0900000014F6E1EC
sqlno_ff_col_eq_col__FP13sqlno_globalsP19sqlno_ff_essentialsPfT3
P16sqlno_listheader13SQLNN_BOOLEAN + 0xECC
0x0900000014F6F840
sqlno_ff_compute_atomprd__FP13sqlno_globalsP19sqlno_ff_essential
sP16sqlno_listheader13SQLNN_BOOLEAN + 0xDA0
0x0900000014F74634
sqlno_ff_compute__FP13sqlno_globalsP19sqlno_ff_essentialsP16sqln
o_listheader13SQLNN_BOOLEAN + 0x714
0x09000000154E4C48
sqlno_prop_recompute_pred_ff__FP13sqlno_globalsP9sqlno_qtbP11sql
no_table9sqlno_setPf + 0x468
0x0900000015622D84
sqlno_starjn_edge_list_init__FP13sqlno_globalsP9sqlno_qtbP10sqln
o_apcbiP10sqlno_factP21sqlno_starjn_qun_infoPP17sqlno_starjn_edg
ePiPP9sqlno_set
+ 0
xCE4
0x090000001562836C
sqlno_starjn_init__FCP13sqlno_globalsCP9sqlno_qtb + 0x9AC
0x09000000154FAED4
sqlno_prop_compute_ff_adjustments__FP13sqlno_globalsP9sqlno_qtb
+ 0x254
0x09000000177C62A4
sqlno_plan_begin_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_opr +
0x644
0x09000000175BC24C
sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQLN
N_BOOLEAN + 0x2CC
0x09000000175BFC0C
sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq_o
pr + 0x1AC
0x09000000175BC24C
sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQLN
N_BOOLEAN + 0x2CC
0x09000000175C0490
sqlno_walk_qun__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_oprP9sqlnq_q
un + 0xF0
0x09000000175BC24C
sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQLN
N_BOOLEAN + 0x2CC
0x09000000175BFC8C
sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq_o
pr + 0x22C
0x09000000175BC24C
sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQLN
N_BOOLEAN + 0x2CC
0x09000000175C0490
sqlno_walk_qun__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_oprP9sqlnq_q
un + 0xF0
0x09000000175BC24C
sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQLN
N_BOOLEAN + 0x2CC
0x09000000175BFC8C
sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq_o
pr + 0x22C
0x09000000175BC24C
sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQLN
N_BOOLEAN + 0x2CC
0x09000000175C0788
sqlno_top_qtb__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qurP9sqlnq_qt
b + 0xE8
0x09000000175BC24C
sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQLN
N_BOOLEAN + 0x2CC
0x09000000175C0CC4
sqlno_each_qur__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qurT3 + 0x2C
4
0x09000000175BC24C
sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQLN
N_BOOLEAN + 0x2CC
0x09000000175BC5D8
sqlno_scan_qgm__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qur + 0x138
0x0900000017761134
sqlno_planning_scan__FP13sqlno_globalsP9sqlnq_quri + 0x514
0x0900000017762128
sqlno_planning_phase__FP13sqlno_globalsP9sqlnq_quriT3 + 0x808
0x0900000017768A60 sqlno_exe__FP9sqlnq_qur + 0x1720
0x0900000014132064
sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14sq
lrr_cmpl_enviT7PP9sqlnq_qur + 0x70C4
0x090000001411BA28
sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQLP_
LOCK_INFOP16sqlra_cached_varPiPUl + 0x18A8
0x0900000015E8EC04
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
SQLP_LOCK_INFOPP16s
qlra_cached_varT12_PUlb + 0xD64
0x0900000015EE40F0 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0x22B0
0x090000001595F74C
sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0x24C
0x0900000016AA1E40
sqljs_ddm_prpsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0x1A0
0x0900000016A6DE10
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface + 0x210
0x0900000016A6CA38
sqljsParse__FP13sqljsDrdaAsCbP14db2UCinterface + 0x1F8
0x0900000016A37764 sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb +
0x4A4
0x0900000016A395B4
sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0x154
0x0900000016A40838
sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x478
0x0900000016A428EC sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
0x26C
0x0900000011DDC518 RunEDU__8sqeAgentFv + 0x3F8
0x09000000114AC3C8 EDUDriver__9sqzEDUObjFv + 0x1C8
0x09000000114AC6FC sqlzRunEDU__FPcUi + 0x3C
0x09000000110047B8 sqloEDUEntry + 0x6F8
</StackTrace> |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* The DB2 server might abend during query compilation if the *
* following conditions are satisfied: *
* *
* *
* *
* 1. The "JOIN" setting of DB2_REDUCED_OPTIMIZATION is set. *
* 2. The query includes a decfloat column participating in a *
* join. *
* 3. Distribution statistics are collected on both columns *
* participating in the join. *
* 4. The query block containing the join of concern is *
* eligible as a star join. *
* *
* *
* The following is an example stack trace that you will *
* observe if you encounter this problem: *
* *
* *
* *
* <StackTrace> *
* *
* -------Frame------ ------Function + Offset------ *
* *
* 0x09000000110FE4A0 sqlvsht2dec + 0x60 *
* *
* 0x0900000011C8B0EC sqlvcnum + 0x1CC *
* *
* 0x0900000011C88A68 sqlrxcon2__FP8SQLRXNLSP10sqlz_valueT2 + *
* 0x2C8 *
* 0x0900000011C72524 *
* sqlrxcon_simple__FP8SQLRXNLSP10sqlz_valueT2 + *
* 0xAE4 *
* *
* 0x0900000011C74B14 *
* *
* sqlv_intcompdec__FCPC14sqlrxCollationP10sqlz_valueT2Ui + *
* 0xD4 *
* 0x0900000011C76EC8 *
* *
* sqlrxcp2__FCPC14sqlrxCollationP10sqlz_valueT2Ui + 0x4A8 *
* *
* 0x0900000014F437EC *
* *
* sqlno_compare_literal__FP13sqlno_globalsP10sqlz_valueT2CPC14sq
* xCollation + 0x2C *
* *
* 0x0900000014F43BEC *
* *
* sqlno_nud_compare_literal__FP13sqlno_globalsP10sqlz_valuePdUiT
* 411sqlz_varlenPUcP9sqlnq_cstCPC14sqlrxCollationP13SQLNN_BOOLEA
* P19sqlno_ff_essenti *
* *
* als + 0x24C *
* *
* 0x0900000014F43DC4 *
* *
* sqlno_nud_compare_literal__FP13sqlno_globalsPdUiP10sqlz_valueP
* qlnq_frqP9sqlnq_cst17sqlno_prefix_typeCPC14sqlrxCollationP13SQ
* N_BOOLEANCP19sqlno_ *
* *
* ff_essentials + 0xA4 *
* *
* 0x0900000014F47898 *
* *
* sqlno_ff_nud_col_eq_col__FP13sqlno_globalsP19sqlno_ff_essentia
* P9sqlnq_cstfT4T3N24PfT9 + 0xDD8 *
* *
* 0x0900000014F6E1EC *
* *
* sqlno_ff_col_eq_col__FP13sqlno_globalsP19sqlno_ff_essentialsPf
* P16sqlno_listheader13SQLNN_BOOLEAN + 0xECC *
* *
* 0x0900000014F6F840 *
* *
* sqlno_ff_compute_atomprd__FP13sqlno_globalsP19sqlno_ff_essenti
* sP16sqlno_listheader13SQLNN_BOOLEAN + 0xDA0 *
* *
* 0x0900000014F74634 *
* *
* sqlno_ff_compute__FP13sqlno_globalsP19sqlno_ff_essentialsP16sq
* o_listheader13SQLNN_BOOLEAN + 0x714 *
* *
* 0x09000000154E4C48 *
* *
* sqlno_prop_recompute_pred_ff__FP13sqlno_globalsP9sqlno_qtbP11s
* no_table9sqlno_setPf + 0x468 *
* *
* 0x0900000015622D84 *
* *
* sqlno_starjn_edge_list_init__FP13sqlno_globalsP9sqlno_qtbP10sq
* o_apcbiP10sqlno_factP21sqlno_starjn_qun_infoPP17sqlno_starjn_e
* ePiPP9sqlno_set *
* *
* + 0 *
* *
* xCE4 *
* *
* 0x090000001562836C *
* *
* sqlno_starjn_init__FCP13sqlno_globalsCP9sqlno_qtb + 0x9AC *
* *
* 0x09000000154FAED4 *
* *
* sqlno_prop_compute_ff_adjustments__FP13sqlno_globalsP9sqlno_qt
* + 0x254 *
* *
* 0x09000000177C62A4 *
* *
* sqlno_plan_begin_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_opr
* + *
* 0x644 *
* *
* 0x09000000175BC24C *
* *
* sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQ
* N_BOOLEAN + 0x2CC *
* *
* 0x09000000175BFC0C *
* *
* sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq
* pr + 0x1AC *
* *
* 0x09000000175BC24C *
* *
* sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQ
* N_BOOLEAN + 0x2CC *
* *
* 0x09000000175C0490 *
* *
* sqlno_walk_qun__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_oprP9sqlnq
* un + 0xF0 *
* *
* 0x09000000175BC24C *
* *
* sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQ
* N_BOOLEAN + 0x2CC *
* *
* 0x09000000175BFC8C *
* *
* sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq
* pr + 0x22C *
* *
* 0x09000000175BC24C *
* *
* sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQ
* N_BOOLEAN + 0x2CC *
* *
* 0x09000000175C0490 *
* *
* sqlno_walk_qun__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_oprP9sqlnq
* un + 0xF0 *
* *
* 0x09000000175BC24C *
* *
* sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQ
* N_BOOLEAN + 0x2CC *
* *
* 0x09000000175BFC8C *
* *
* sqlno_each_opr__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qunP9sqlnq
* pr + 0x22C *
* *
* 0x09000000175BC24C *
* *
* sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQ
* N_BOOLEAN + 0x2CC *
* *
* 0x09000000175C0788 *
* *
* sqlno_top_qtb__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qurP9sqlnq_
* b + 0xE8 *
* *
* 0x09000000175BC24C *
* *
* sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQ
* N_BOOLEAN + 0x2CC *
* *
* 0x09000000175C0CC4 *
* *
* sqlno_each_qur__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qurT3 + *
* 0x2C *
* 4 *
* *
* 0x09000000175BC24C *
* *
* sqlno_call_sf__FP13sqlno_globalsP8sqlno_sfP9sqlno_sfbPvT4i13SQ
* N_BOOLEAN + 0x2CC *
* *
* 0x09000000175BC5D8 *
* *
* sqlno_scan_qgm__FP13sqlno_globalsP9sqlno_sfbP9sqlnq_qur + *
* 0x138 *
* 0x0900000017761134 *
* *
* sqlno_planning_scan__FP13sqlno_globalsP9sqlnq_quri + 0x514 *
* *
* 0x0900000017762128 *
* *
* sqlno_planning_phase__FP13sqlno_globalsP9sqlnq_quriT3 + *
* 0x808 *
* 0x0900000017768A60 sqlno_exe__FP9sqlnq_qur + 0x1720 *
* *
* 0x0900000014132064 *
* *
* sqlnn_cmpl__FP8sqeAgentP11sqlrrstrings17sqlnn_compileModeT3P14
* lrr_cmpl_enviT7PP9sqlnq_qur + 0x70C4 *
* *
* 0x090000001411BA28 *
* *
* sqlra_compile_var__FP8sqlrr_cbP14sqlra_cmpl_envPUciUsN54P14SQL
* LOCK_INFOP16sqlra_cached_varPiPUl + 0x18A8 *
* *
* 0x0900000015E8EC04 *
* *
* sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_id
* T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P
* SQLP_LOCK_INFOPP16s *
* *
* qlra_cached_varT12_PUlb + 0xD64 *
* *
* 0x0900000015EE40F0 sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + *
* 0x22B0 *
* 0x090000001595F74C *
* *
* sqlrr_prepare__FP14db2UCinterfaceP16db2UCprepareInfo + 0x24C *
* *
* 0x0900000016AA1E40 *
* *
* sqljs_ddm_prpsqlstt__FP14db2UCinterfaceP13sqljDDMObject + *
* 0x1A0 *
* 0x0900000016A6DE10 *
* *
* sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2
* interface + 0x210 *
* *
* 0x0900000016A6CA38 *
* *
* sqljsParse__FP13sqljsDrdaAsCbP14db2UCinterface + 0x1F8 *
* *
* 0x0900000016A37764 sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb *
* + *
* 0x4A4 *
* *
* 0x0900000016A395B4 *
* *
* sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0x154 *
* *
* 0x0900000016A40838 *
* *
* sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x478 *
* *
* 0x0900000016A428EC sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T *
* + *
* 0x26C *
* *
* 0x0900000011DDC518 RunEDU__8sqeAgentFv + 0x3F8 *
* *
* 0x09000000114AC3C8 EDUDriver__9sqzEDUObjFv + 0x1C8 *
* *
* 0x09000000114AC6FC sqlzRunEDU__FPcUi + 0x3C *
* *
* 0x09000000110047B8 sqloEDUEntry + 0x6F8 *
* *
* </StackTrace> *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 LUW Version 9.8 Fix Pack 2 *
**************************************************************** |
| Local Fix: |
You can use either of the following methods to workaround the
problem:
1. Unset the registry setting. This is not always practical as
it could impact query execution performance.
2. Execute runstats without collecting distribution statistics
on the join columns. This could also impact query execution
performance since it could lead to a change in the access plan
chosen by the optimizer. |
| Solution |
Problem was first fixed in Version 9.8 Fix Pack 2. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 03.06.2010
13.09.2010
13.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.8.FP2
|
| Problem solved according to the fixlist(s) of the following version(s) |
