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.

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"

---