Constant evaluation of DB2 parameters is essential to offer good database performance, but what to do when you have more than 300 databases in different geographic locations and simply cannot check out them every day? This could happen when dealing with customers with none or poor DBA’s. In Brazil that’s a very common scenario as many of customers starts with DB2 Express Edition and don’t want to pay a DBA to maintain the environment working. The software provider needs to work on those databases until someone take this position.
Well, I’m not here to complain about this scenario but just give you the solution provided by DB2 9, even the Express-C edition in both architectures 32 and 64-bit. That’s called STMM: Self Tuning Memory Management. But how it works, what’s the consequences and how to enable it?
How STMM works?
STMM works evaluating the workload requirements and changing database parameters to fulfill the workload needs. This means the more resources are needed, they’re increased. STMM only lower memory consumers when the workloads decrease, or when the amount of system’s free memory is too low. Buffer pools are also monitored and their page size changes as needed. The main goal of this feature is to work as a robot DBA changing the parameters before reach the memory consumption limits and trying to offer the best values for each consumer with equilibrium.
If your database has different workloads concurrently or in short period of times you’ll notice very poor tunings by STMM, sometimes leading to errors in memory consumer parameters (especially with PCKCACHESZ) as they cannot grow as fast as needed. When you have more than 2 databases with STMM enabled in the same instance this makes the situation worse.
However, even if STMM is giving you more headaches than managing db memory effectively it provides a LOG file to see which parameters are increased and get a notion of the limits required on massive workloads. The files are written to a folder called STMMLOG in the same path of the instance (on Windows Servers they’re written under %PROGRAMDATA% - C:\ProgramData\IBM\DB2\DB2COPY1\DB2\stmmlog for an instance called DB2COPY1).
How to enable STMM in a database?
You’ll just need to set some parameters in the database. And it’s done! I’ve created a Windows Batch file to perform this operation (can easily converted to Unix/Linux shell script):
REM - File...: db2stmm.cmd
REM - Script.: Enables STMM for a database
REM - Author.: Tiago J. Adami
REM - Version: 1.0 (14/08/2011)
REM - Usage:
REM - db2stmm <database_name> <user_name> <user_name_password>
REM - // call it under "db2cmd"
db2 "update database configuration for %1 using SELF_TUNING_MEM ON"
db2 "update database configuration for %1 using PCKCACHESZ AUTOMATIC"
db2 "update database configuration for %1 using DBHEAP AUTOMATIC"
db2 "update database configuration for %1 using SORTHEAP AUTOMATIC"
db2 "update database configuration for %1 using SHEAPTHRES_SHR AUTOMATIC"
db2 "update database configuration for %1 using MAXLOCKS AUTOMATIC"
db2 "update database configuration for %1 using LOCKLIST AUTOMATIC"
db2 "update database configuration for %1 using DATABASE_MEMORY AUTOMATIC"
REM – STMTHEAP is not managed by STMM, but if you’re using version 9.7 its very nice to set it to AUTOMATIC when STMM is active
db2 "update database configuration for %1 using STMTHEAP AUTOMATIC"
db2 "connect to %1 user %2 using %3"
REM - Add bufferpool entries here
db2 "alter bufferpool ibmdefaultbp size automatic"
db2 "connect reset"
When STMM is useful?
- STMM logs are very useful when you don’t know the workload of your – or a customer – database. After reading them, you can start to tune parameters;
- On test environments with multiple databases on one instance STMM reduce significantly the overall operating system memory consumption;
- Production environments where you cannot be aware of the workloads in a regular basis STMM should play a important role mantaining the databases working;
If you have questions or corrections, fell free to contact me. If you have doubt on how STMM works, I have at least 4 servers in my lab with 2 or more instances each one, each instance with more than 10 databases. All of them STMM enabled. Trust me, STMM saved me a lot of nights of sleep...