|
Problem IC85201
|
Status: Closed |
HEAVY INDEX SCANNING CAUSED BY SQLD_NEED_RETRY CAN STARVE INSERT/DELETE |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
On a heavily used table, with insert/update/delete activity and
very heavy scanning (select) activity, it is possible for
insert/delete to be left halfway through an update - with the
data and index inconsistent. The response by scanners to this
inconsistency is to get a SQLD_NEED_RETRY and retry its index
scan. With many scanners, the page latch required by the update
is constantly unavailable, with the result that the update is
starved out for very long periods of time.
The only external symptom will be a insert or delete statement
that is taking too long. The only way to identify the issue is
to take a trace using db2trc. The trace will show an insert or
delete thread looping and repeatedly getting the SQLD_NEED_RETRY
return code. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 version 9.7.0.7. *
**************************************************************** |
| Local Fix: |
If the select activity on the table slows or stops, the problem
will resolve itself automatically. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The problem is first fixed in DB2 version 9.7.0.7. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 09.07.2012
15.11.2012
15.11.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.0.7
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.7
|
 |
