Table space containers use concurrent I/O or direct I/O by default

The default I/O mechanism for created table space containers on most AIX®, Linux®, and Windows operating systems 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 sqlfscaching field, instructs the database manager to use the default setting.

Note that 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 reside 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.