Brother-Eagle® DB2 Performance
Advice: Files Closed

Most operating systems have limits on the number of files that can be open at any one time.

DB2 LUW attempts to be a good citizen within the contraints of Operating System limits by managing how many files it has open at any one time.

The file open limit for DB2 is governed by the database configuration parameter MAXFILOP. The default value for MAXFILOP is a mere 64. This default value is grossly too small for most of today's large databases.

Brother-Eagle will indicate a warning if three or more files have been closed. An alert will be highlighted if five or more files have been closed.

Closing files needlessly consumes CPU time and slows down SQL performance. If you observe any files being closed, either:

• Gradually increase the value of MAXFILOP by 10% increments until repeated monitoring verifies that files are no longer being closed.
• Set the value of MAXFILOP to its maximum allowed value and forget about it. Use the command:
  db2 "update db cfg for DBNAME using MAXFILOP 32767"

View full list of DB2 LUW advice topics