|
Problem IC68846
|
Status: Closed |
SET WRITE SUSPEND CONSISTENTLY FAILS WITH SQL1550N RC=5 WHEN THE BUFFERPOOL IS STMM ENABLED. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When the bufferpool is being resized by STMM, the SET WRITE
SUSPEND command will need to wait behind the resize operations
until it is finish. Sometimes the resize can take a long time,
and also it is unpredictable when STMM will trigger the resize.
This can therefore pose a difficulty to users who hope to
perform SET WRITE SUSPEND in fixed maintenance window.
.
When this issue happens, the STMM agent is usually in the
waiting state under the following codepath.
.
0x09000000042E737C getConflictComplex__17SQLO_SLATCH_CAS64FCUl +
0x244
0x09000000042E6F1C getConflict__17SQLO_SLATCH_CAS64FCUl + 0x20
0x090000000381680C
sqlbSecurePageProtection__FP15SQLB_BufferPoolP8SQLB_BPDPP22SQLB_
HASH_BUCKET_GROUPP12SQLB_GLOBALS + 0x644
0x0900000003814DF0
sqlbRemInvalidPagesFromBufferPool__FP15SQLB_BufferPoolUiN32P12SQ
LB_GLOBALS + 0x121C
0x09000000038122D4
sqlbDecreaseBufferpoolSize__FP15SQLB_BufferPoolP21SQLB_BP_UC_ALT
ER_INFOP12SQLB_GLOBALS + 0x4EC
0x0900000003810C48
sqlbResizeBufferPool__FP15SQLB_BufferPoolP21SQLB_BP_UC_ALTER_INF
OP12SQLB_GLOBALS + 0x230
0x0900000003826B64
sqlbAlterAutomaticBufferPool__FUiiP20sqle_agent_privatecb +
0x630
0x0900000003D8312C
sqlrlStmmAlterBufferPool__FP20sqle_agent_privatecbPciT3 + 0x2D0
0x0900000003DA533C
stmmDecreaseBufferPoolInBlocks__FP21stmmCostBenefitRecord +
0x8D8
0x0900000003DA3AD8
stmmResizeRecord__FP21stmmCostBenefitRecordP8sqledbcb + 0xA6C
0x0900000003DA2470
stmmDecreaseEntriesAndRemoveFromList__FP8sqledbcbPP21stmmCostBen
efitRecordPUi + 0x82C
0x0900000003DA1450
stmmResizeEntriesAndRemoveFromList__FPP21stmmCostBenefitRecordP8
sqledbcb + 0xBC
0x0900000003DA0FD0
stmmTuneMemory__FPP21stmmCostBenefitRecordP8sqledbcb + 0x110
0x090000000439BB4C stmmMemoryTunerMain + 0x238 |
| Problem Summary: |
SET WRITE SUSPEND consistently fails with SQL1550N RC=5 when the
bufferpool is STMM enabled. |
| Local Fix: |
Avoid doing SET WRITE SUSPEND when STMM is busy (i.e. change in
workload that causes STMM to need to resize the bufferpool).
.
Turn off STMM momentarily for SET WRITE SUSPEND if STMM is not
already in action. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
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 WRITE SUSPEND latch is being help by the STMM process, as it
resizes the various bufferpools. Due to this, the SET WRITE
SUSPEND command makes 3 attempts to grab the latch before
finally failing with the SQL1550N error with RC=5. |
| Workaround |
Avoid doing SET WRITE SUSPEND when STMM is busy (i.e. change in
workload that causes STMM to need to resize the bufferpool).
Turn off STMM momentarily for SET WRITE SUSPEND if STMM is not
already in action. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.05.2010
12.10.2010
12.10.2010 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
