Skip to Content

Welcome to this last post in the series about new features in DB2 9 and
their applicability in the SAP environment. Today I’d like to talk about the
Self Tuning Memory Manager (STMM). Also I will mention briefly other interesting
DB2 9 features.

Automatic Memory Management with STMM

In an effort to furthermore ease the configuration and administration of
DB2 the new self tuning memory manager was introduces with version 9. The main
idea behind the STMM is that the memory parameters of the database like size of
the sorting area, buffer pools, lock list etc. are adapted automatically depending
on the workload
. As the memory settings of a database have quite an impact on
the overall performance this will lead to better running systems with a lower
administration effort.

STMM works in two modes which can be distinguished by the setting for the
DB config parameter DATABASE_MEMORY (which specifies the total amount of shared
memory available to the database). In the first mode DATABASE_MEMORY is set to a
numerical value or to COMPUTE which means that the value is calculated by DB2 at
database activation time. If now the DB configuration parameter SELF_TUNING_MEM
is set to ON and at least two memory consumers are set to AUTOMATIC the STMM
starts to work by balancing the overall available memory resources between the
consumers which are set to AUTOMATIC. The possible memory cosumers are

    • the bufferpools, with CREATE BUFFERPOOL or ALTER BUFFERPOOL the memory
      management for a bufferpool can be set to AUTOMATIC
    • the locking memory, controlled by the database configuration
      parameters LOCKLIST and MAXLOCKS
    • the package cache, controlled by PCKCACHESZ
    • the sort memory, controlled by SHEAPTHRES_SHR and SORTHEAP

Here are the settings from a system which runs at SAP and uses the STMM.

db2 =></body>

get db cfg show detail

       Database Configuration for Database

Description                                   Parameter   Current Value     

Database configuration release level                    = 0x0b00
Database release level                                  = 0x0b00

Self tuning memory                    (SELF_TUNING_MEM) = ON (Active)      
Size of database shared memory (4KB)  (DATABASE_MEMORY) = 1354240          
Database memory threshold               (DB_MEM_THRESH) = 10               
Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(6514)  
Percent. of lock lists per application       (MAXLOCKS) = AUTOMATIC(97)    
Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(32910) 
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(9307)  
Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(1861)  

To find out if the bufferpools are enabled for automatic memory
management we have to query the system catalog.

db2 =></body>

select CAST(BPNAME AS VARCHAR(20)), NPAGES from sysibm.sysbufferpools

1                    NPAGES

IBMDEFAULTBP                  -2

A value of -2 indicates that this buffer pool is set to AUTOMATIC. I did
the CAST in this query only so that the result fits better on the screen. The
current memory usage of the buffer pool can be determined with a snapshot:

db2 => get snapshot for bufferpools on AJF

             Bufferpool Snapshot

Bufferpool name                            = IBMDEFAULTBP
Alter bufferpool information:
Pages left to remove                      = 0
Current size                              = 204382
Post-alter size                           = 204382

It’s of course more convenient to use the SAP transaction DB6COCKPIT to
monitor the parameters which are relevant for STMM. Here are two screenshots
from the forthcoming SAP Netweaver Release which show the information from



2006-10-23- A5842215E694       LEVEL: Event
PID     : 16308                TID  : 183042073280PROC : db2stmm (AJF) 0
INSTANCE: db2ajf               NODE : 000         DB   : AJF
APPHDL  : 0-25                 APPID:
FUNCTION: DB2 UDB, Self tuning memory manager, stmmLogRecordAfterResizes, probe:527
DATA #1 : String, 295 bytes

      • stmmCostBenefitRecord ***

PageSize: 4096
Original Size: 14747
Desired New Size: 11803
Actual New Size: 11803
Minimum Size: 6771
Potential Increase Amount: 0
Potential Increase Amount From OS: 0
Potential Decrease Amount: 0
Pages Available For OS: 0
Interval Time: 600.148

In the second mode of STMM the DATABASE_MEMORY itself is set to
AUTOMATIC. Now STMM also takes memory from the OS if it needs it and if it is
available. Also memory can be given back to the OS if no longer needed. This
mode is only available on the Windows and AIX platform.

STMM is another important new feature of DB2 9 and it is supported by
SAP. Note that STMM is switched on by default for all memory consumers in new
DB2 9 installations. If you migrate from DB2 UDB V8 to Version 9 STMM is
switched off by default. There is much more to say about STMM, for instance how
it behaves if the DPF (database partitioning feature) is used. Instead of going
into all the details here I like to provide a list of recommend readings which I
suggest to look over before starting with STMM in your SAP installation.

Before using STMM you should read SAP notes <a href=”″ target=”_blank”>899322</A> (there is a special section about STMM) and <a href=”″ target=”_blank”>976285

More DB2 9 features…

There is of course much more to say about DB2 9. Let me finish this blog series
by just mentioning a few more interesting features and enhancements:

    1. A new DB2 Client, the so called ‘CLI driver’, is available on SAP’s
      DB2 V9 GA DVD. This new client is very lightweight, requires no installation
      and will ease the maintenance of DB2 systems. The CLI Driver will be supported
      with the forthcoming SAP Netweaver Release. Expect to find more on this in SDN.
    2. Automatic Storage which is available since version 8 is now also
      possible then partitioned database servers (DPF, database partitioning feature)
      are used.
    3. The RECOVER DATABASE command has now a RESTART option which allows a
      ‘resumable recover’.
    4. The new view SYSIBMADM.DBPATHS contains a list of all file system
      paths which are needed by the database. This information is especially helpful
      for flash copies or file system backups.

I suggest to have a look at SAP note 930487 which has some more details.

DB2 9 and SAP

Based on the strategic partnership between IBM and SAP the new DB2 9 was
improved in many areas. Besides features like pureXML and Label Based Access
Control there are a lot of improvements with special relevance for SAP. My IBM
colleague Jens Seifert and I discussed in this blog series

In the meantime our colleagues from IBM started to work on the next
version of DB2, and again SAP is contributing to the product definition. I hope
that we were able to show you one thing in our blog series: if you need to
decide on the database for your SAP installations, think about DB2!

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply