The beginnings of autonomics
In early releases of DB2 LUW the database and instance configurations were more or less static. In order to make a change the instance or database had to be taken offline for any changes to be made. The move towards an automated tuning process began in V8 where you could start to dynamically change parameters without having to shut down the database. There were quite a few limitations at this stage however.
Self Tuning Memory Manager- STMM
With DB2 LUW V9.1, IBM introduced the concept of automated self tuning memory. The feature simplifies the task of managing package cache, sort heap, buffer pools, and locklists. The amount of memory allocated to these areas is constrained by the maximum memory allocated to the database shared memory. This parameter can be left on or once a typical workload has been set the values can be frozen by turning off the automatic parameter. Generally running the auto tune for a few hours under typical workloads will produce optimal tuning parameters.
There are limitations to using self tuning memory.
- Good Candidates for STMM
- Box has lots of free memory
- Workload changes throughout the day
- Inexperienced DBAs
- Multiple bufferpools
- Poor Candidates for STMM
- Databases with constrained memory
- Heavily used server
- Database_memory set low
- Volatile workloads
- Constantly changing workloads cannot be tuned effectively
- Static workload throughout the day
The level of automation is continuing to evolve. In v9.5 you can now automate at the instance level, database heap, and new application memory. By specifying the database_memory parameter as AUTOMATIC, DB2 will also dynamically adjust the shared memory based on database workload. If you need to view what the current memory allocations are you can use a snapshot or the GET DATABASE CONFIGURATION command. All memory changes are recorded in memory tuning files which can be found in the STMMLOG directory:
STMM Log
Next time, I will be covering the new automatic storage features introduced into DB2 LUW.