Can we set database_memory to AUTOMATIC on DB2 9.5.5 for Linux?

Take a look at:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Yes , but it is worth analysing what this means in a multithreaded environment. and How this relates to instance_memory .

My preference on Linux is to use COMPUTED and to hard code the following STMM configurations (example settings given below):

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = COMPUTED
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = 8192
Percent. of lock lists per application (MAXLOCKS) = 30
Package cache size (4KB) (PCKCACHESZ) = 8192
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 50000
Sort list heap (4KB) (SORTHEAP) = 10000

I also recommend that Bufferpools be hard-coded and not set to -2. Setting bufferpools to about 50% of total system memory is a good starting point (but not larger than the tables and indexes using a given bufferpool). Also, you will need to take into consideration all bufferpools for all databases on the server when using the 50% number.

Note that even though STMM is ON, if the above parameter are hard-coded (as they would be in 8.2) then it shows as "Inactive" when you do "get db cfg show details" (you must be connected to the database to run this command).

To tune the above parms do a "snapshot for database":

1. If the package cache is frequently showing overflow, then increase package cache setting. If it never shows overflow, then you can lower it.
2. If sorts are overflowing frequently, then increase the 2 sort parms proportionately. If not overflow, you can lower it.
3. Check you bufferpool hit ratios for data and indexes, and adjust the bufferpool sizes (if you have more memory) to keep it as high as possible.

Jack, Can you explain what this means in a multithreaded environment and how this relates to instance_memory?

Marcus,

What is the difference between COMPUTED and AUTOMATIC for DATABASE_MEMORY on DB2 Linux? Why do you set SELF_TUNING_MEM to ON if not using STMM?

What do you recommend for INSTANCE_MEMORY? COMPUTED or AUTOMATIC?

I don't set STMM to ON, it is ON by default. But it is Inactive if the parameters it controls (see config above) are all set to specific values.

AUTOMATIC means that STMM can control the database memory when the other parms increase or decrease according to STMM. COMPUTED means that DB2 simply adds up the various components that make up the database memory that you hard-coded (although it can increase somewhat beyond that if needed).

If you have database memory set to AUTOMATIC, but STMM is OFF or Inactive, then it automatically changes to COMPUTED at db activation time.

For Instance Memory on Linux, I set it to a hard-code value if there is more than one instance. This is maximum amount of memory that can be used, and DB2 does not allocate it unless needed by the all the other memory configs defined in dbm and db.

Why do we need STMM if we're not going to use it?

What is the best way to see how much DATABASE_MEMORY and INSTANCE_MEMORY DB2 is using?

IBM needs it to help convince management that they should purchase DB2 because it automatically configures itself without having to have any of those pesky (and expensive) DBA's. Unfortunately, IMO it does not work well (at least so far).

There are (at least) two ways:

• db2mtrk
• db2pd

Check the Command Reference manual for the different options of the above commands.

Do you think this is helping IBM? Do companies make their decision about which DBMS to purchase based on STMM? I don't want to be replaced by STMM Do you know if other DBMS have STMM?

There have been many studies and marketing claims as to which database has the lowest cost of ownership. IBM has undertaken significant efforts to automate DB2 to lower the need for DBA's (or have them concentrate on more important things). Unfortunately two things have happened:

• Automation features like STMM don't always work well, sometimes causing serious problems that require many more hours of DBA work than would be needed to just manually tune the database.
• DB2 is adding other features at an amazing rate to be competitive with the features of other databases, and this has made DB2 significantly more complex than before, so the overall need for qualified DBA's is even higher than before.

So I don't think you need to worry about your DBA job going away. The biggest worry is being able to keep your database up and running with these "so-called" improvements that have implemented by IBM.

Excellent. Thank you.

Marcus_A， hello，can it somehow draw into conclusion that most of the time db2 is running slow because database memory is set to auto? Or at least it is a good starting point to look at?