75 ways to demystify DB2: #52: Techtip: "TOO MANY OPEN FILES" in db2diag.log under Linux environment

Body

Experiencing "TOO MANY OPEN FILES" in db2diag.log under Linux environment.

In Linux platforms it's more common to experience "TOO MANY OPEN FILES" from one of db2 enigne process' like db2sysc.

Messages in the db2diag.log will contain texts similar to following :

MESSAGE : ZRC=0x850F0006=-2062614522=SQLO_FHNL "TOO MANY OPEN FILES"

DIA8306C Too many files were opened.

CALLED : OS, -, open OSERR: EMFILE (24)

Increasing the "nofiles" in the ulimit for the instance owner or, any other users starting or using DB2 might not help.

There might be few reasons behind this issue.

Linux being an open source UNIX operating system has multiple providers who own specific flover of the same. Over the time DB2 have experineced sevearal file

descriptor usage related issues at Linux operating system level.

Detection of operating system level file descriptor issues is difficult and coordinating the analysis with each vendor is complex.

That is why even when "nofiles" is set much higher or, even "unlimited" DB2 inernally cap the value to be 64K (65536) for Linux operating system.

It might show the value to be 65534 in the db2diag.log

Similar to following :

nofiles (S/H) = 65534 / 65534

No of files 64KB is normally sufficient for DB2.

But, there are situations when it might still hit the limit.

Using of alternate page cleaning DB2_USE_ALTERNATE_PAGE_CLEANING=ON might have more possibilities of causing the issue.

But, it will mostly depend on,

- How many databases are there under the instance

- How many containers are being used by each database under one instance.

Reducing number of containers might not be suggested as that can affect in the performance.

Howerver, distributing multiple databases under different instances will be suggested.

But, most important action which could be taken is related to the database configuration parameter MAXFILOP (Maximum database files open per database).

In Linux 64bit setup the maximum and default value of this parameter is 61440.

This value is very close to 64K.

So, more than one databases with large number of file descriptor need under one instance can hit the total "nofiles" ulimit limit of 64K (DB2 imposed) easily.

Tuning down the MAXFILOP is suggested if no other actions help.

By reducing the MAXFILOP per database will trigger file descriptor cleanups earlier by DB2. But, that usually have minimal performance impact.

If the too many open file messages show up from a non-DB2 server related process or, db2fmp process then it's most likely due to 32bit vendor codes being used there. In that case, necessary actions to be taken from specific vendor code point of view.