DB2 LUW MAXFILOP

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,

Scott

Posted by  
on August 10, 2006, 2:39 pm
Please confirm the maximum value for maxfilop can be set to 32767 for DB2 v8.2 (FP8). IBM documentation says that the limit is 1950.
Posted by  
on August 14, 2006, 3:25 pm
Unix value range: 2 - 1950
Windows value range: 2 - 32768

Posted by  
on September 7, 2006, 9:49 am
From my recent experiences at different customer sites, another contributing factor to the high number of "Database Files Closed" is SMS tablespaces which use significantly more files than DMS tablespaces. Another good reason to say goodbye to SMS! ;-)
Posted by scott  
on November 6, 2006, 2:22 pm
Robert, thanks for clarifying the Windows and UNIX difference. -Scott