Dear DB2 LUW Database Administrators,
I continued to be surprized and amazed by the number of database snapshots that I've seen recently where "Database Files Closed" is greater than zero, and often times much greater than zero. It is not reasonable to expect DB2 to perform its best when it is busy wasting valuable CPU cycles by closing and opening files (or devices) - an operation that can take significant relative time and slow down SQL response times.
The culprit is often database configuration parameter MAXFILOP. The default value of 64 was invented around 1992 when we were lucky if a machine had 128MB of memory. 64 is completely unreasonable, unacceptable, unthinkable, and unrealistic for today's hardware. If this is your value, or if you are observing "Database Files Closed" greater than zero, then, PLEASE, HURRY UP and INCREASE the value to a minimum of 512, or incrementally by steps of 512, until Database Snapshots regularly show "Database Files Closed = 0".
If you like, go ahead and set MAXFILOP to its maximum allowable value of 32,767. This parameter hurts a lot if it is too small, but I am not aware of any adverse consequences of having it too large during the past TWELVE years.
Here is an example of the command syntax:
- db2 "update DB CFG for DBNAME using MAXFILOP 512"
If you are not sure what I'm talking about, or if you are unsure if your database is plagued by this severe problem, then give Brother-Eagle™ a try (it's free) and let it do the analysis for you. Database Files Closed is just one of 24 key health, efficiency, cost, and problem metrics that it monitors. Check out the full list here.
Until next time,