SMS and DMS device considerations

There are a few options to consider when choosing to use file system files versus devices for table space containers: the buffering of data and whether to use LOB or LONG data.

• Buffering of data

  Table data read from disk is usually available in the database buffer pool. In some cases, a data page might be freed from the buffer pool before the application has actually used the page, particularly if the buffer pool space is required for other data pages. For table spaces that use system managed space (SMS) or database managed space (DMS) file containers, file system caching can eliminate I/O that would otherwise have been required.

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

  If system-level monitoring tools show that I/O is higher for a DMS table space using device containers compared to the equivalent SMS table space, this difference might be because of double buffering.

  Important: User table spaces that use System Managed Space (SMS) are deprecated and might be removed in a future release. Use Database Managed Spaces (DMS) or Automatic Storage table spaces (AMS) instead.
• Using LOB or LONG data

  When an application retrieves either LOB or LONG data, the database manager does not cache the data in its buffers. Each 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 DMS file containers, file system caching might provide buffering and, as a result, better performance.

  Because system catalogs contain some LOB columns, you should keep them in DMS-file table spaces.