DB2 9 self-tuning memory management

Before you start

Introduction

This tutorial is based on the IBM DB2 9 self-tuning memory management feature. The tuner works within the memory limits defined by the database_memory configuration parameter. The value of database_memory can itself be automatically tuned on Windows(R) and AIX(R). When self-tuning is enabled for database_memory (when it is set to AUTOMATIC), the tuner determines the overall memory requirements for the database and increases or decreases the amount of memory allocated for database shared memory depending on the current database requirements. For example, if the current database requirements are high, and there is sufficient free memory on the system, more memory will be consumed by database shared memory. Once the database memory requirements drop, or the amount of free memory on the system drops too low, some database shared memory is released.

This tutorial provides hands-on experience in the five basic areas:

1. Activating self-tuning memory
2. Determining which memory consumers are enabled for self tuning
3. Determining the current size of DB2 memory usage
4. Automatic tuning in a production environment
5. Deactivating self-tuning memory

Further description of the feature can be found in the article " Self-tuning memory in DB2 9" (developerWorks, June 2006)..

Back to top

About this tutorial

The following exercises enable you to work with the DB2 self-tuning memory feature and to have available memory resources dynamically distributed among several memory consumers, including sorts, the package cache, the lock list, and buffer pools.

They have been developed to demonstrate one or more tasks found within each of the areas.

Objectives

This tutorial helps familiarize you with concepts and capabilities of self tuning in DB2 9:

1. Self-tuning memory overview and logon
2. Self-tuning memory and SORTHEAP
3. Self-tuning memory and PCKCACHESZ
4. Self-tuning memory and LOCKLIST
5. Self-tuning memory and health checking

System requirements

To run the examples in this tutorial, you need the following installed on your machine:

• DB2 9 Data Server
• Microsoft Windows 2000 or later and an account with administrator privileges or Linux (Validated edition) with root access
• Java Runtime Environment 1.4.2 or later
• Hardware requirements referred to on the DB2 9 system requirements page

DB2 9 Express-C is available from the DB2 9 Data Server link above. See the tutorial "DB2 XML evaluation guide (developerWorks, June 2006) for steps on installing DB2. Unless the DB2 configuration is altered, then DB2 will automatically start after installation.

Use the samples scripts and data provided in the stmm_zipped.zip file (see Downloads section) to demonstrate the concepts in this tutorial. Extract the contents into a subdirectory called stmm_scripts ( C:\stmm_scripts or home/userid/stmm_scripts, for example). This directory will be referred to simply as stmm_scripts throughout the tutorial. This tutorial assumes that you have used the default directories for the DB2 installation and all the DB2 exercises are performed by a database administrator id.

1 of 11 | Next

Comments

Back to top

Table of contents

• Before you start
• Self-tuning memory overview and logon
• Self-tuning memory and SORTHEAP
• Self-tuning memory and PCKCACHESZ
• Self-tuning memory and LOCKLIST
• Self-tuning memory and bufferpool size
• Self-tuning memory and health checking
• Conclusion
• Download
• Resources
• About the authors

---

1 of 11 | Next

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers