File System Caching Info

Other than using the OS mount option, file system caching can be disabled/enabled during tablespace creation time. As important as this option is, it is not being shown in the standard 'list tablespaces' output.

To get this info, you will have to resort to this:

GET SNAPSHOT FOR TABLESPACES ON <db name>

A sample output is shown below. Note the line with 'File system caching' ( highlighted in italic bold font). For this example, it is 'No' for USERSPACE1:


Tablespace name                            = USERSPACE1
  Tablespace ID                            = 2
  Tablespace Type                          = Database managed space
  Tablespace Content Type                  = All permanent data. Large table space.
  Tablespace Page size (bytes)             = 4096
  Tablespace Extent size (pages)           = 32
  Automatic Prefetch size enabled          = Yes
  Buffer pool ID currently in use          = 1
  Buffer pool ID next startup              = 1
  Using automatic storage                  = Yes
  Auto-resize enabled                      = Yes
  File system caching                      = No
  Tablespace State                         = 0x'00000000'
   Detailed explanation:
     Normal
  Tablespace Prefetch size (pages)         = 32


For tablespaces created in v9.7 and later, NO FILE SYSTEM CACHING is the default. Before deciding whether caching is good for the system or not, make sure you understand the characteristics of the DB ( the deciding factor would involve the presence of inlined/non-inlined LOB).