The operating system, by default, caches file data that
is read from and written to disk.
A typical read operation involves physical disk access to read
the data from disk into the file system cache, and then to copy the
data from the cache to the application buffer. Similarly, a write
operation involves physical disk access to copy the data from the
application buffer into the file system cache, and then to copy it
from the cache to the physical disk. This behavior of caching data
at the file system level is reflected in the FILE SYSTEM CACHING clause
of the CREATE TABLESPACE statement. Since the database manager manages
its own data caching using buffer pools, the caching at the file system
level is not needed if the size of the buffer pool is tuned appropriately.
Note: The
database manager already prevents caching of most DB2® data, except temporary data and LOBs on AIX®, by invalidating the pages from
the cache.
In some cases, caching at the file system level and in the buffer
pools causes performance degradation because of the extra CPU cycles
required for the double caching. To avoid this double caching, most
file systems have a feature that disables caching at the file system
level. This is generically referred to as non-buffered I/O.
On UNIX, this feature is commonly
known as Direct I/O (or DIO). On Windows, this is equivalent to opening the
file with the FILE_FLAG_NO_BUFFERING flag. In addition, some file
systems such as IBM® JFS2 or
Symantec VERITAS VxFS also support enhanced Direct I/O, that is, the
higher-performing Concurrent I/O (CIO) feature. The database
manager supports this feature with the NO FILE SYSTEM CACHING table
space clause. When this is set, the database manager automatically
takes advantage of CIO on file systems where this feature exists.
This feature might help to reduce the memory requirements of the file
system cache, thus making more memory available for other uses.
Prior to Version 9.5, the keyword FILE SYSTEM CACHING was implied
if neither NO FILE SYSTEM CACHING nor FILE SYSTEM CACHING was specified.
With Version 9.5, if neither keyword is specified, the default, NO
FILE SYSTEM CACHING, is used. This change affects only newly created
table spaces. Existing table spaces created prior to Version 9.5 are
not affected. This change applies to AIX, Linux, Solaris, and Windows with the following exceptions, where
the default behavior remains to be FILE SYSTEM CACHING:
- AIX JFS
- Solaris non-VxFS
- Linux for System z®
- All SMS temporary table space files
- Long Field (LF) and Large object (LOB) data files
in SMS permanent table space files.
To override the default setting, specify FILE SYSTEM CACHING
or NO FILE SYSTEM CACHING.
Supported configurations
Table 1 shows the supported configuration
for using table spaces without file system caching. It also indicates:
(a) whether DIO or enhanced DIO will be used in each case, and (b)
the default behavior when neither NO FILE SYSTEM CACHING nor FILE
SYSTEM CACHING is specified for a table space based on the platform
and file system type.
Table 1. Supported configurations for table spaces without file system
caching| Platforms |
File system type and minimum level
required |
DIO or CIO requests submitted by the
database manager when NO FILE SYSTEM CACHING is specified |
Default behavior when neither
NO FILE SYSTEM CACHING nor FILE SYSTEM CACHING is specified |
|---|
| AIX 5.3 and higher |
Journal File System (JFS) |
DIO |
FILE SYSTEM CACHING (See Note 1.) |
| AIX 5.3
and higher |
General Parallel File System (GPFS™) |
DIO |
NO FILE SYSTEM CACHING |
| AIX 5.3 and higher |
Concurrent Journal File System (JFS2) |
CIO |
NO FILE SYSTEM CACHING |
| AIX 5.3 and higher |
VERITAS Storage Foundation for DB2 4.1
(VxFS) VERITAS Storage Foundation for DB2 5.0
(VxFS)
|
CIO |
NO FILE SYSTEM CACHING |
| HP-UX Version 11i v2, 11iv3 (Itanium) |
VERITAS Storage Foundation 4.1 (VxFS) VERITAS Storage Foundation
5.0 (VxFS) (See Note 6.)
|
CIO |
FILE SYSTEM CACHING |
| Solaris 9 |
UNIX File System (UFS) |
DIO |
FILE SYSTEM CACHING (See Note 2.) |
| Solaris 10 |
UNIX File System (UFS) |
CIO |
FILE SYSTEM CACHING (See Note 2.) |
| Solaris 9, 10 |
VERITAS Storage Foundation for DB2 4.1
(VxFS) VERITAS Storage Foundation for DB2 5.0
(VxFS)
|
CIO |
NO FILE SYSTEM CACHING |
| Linux distributions
SLES 10 SP2 or higher, and RHEL 5.2 or higher (on these architectures:
x86, x64, POWER®)
|
ext2, ext3, reiserfs |
DIO |
NO FILE SYSTEM CACHING |
| Linux distributions
SLES 10 SP2 or higher, and RHEL 5.2 or higher (on these architectures:
x86, x64, POWER)
|
VERITAS Storage Foundation for DB2 4.1
(VxFS) VERITAS Storage Foundation for DB2 5.0
(VxFS)
|
CIO |
NO FILE SYSTEM CACHING |
| Linux distributions
SLES 10 SP2 or higher, and RHEL 5.2 or higher (on this architecture: zSeries)
|
ext2, ext3 or reiserfs on a Small Computer System Interface
(SCSI) disks using Fibre Channel Protocol (FCP) |
DIO |
FILE SYSTEM CACHING |
| Windows |
No specific requirement, works on all DB2 supported file systems |
DIO |
NO FILE SYSTEM CACHING |
Note: - On AIX JFS, FILE SYSTEM
CACHING is the default.
- On Solaris UFS, NO FILE SYSTEM CACHING is the default.
- The VERITAS Storage Foundation for the database manager might
have different operating system prerequisites. The platforms listed
above are the supported platforms for the current release. Consult
the VERITAS Storage Foundation for DB2 support
for prerequisite information.
- If SFDB2 5.0 is used, the 5.0 MP3 RP2 release (or
higher) must be used. This release includes fixes that are specific
to the 5.0 version.
- VERITAS Storage Foundation 5.0 MP3 and 5.1 now include
CIO support in the base product, and no longer require the DB edition
version of the product.
- On HP, CIO is enabled with 5.0.1 OnlineJFS and does not require
an additional VERITAS license.
- If you do not want the database manager to choose NO FILE SYSTEM
CACHING for the default setting, specify FILE SYSTEM CACHING in the
relevant SQL, commands, or APIs.
Examples
Example 1: By default,
this new table space will be created using non-buffered I/O; the NO
FILE SYSTEM CACHING clause is implied:
CREATE TABLESPACE table space name ...
Example
2: On the following statement, the NO FILE SYSTEM CACHING clause
indicates that file system level caching will be OFF for this particular
table space:
CREATE TABLESPACE table space name ... NO FILE SYSTEM CACHING
Example
3: The following statement disables file system level caching
for an existing table space:
ALTER TABLESPACE table space name ... NO FILE SYSTEM CACHING
Example
4: The following statement enables file system level caching
for an existing table space:
ALTER TABLESPACE table space name ... FILE SYSTEM CACHING
DB2 Version 9.7 for Linux, UNIX, and Windows