db2表空间是否开启文件系统缓存

DB2 10.5 for Linux, UNIX, and Windows官方建议：

选择将文件系统文件和设备用于表空间容器时，应考虑文件系统高速缓存，它按如下所示执行：
• 对于 DMS 文件容器（和所有 SMS 容器），操作系统可能会将页高速缓存在文件系统高速缓存中（除非使用 NO FILESYSTEM CACHING 定义表空间）。
• 对于 DMS 设备容器表空间，操作系统不会将页高速缓存在 该文件系统高速缓存中。（当时的理解是设备本身是裸设备，开不开启文件系统缓存应该都没有影响。）

来自 https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0052377.html?view=embed

官网案例说明：
Alter your tablespace to NO FILESYSTEM CACHING if your tablespaces exclusively contain tables that do not include LOBs.
If your tablespace does contain tables that use LOBs, turn on file system caching with the ALTER TABLESPACE statement, as

ALTER TABLESPACE (tsname) ….. NO FILESYSTEM CACHING
ALTER TABLESPACE (tsname) ….. FILESYSTEM CACHING

File system caching is enabled or disabled at a tablespace level, so be cognizant of where tables
are allocated within tablespaces. This may involve a tuning effort to separate LOB and NON-LOB
tables into isolated tablespaces.

Why does this help performance?
Data that can be stored in rows is pulled into memory via buffer pools. LOBs, by design, cannot be pulled into buffer pools. For argument’s sake, let’s say a database is the back end for an application where all tables are lumped into one tablespace (USERSPACE1) using file system caching.

The LOB tables in USERSPACE1 will get a needed lift because they cannot be pulled into the buffer pool and will use file system caching. But, regular data in rows is passing through the file system cache and through DB2 buffer pools causing two memory steps instead of one. This double effort is not efficient and can cause slower response times. In our case study environment,we had over 400 tables. Only 15 contained LOBs, one of which was a critical table with extremely high access rates. Separating these tables into proper tablespaces that allowed proper cache management gave huge lift.

实际情形：从原库迁移到新库的时候，做了升级，当前版本是db2 10.5。
原版本的普通表空间容器都是裸设备，并且默认开启了文件缓存。

IBM工程师巡检的时候给出建议如下：
**DB2 V10.5.0.5 要求使用裸设备容器的表空间需设置 “no file system caching”，在表空间未按建议配置并且活动日志中存在联机业务或存在使 DB2 运行时将对表空间进行处理动作的同时将导致数据库异常。

如将来使用 DB2 V10.5.0.5 的业务系统投产，请在投产前检查所有表空间设置，确保所有使用裸设备作为容器的表空间都已设置 “no file system caching”。
“`

## 找出所有裸设备类型且激活文件系统缓存的 DMS 表空间，并在线关闭文件系统缓存
db2 "select t.tbsp_name from sysibmadm.container_utilization c,sysibmadm.snaptbsp t where c.container_type like 'DISK_%' and t.tbsp_type='DMS' and c.tbsp_id=t.tbsp_id and t.FS_CACHING=0"

db2 "alter tablespace xxxxx no file system caching"   
##直接在线处理，对业务没有影响


最后复查有无误关闭非裸设备类型的表空间（包括SMS和DMS）
db2 "select t.tbsp_name,t.FS_CACHING from sysibmadm.container_utilization c,sysibmadm.snaptbsp t where c.container_type not like 'DISK_%' and c.tbsp_id=t.tbsp_id and t.FS_CACHING=1"

“`