The default I/O mechanism for table space
containers on most AIX®, Linux, Solaris, and Windows platforms is CIO/DIO (concurrent
I/O or direct I/O). This default provides an increase of throughput
over buffered I/O on heavy transaction processing workloads and rollbacks.
The FILE SYSTEM CACHING or NO FILE SYSTEM CACHING attribute specifies
whether I/O operations are to be cached at the file system level:
- FILE SYSTEM CACHING specifies that all I/O operations in the target
table space are to be cached at the file system level.
- NO FILE SYSTEM CACHING specifies that all I/O operations are to
bypass the file system-level cache.
If large object (LOB) data is inlined, then it is
accessed as regular data and uses the I/O method (buffered or non-buffered)
specified for the table space FILE SYSTEM CACHING attribute.
If large object (LOB) data is not inlined, then the
following statements apply:
- For SMS table spaces, non-buffered I/O access is not requested
for long field (LF) data and large object (LOB) data even when the
NO FILE SYSTEM CACHING table space attribute is set. Buffering occurs
in the file system cache, subject to operating system configuration
and behavior, and potentially improves performance.
- For DMS table spaces, DB2 does not distinguish between different
data types when performing I/O. Buffering of LF or LOB data does not
occur unless the table space is configured with FILE SYSTEM CACHING
enabled. If buffering of LF or LOB data in DMS tables spaces is wanted
for performance reasons, then you can place this data in a separate
DMS table space and explicitly enable FILE SYSTEM CACHING.
The following interfaces contain the FILE SYSTEM CACHING attribute:
- CREATE TABLESPACE statement
- CREATE DATABASE command
- sqlecrea() API (using the sqlfscaching field of the SQLETSDESC structure)
When this attribute is not specified on the CREATE TABLESPACE statement,
or on the CREATE DATABASE command, the database
manager processes the request using the default behavior based on
the platform and file system type. See File system caching configurations for the exact behavior. For the sqlecrea() API, a value of 0x2 for the field sqlfscaching field, instructs the database manager to use
the default setting.
The following tools currently interpret the value for
FILE SYSTEM CACHING attribute:
- GET SNAPSHOT FOR TABLESPACES command
- db2pd -tablespaces command
- db2look -d <dbname> -l command
For
db2look, if the FILE SYSTEM CACHING attribute
is not specified, the output does not contain this attribute.
Example
Suppose that the database
and all related table space containers exist on an AIX JFS file system and the following statement
was issued:
DB2 CREATE TABLESPACE JFS2
If
the attribute was not specified, the database manager uses NO FILE
SYSTEM CACHING.