IBM Support

IC70573: Deferred updates to STMM enabled db configuration parameters may be overwritten by STMM.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error 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.
    

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.
    

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.                   *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 9.7 Fix Pack 4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC70573

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-08-15

  • Closed date

    2011-05-05

  • Last modified date

    2011-05-05

  • APAR is sysrouted FROM one or more of the following:

    IC70545

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC70573

Modified date: 05 May 2011