Administration Guide

Performance Considerations for DMS Devices

If you are using Database Managed Storage (DMS) device containers for your table spaces, you need to understand the following so you can effectively administer your environment:

• File system caching

  File system caching is performed as follows:

  • For DMS file containers (and all SMS containers), the operating system may cache pages in the file system cache
  • For DMS device container table spaces, the operating system does not cache pages in the file system cache.

    Note:

    When working on Windows NT, the registry variable DB2NTNOCACHE specifies whether or not DB2 will open database files with a NOCACHE option. If DB2NTNOCACHE=ON, file system caching is eliminated. If DB2NTNOCACHE=OFF, the operating system caches DB2 files. This applies to all data except for files that contain LONG FIELDS or LOBS. Eliminating system caching allows more memory to be available to the database so that the buffer pool or sortheap can be increased.

• Buffering of data

  Table data read from disk is normally available in the database's buffer pool (see Managing the Database Buffer Pool). In some cases, a data page can be freed from the buffer pool before the application has actually used that page. (This can happen if the buffer pool space is required for other data pages.) For table spaces using system managed storage (SMS) or database managed storage (DMS) file containers, see the description of file system caching above. This can eliminate I/O that would otherwise have been required.

  Table spaces using database managed storage (DMS) device containers do not use the file system or its cache. As a result, you may wish to increase the size of the database buffer pool and reduce the size of the file system cache to offset the fact that double buffering is not being done with DMS table spaces that use device containers.

  If you notice, through the use of system-level monitoring tools, that I/O is higher for a DMS table space using device containers compared to the equivalent SMS table space, this difference could be due to the double buffering discussed above.

• Using LOB or LONG data

  When an application retrieves either LOB or LONG data, the database manager does not use its buffers to cache the data. Every time an application needs one of these pages, the database manager must retrieve it from disk.

  However, if LOB or LONG data is stored in SMS or DMS file containers, file system caching may provide buffering and, as a result, better performance.

  Because system catalogs contain some LOB columns, it is recommended that you keep them in SMS (or alternatively in DMS-file) table spaces.