|
Problem IC68844
|
Status: Closed |
SET WRITE SUSPEND CONSISTENTLY FAILS WITH SQL1550N RC=5 WHEN THE BUFFERPOOL IS STMM ENABLED. |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - 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: |
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_BPDPP22SQLBH
ASH_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 |
| 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.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
The fix is provided in FP7 for DB2 v9.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). |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 27.05.2010
09.02.2011
09.02.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.1.0.7
|
 |
| 9.5.0.7
|
|
