Table spaces without file system caching

The recommended method of enabling or disabling non-buffered I/O on UNIX, Linux®, and Windows is at the table space level.

This allows you to enable or disable non-buffered I/O on specific table spaces while avoiding any dependency on the physical layout of the database. It also allows the database manager to determine which I/O is best suited for each file, buffered or non-buffered.

The NO FILE SYSTEM CACHING clause is used to enable non-buffered I/O, thus disabling file caching for a particular table space. Once enabled, based on platform, the database manager automatically determines which of the Direct I/O (DIO) or Concurrent I/O (CIO) is to be used. Given the performance improvement in CIO, the database manager uses it whenever it is supported; there is no user interface to specify which one is to be used.

To obtain the maximum benefits of non-buffered I/O, it might be necessary to increase the size of buffer pools. However, if the self-tuning memory manager is enabled and the buffer pool size is set to AUTOMATIC, the database manager will self-tune the buffer pool size for optimal performance. Note that this feature is not available before Version 9.

To disable or enable file system caching, specify the NO FILE SYSTEM CACHING or the FILE SYSTEM CACHING clause in the CREATE TABLESPACE or ALTER TABLESPACE statement. The default setting is used if neither clause is specified. In the case of ALTER TABLESPACE, existing connections to the database must be terminated before the new caching policy is applied. The FILE SYSTEM CACHING setting might apply earlier than expected under the following conditions:
  • When the number of files that are opened by the database is near but less than or equal to the value of the configured parameter MAXFILOP. In this case, table space container files might be reopened by using the altered FILE SYSTEM CACHING setting.
  • When a new table reorganization is initiated with LONGLOBDATA after the FILE SYSTEM CACHING setting is altered. In this case, the FILE SYSTEM CACHING setting only affects the file I/O operations that are performed by the table reorganization process.
Note: If an attribute is altered from the default to either FILE SYSTEM CACHING or NO FILE SYSTEM CACHING, there is no mechanism to change it back to the default.

This method of enabling and disabling file system caching provides control of the I/O mode, buffered or non-buffered, at the table space level.

To determine whether file system caching is enabled, query the value of the fs_caching monitor element for the table space in the MON_GET_TABLESPACE table.

Alternate methods to enable/disable non-buffered I/O on UNIX, Linux, and Windows
Some UNIX platforms support the disabling of file system caching at a file system level by using the MOUNT option. Consult your operating system documentation for more information. However, it is important to understand the difference between disabling file system caching at the table space level and at the file system level. At the table space level, the database manager controls which files are to be opened with and without file system caching. At the file system level, every file residing on that particular file system will be opened without file system caching. Some platforms such as AIX® have certain requirements before you can use this feature, such as serialization of read and write access. although the database manager adheres to these requirements, if the target file system contains files not from the database manager, before enabling this feature, consult your operating system documentation for any requirements.
Note: The now-deprecated registry variable DB2_DIRECT_IO, introduced in Version 8.1 FixPak 4, enables no file system caching for all SMS containers except for long field data, large object data, and temporary table spaces on AIX JFS2. Setting this registry variable in Version 9.1 or later is equivalent to altering all table spaces, SMS and DMS, with the NO FILE SYSTEM CACHING clause. However, using DB2_DIRECT_IO is not recommended, and this variable will be removed in a later release. Instead, you should enable NO FILE SYSTEM CACHING at the table space level.
Alternate methods to enable/disable non-buffered I/O on Windows
In previous releases, the performance registry variable DB2NTNOCACHE could be used to disable file system caching for all Db2® files in order to make more memory available to the database so that the buffer pool or sort heap can be increased. The difference between DB2NTNOCACHE and using the NO FILE SYSTEM CACHING clause is the ability to disable caching for selective table spaces. Starting in Version 9.5, since the NO FILE SYSTEM CACHING is used as the default, unless FILE SYSTEM CACHING is specified explicitly, there is no need to set this registry variable to disable file system caching across the entire instance if the instance includes only newly created table spaces.
Performance considerations
Non-buffered I/O is essentially used for performance improvements. However, sometimes performance degradation might be due to, but is not limited to, a combination of a small buffer pool size and a small file system cache. To improve performance, the following are suggestions:
  • If self-tuning memory manager is not enabled, enable it and set the buffer pool size to automatic using ALTER BUFFERPOOL name SIZE AUTOMATIC. This method allows the database manager to self-tune the buffer pool size.
  • If self-tuning memory manager is not to be enabled, increase the buffer pool size in increments of 10 or 20 percent until performance is improved.
  • If self-tuning memory manager is not to be enabled, alter the table space to use FILE SYSTEM CACHING. This method essentially disables the non-buffered I/O and reverts to buffered I/O for container access.
Performance tuning must be tested in a controlled environment before implementing it on the production system.
When choosing to use file system files versus devices for table space containers, you should consider file system caching, which is performed as follows:
  • For DMS file containers (and all SMS containers), the operating system might cache pages in the file system cache (unless the table space is defined with NO FILESYSTEM CACHING).
  • For DMS device container table spaces, the operating system does not cache pages in the file system cache.