home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IT30341 Status: Closed

PERFORMANCE ISSUE DUE TO THE HIGH NUMBER OF ANCHOR LATCH CONTENTION

product:
DB2 FOR LUW / DB2FORLUW / B10 - DB2
Problem description:
High number of anchor latch waiters can be observed on a highly
OLTP environment which may impact query performance and cause
performance bottlenecks.

The anchor latch contention is caused during user privilege
lookup when privileges are granted via user roles.

We can see the following anchor latch waits in db2pd -latches
outputs:

Latch waiters with holders:
================================
0_2019-08-23-08.03.44.074717: Latch Address: 0x0A0003284CD67584
( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 1
, Num Waiters - 44,HOLDER EDU(s):  95813( 44 ),
0_2019-08-23-08.06.03.094406: Latch Address: 0x0A0003284CD67584
( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 3
, Num Waiters - 210,HOLDER EDU(s):  311661( 70 ), 91307( 70 ),
67028( 70 ),
0_2019-08-23-08.10.40.118186: Latch Address: 0x0A0003284CD67214
( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 1
, Num Waiters - 19,HOLDER EDU(s):  141652( 19 ),
0_2019-08-23-08.15.19.130377: Latch Address: 0x0A0003284CD67584
( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 1
, Num Waiters - 95,HOLDER EDU(s):  222325( 95 ),
0_2019-08-23-08.18.48.079977: Latch Address: 0x0A0003284CD67584
( SQLO_LT_sqlrlc_anchor_common__anchor_latch ): Num Holders -- 1
, Num Waiters - 91,HOLDER EDU(s):  456299( 91 ),



SQLO_LT_sqlrlc_anchor_common__anchor_latch
0x0A0003284A4D7584 0          615936     sqlbchsb.C           74
SQLO_LT_sqlrlc_anchor_common__anchor_latch
0x0A0003284A4D7584 0          632361     sqlbchsb.C           74
SQLO_LT_sqlrlc_anchor_common__anchor_latch
0x0A0003284A4D7584 0          650582     sqlbchsb.C           74
SQLO_LT_sqlrlc_anchor_common__anchor_latch

stack dumps collected during the issue may look as follows:

0x0900000014A8C5C0 sqloXlatchConflict + 0x8A0
  0x09000000162B2F08
sqlrlc_find_latch_anchor__FP8sqlrr_cbP10sqlrlc_keyPP20sqlrlc_anc
hor_commonPP19sqlrlc_entry_commonT4Pb + 0x388
  0x09000000162BDBE8
sqlrlc_auth_find_insert__FP8sqlrr_cbP21sqlrlc_auths_requiredP14S
QLP_LOCK_INFOPb + 0xE8
  0x0900000016C42090
sqlrlc_rtn_get_auths__FP8sqlrr_cbP21sqlrlc_auths_requiredUiPUcN2
4 + 0x130
  0x0900000016C3E990
sqlrlc_rtn_request_auths__FP8sqlrr_cbP11sqlr_aainfoPUcUsT3sUcT3T
4T3T6N33P10sqlr_rolesbT16_ + 0x710
  0x0900000015BF4244
sqlra_is_rtn_auth_held__FP8sqlrr_cbP18sqlra_routine_infoP10sqlr_
rolesPUi + 0x104
  0x0900000015BF5998
sqlra_compare_env_routine_auths__FP8sqlrr_cbP16sqlra_cached_envi
T3Pi + 0x1578
  0x0900000015BC7444
sqlra_hash_loc_env__FP8sqlrr_cbP14sqlra_cmpl_envP17sqlra_cached_
stmtibPP16sqlra_cached_envT6PiPUl + 0x1364
  0x0900000015BD0270
sqlra_find_var_env__FP8sqlrr_cbP17sqlra_anchor_stmtP17sqlra_cach
ed_stmtP13sqlra_stmt_idUiP14sqlra_cmpl_env15sqlra_fill_modebT8Pi
T10_PP16sqlra_cached_envT10_PUl + 0x1D0
  0x0900000015BDE11C
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
SQLP_LOCK_INFOPP16sqlra_cached_varT12_bT19_Pb + 0x75C
  0x0900000016C9EAAC sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0xC0C
  0x0900000017AEDC50
sqlrr_process_execute_request__FP8sqlrr_cbib + 0x42B0
  0x09000000163ED4E0
sqlrr_execute__FP14db2UCinterfaceP9UCstpInfo + 0x5E0
  0x090000001B82F43C
sqljs_ddm_excsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0x5BC
  0x090000001B7B3D60
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface + 0x280
  0x090000001B7B6DE4
sqljsParse__FP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb +
0x2264
  0x090000001B78339C
IPRA.$sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0xE9C
  0x090000001B78BE94
sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xB4
  0x090000001B781460
IPRA.$sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x13E0
  0x090000001B77E8B4 sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
0x334



Stack:
======================

0x0900000014A8C958 sqloSpinLockReleaseConflict + 0xB8
  0x09000000162BF958
sqlrlc_auth_find_insert__FP8sqlrr_cbP21sqlrlc_auths_requiredP14S
QLP_LOCK_INFOPb + 0x1E58
  0x0900000016C42090
sqlrlc_rtn_get_auths__FP8sqlrr_cbP21sqlrlc_auths_requiredUiPUcN2
4 + 0x130
  0x0900000016C3E990
sqlrlc_rtn_request_auths__FP8sqlrr_cbP11sqlr_aainfoPUcUsT3sUcT3T
4T3T6N33P10sqlr_rolesbT16_ + 0x710
  0x0900000015BF4244
sqlra_is_rtn_auth_held__FP8sqlrr_cbP18sqlra_routine_infoP10sqlr_
rolesPUi + 0x104
  0x0900000015BF5998
sqlra_compare_env_routine_auths__FP8sqlrr_cbP16sqlra_cached_envi
T3Pi + 0x1578
  0x0900000015BC7444
sqlra_hash_loc_env__FP8sqlrr_cbP14sqlra_cmpl_envP17sqlra_cached_
stmtibPP16sqlra_cached_envT6PiPUl + 0x1364
  0x0900000015BD0270
sqlra_find_var_env__FP8sqlrr_cbP17sqlra_anchor_stmtP17sqlra_cach
ed_stmtP13sqlra_stmt_idUiP14sqlra_cmpl_env15sqlra_fill_modebT8Pi
T10_PP16sqlra_cached_envT10_PUl + 0x1D0
  0x0900000015BDE11C
sqlra_find_var__FP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idUi
T4PUcT4UsUcP14sqlra_cmpl_env15sqlra_fill_modePiiT12_N313_T12_P14
SQLP_LOCK_INFOPP16sqlra_cached_varT12_bT19_Pb + 0x75C
  0x0900000016C9EAAC sqlra_get_var__FP8sqlrr_cbiT2bPbT5 + 0xC0C
  0x0900000017AEDC50
sqlrr_process_execute_request__FP8sqlrr_cbib + 0x42B0
  0x09000000163ED4E0
sqlrr_execute__FP14db2UCinterfaceP9UCstpInfo + 0x5E0
  0x090000001B82F43C
sqljs_ddm_excsqlstt__FP14db2UCinterfaceP13sqljDDMObject + 0x5BC
  0x090000001B7B3D60
sqljsParseRdbAccessed__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UC
interface + 0x280
  0x090000001B7B6DE4
sqljsParse__FP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb +
0x2264
  0x090000001B78339C
IPRA.$sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb + 0xE9C
  0x090000001B78BE94
sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0xB4
  0x090000001B781460
IPRA.$sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0x13E0
  0x090000001B77E8B4 sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
0x334
  0x0900000014B3D1A0 RunEDU__8sqeAgentFv + 0xB60

The issue is more apparent when a user has many roles.
Problem Summary:
****************************************************************
* USERS AFFECTED:                                              *
* All users on db2 v11.1 Fixpack 4 and lower                   *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to First Fixed in DB2 v11.1 M4 FP5 or see local Fix. *
****************************************************************
Local Fix:
As a temp workaround, granting the execute privileges to user
directly, avoids the lookup by role and avoids this issue.
Solution


Workaround


not known / see Local fix


Timestamps


Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

20.09.2019
03.10.2019
03.10.2019


Problem solved at the following versions (IBM BugInfos)




Problem solved according to the fixlist(s) of the following version(s)