|
Problem IC70573
|
Status: Closed |
Deferred updates to STMM enabled db configuration parameters may be overwritten by STMM. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Deferred updates to self tuning memory manager (STMM) enabled
database configuration parameters may be overwritten by STMM by
the time the database is reactivated. It is expected that upon
database reactivation, the deferred updates you have made will
take effect.
Brief summary:
0. Originally, DATABASE_MEMORY is AUTOMATIC, and is tuned by
STMM.
1. UPDATE DB CFG ... USING DATABASE_MEMORY <some fixed value,
not automatic> DEFERRED
2. Since the update statement was deferred, STMM keeps tuning
DATABASE_MEMORY.
3. After reactivation, the cfg value in #1 is lost.
Repro scenario:
0. Originally, DATABASE_MEMORY is AUTOMATIC, and is tuned by
STMM.
db2 update dbm cfg using instance_memory 100000
db2 update db cfg for testdb using database_memory 82000
<<fabricating the numbers so that STMM will change this value at
step 2.>>
db2 connect to testdb
db2 update db cfg for testdb using database_memory 82000
automatic
1. UPDATE DB CFG ... USING DATABASE_MEMORY <some fixed value,
not automatic> DEFERRED
db2 update db cfg for testdb using database_memory 50000
deferred
2. Since the update statement was deferred, STMM keeps tuning
DATABASE_MEMORY.
After about 3 mins, the following appears in db2diag.log.
2010-07-21-17.31.01.418335-240 I137851A473 LEVEL:
Event
PID : 2441236 TID : 5142 PROC :
db2sysc
INSTANCE: db2inst1 NODE : 000 DB :
TESTDB
APPHDL : 0-9 APPID:
*LOCAL.DB2.100721212738
AUTHID : DB2INST1
EDUID : 5142 EDUNAME: db2stmm (TESTDB)
FUNCTION: DB2 UDB, config/install, sqlfLogUpdateCfgParam,
probe:20
CHANGE : STMM CFG DB SB: "Database_memory" From: "50000"
To: "37220" <automatic>
3. After reactivation, the cfg value in #1 is lost
3.1 On-disk value is now 37220<automatic>, as seen below.
db2 force applications all
db2 get db cfg for testdb | grep -i database_memory
Size of database shared memory (4KB) (DATABASE_MEMORY) =
AUTOMATIC(37220)
3.2 During next reactivation, the value changes yet again,
as DATABASE_MEMORY is AUTOMATIC.
db2 connect to testdb
db2 get db cfg for testdb show detail | grep -i
database_memory
Size of database shared memory (4KB) (DATABASE_MEMORY) =
AUTOMATIC(35584) AUTOMATIC(35584)
The correct behaviour have been after reactivation,
DATABASE_MEMORY is 50000 and not automatic. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* Deferred updates to self tuning memory manager (STMM) *
* enabled *
* database configuration parameters may be overwritten by STMM *
* by *
* the time the database is reactivated. It is expected that *
* upon *
* database reactivation, the deferred updates you have made *
* will *
* take effect. *
* *
* *
* *
* Brief summary: *
* *
* 0. Originally, DATABASE_MEMORY is AUTOMATIC, and is tuned by *
* *
* STMM. *
* *
* 1. UPDATE DB CFG ... USING DATABASE_MEMORY <some fixed *
* value, *
* not automatic> DEFERRED *
* *
* 2. Since the update statement was deferred, STMM keeps *
* tuning *
* DATABASE_MEMORY. *
* *
* 3. After reactivation, the cfg value in #1 is lost. *
* *
* *
* *
* *
* *
* Repro scenario: *
* *
* 0. Originally, DATABASE_MEMORY is AUTOMATIC, and is tuned by *
* *
* STMM. *
* *
* *
* *
* db2 update dbm cfg using instance_memory 100000 *
* *
* db2 update db cfg for testdb using database_memory 82000 *
* *
* <<fabricating the numbers so that STMM will change this *
* value at *
* step 2.>> *
* *
* db2 connect to testdb *
* *
* db2 update db cfg for testdb using database_memory 82000 *
* *
* automatic *
* *
* *
* *
* 1. UPDATE DB CFG ... USING DATABASE_MEMORY <some fixed *
* value, *
* not automatic> DEFERRED *
* *
* *
* *
* db2 update db cfg for testdb using database_memory 50000 *
* *
* deferred *
* *
* *
* *
* 2. Since the update statement was deferred, STMM keeps *
* tuning *
* DATABASE_MEMORY. *
* *
* *
* *
* After about 3 mins, the following appears in *
* db2diag.log. *
* *
* *
* 2010-07-21-17.31.01.418335-240 I137851A473 LEVEL: *
* *
* Event *
* *
* PID : 2441236 TID : 5142 PROC : *
* *
* db2sysc *
* *
* INSTANCE: db2inst1 NODE : 000 DB : *
* TESTDB *
* *
* APPHDL : 0-9 APPID: *
* *
* *LOCAL.DB2.100721212738 *
* *
* AUTHID : DB2INST1 *
* *
* EDUID : 5142 EDUNAME: db2stmm (TESTDB) *
* *
* FUNCTION: DB2 UDB, config/install, *
* sqlfLogUpdateCfgParam, *
* probe:20 *
* *
* CHANGE : STMM CFG DB SB: "Database_memory" From: *
* "50000" *
* To: "37220" <automatic> *
* *
* *
* *
* 3. After reactivation, the cfg value in #1 is lost *
* *
* *
* *
* 3.1 On-disk value is now 37220<automatic>, as seen *
* below. *
* *
* *
* db2 force applications all *
* *
* db2 get db cfg for testdb | grep -i database_memory *
* *
* Size of database shared memory (4KB) (DATABASE_MEMORY) *
* = *
* AUTOMATIC(37220) *
* *
* *
* *
* 3.2 During next reactivation, the value changes yet *
* again, *
* as DATABASE_MEMORY is AUTOMATIC. *
* *
* *
* *
* db2 connect to testdb *
* *
* db2 get db cfg for testdb show detail | grep -i *
* *
* database_memory *
* *
* Size of database shared memory (4KB) (DATABASE_MEMORY) *
* = *
* AUTOMATIC(35584) AUTOMATIC(35584) *
* *
* *
* *
* The correct behaviour have been after reactivation, *
* *
* DATABASE_MEMORY is 50000 and not automatic. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 UDB version 9.7 fix pack 4. *
**************************************************************** |
| Local Fix: |
There are a few things you can do
- Turn off STMM for the configuration parameter you'd like to
change, then make the deferred update.
- Perform the actual desired update to the configuration
parameter after the database has been deactivated.
- If making a deferred update while the database is activated,
verify that the desired database configuration parameter is
still set upon database deactivation. STMM may not have decided
to modify the value, hence the deferred update would still be
used. |
| available fix packs: |
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 |
Problem was first fixed in DB2 UDB Version 9.7 Fix Pack 4 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 15.08.2010
05.05.2011
05.05.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
