Introduction

Counting instance configuration parameters, database configuration parameters and registry variables, DB2® gives you over 300 global settings to control your database. The intent is not to blind you with parameters, but to give you detailed control, if you need it, over how the database performs and consumes operating system resources.

Consider the fictional and satirical band Spinal Tap, which was known more for high-volume rock and roll more than high IQ, and who believed that if the volume setting on their amplifier went up to 11, it would be 10% louder than amplifiers that went to 10. With DB2, on the other hand, it is really possible to achieve high performance without turning everything up to 11. The purpose of this article, when used in conjunction with the DB2 Administration Guide , will show you how to do it.

Back to top

The basics

To start, don't panic at the thought of 300 parameters; many of them are fixed when the instance or the database is created (such as the database territory, code page, code and country code). Others, such DIAGLEVEL and DIAGPATH, can stay at their default unless you need to troubleshoot. And, for the vast majority of performance-oriented configuration parameters, you can have DB2 recommend a value by running the Performance Wizard from the Control Center:

Database configuration parameters apply to the database (I'll use the famous SAMPLE database for the rest of this article in examples). Database manager configuration parameters apply to all databases created under that instance. If I run the db2ilist command, I can see the instances on the machine:

C:\dbm>db2ilist


DB2CTLSV
DB2
  

Registry variables generally apply to all DB2 instances running on the machine. They are described in an Appendix of the Administration Guide. You set them by running db2set. For example, the following db2set command enables hash join on all DB2 instances:

db2set db2_hash_join=yes

The command db2set -lr lists all available registry variables. You display a value's setting by running db2set with no argument. For example, the following command tells you the name of the default DB2 instance on Windows:

C:\dbm>db2set db2instdef
DB2

Display the settings of database manager parameters with this command:

DB2 GET DATABASE MANAGER CONFIGURATION 

A shorter form of the above command is:

DB2 GET DBM CFG

Display the settings of database configuration parameters with this command:

	DB2 GET DATABASE CONFIGURATION FOR <<dbname>>

A shorter form of the above command is:

DB2 GET DB CFG FOR SAMPLE

Back to top

Dynamic and static configuration parameters

In DB2 Version 7, most database manager configuration parameters are static: you have to change them and restart DB2 to have them take effect. Most database configuration parameters require all applications to disconnect from and reconnect to the database to take effect. IBM understands the awkwardness of this, and most DB2 configuration parameters will become dynamic in a future release, allowing you to see the results of changing them immediately. In Version 7, examples of dynamic configuration parameters include:

• At the instance level, the default database system monitor switches (dft_monswitches)
• At the database level, the number of commits to group (mincommit)

Back to top

Tracking changes in configuration parameters

When you run your hardware and software with systems management discipline you only make a change when you can monitor the effect and know how to back it out if the effect makes the system worse. Because making multiple changes at the same time can make restoring desired behavior complicated, some systems management disciplines advocate changing only one thing at a time.

If you were happy with the way DB2 is running, but then encounter an unexplained problem, when you contact IBM for help, you might be asked: "What was the last thing you changed?" DB2 can help here. For example, the DB2DIAG.LOG is a record of interesting DB2 events. DB2 also has tracing, logs, an Event Monitor, and for backup and restore, a History file.

To track what's changed in the database manager and database configurations, you can create a table to store historical values for each. In the example below, I've added an identity column as well, and will show how it can be used further on:

CREATE TABLE DBMCFG 
       (CFGPARM VARCHAR(254) NOT NULL,
        VALUE VARCHAR(254),
        CAPTURE_TSTMP TIMESTAMP NOT NULL WITH DEFAULT,
        ITERATION INT GENERATED ALWAYS AS IDENTITY)

CREATE TABLE DBCFG 
      (CFGPARM VARCHAR(254) NOT NULL,
       VALUE VARCHAR(254),
       CAPTURE_TSTMP TIMESTAMP NOT NULL WITH DEFAULT, 
       ITERATION INT GENERATED ALWAYS AS IDENTITY)

These tables allow you to record the values of configuration parameters at points in time. Note that if DB2 is never stopped, most of the database manager parameters (apart from the first iteration of each) may not apply, because they are static. Similarly, if DB2 has never been stopped and one or more applications have stayed connected, changes to database configuration parameters will not yet be effective. Knowing the parameter values at a point in time might help debug problems stated like this: "Performance was good on Wednesday, and then it got bad on Thursday even though we had fewer users on the system."

If you remember to capture all values to these two tables whenever you change a configuration parameter, you'll be able to see if DB2 and application behavior changes are related to configuration parameter settings. Capturing the values is shown with two IMPORT commands below. Making these parts of a script that starts DB2 can automate this record keeping. Each IMPORT command is preceded by writing the configurations to a file (all of this can be done in a C program using the DB2 interfaces described in the Administrative API Reference):

1. Here is how I write the configurations to a file:

   DB2 GET DBM CFG>DBMCFG.OUT DB2 GET DB CFG FOR SAMPLE>DBCFG.OUT

   
   
2. Below is how I capture the values into the table. The IMPORT command reads the contents of the *.OUT files and inserts the ASCII lines as rows into the two tables created above:

   DB2 IMPORT FROM DBMCFG.OUT OF ASC METHOD L ( 2 81,60 314 ) INSERT INTO DBMCFG DB2 IMPORT FROM DBCFG.OUT OF ASC METHOD L ( 2 81,60 314 ) INSERT INTO DBCFG

   
   

Note that the value of the configuration value is stored in both the CFGPARM and VALUE columns. It's stored by itself in the VALUE column so that we can use it for calculations. The VALUE column is so wide because some parameters, such as the location of logs, allow an absolute path, which can be quite long. If these tables grow large, consider a unique index on the combination of CFGPARM and INTERATION:

       CREATE UNIQUE INDEX CFGPARM_ITERATION ON DBCFG 
(CFGPARM,ITERATION)

Now a simple query can list everything that's changed. This query depends on knowing that there are 76 database parameters, but this will vary by the DB2 product and release you are running. Similarly, the number will be lower for the smaller database manager configuration (around 112 parameters). We'll use the TIME and DATE functions to turn the timestamp into something easier to read, and order by these columns:

SELECT CFGPARM, VALUE, TIME(CAPTURE_TSTMP), DATE(CAPTURE_TSTMP)     
FROM DBCFG
      WHERE ITERATION>76 AND 
      CFGPARM NOT IN 
      (SELECT CFGPARM FROM DBCFG WHERE ITERATION<77)
      ORDER BY 4,3

The NOT IN is used to compare recent values against their original values. Like any sample code you get, if you have more ambition than the author, you can devise something quite valuable. For example, the Administration Guide groups some parameters by the types of memory they consume:

• Database shared memory
• Application shared memory
• Agent private memory
• Agent/application communication memory
• Database manager instance memory

If in the *.OUT files above, if you strip out special characters, the numeric values for memory configuration values can be stored in numeric fields and used to calculate the amounts of the five kinds of memory above. This, like many interesting questions you can ask yourself about your own use of DB2, is left as an exercise for the reader.

About the author

Rate this article

Comments

Back to top

Table of contents

• Introduction
• The basics
• Dynamic and static configuration parameters
• Tracking changes in configuration parameters
• About the author
• Comments

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

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers