Last week I did an IDUG sponsored webcast on the topic of autonomic computing. I wil be excerpting these features over the next few blogs. If you wish to view a recording of the webcast here is the link:  http://www.quest.com/events/listdetails.aspx?contentid=7611&technology=&prod=&prodfamily=&loc=

 

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.
 

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.

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:

 

 

Next time, I will be covering the new automatic storage features introduced into DB2 LUW.