Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

    Unanswered: filesystem caching and temporary tablespaces

    What is your experience with disabling filesystem caching (at the filesystem / tablespace level) for system temporary tablespaces (SMS / DMS)?

    Can disabling it degrade performance of utilities such as reorg?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I normally disable file system caching at the tablespace level for all tablespaces except those which contain LOB objects (which do not use bufferpools prior to 9.7). In version 9.7, and inline size of a LOB can be specified so that only LOB's that exceed that size are stored in the LONG tablespace and do not use bufferpools. Obviously, I put the LOB columns in a separate tablespace when creating the table.

    I don't even know how to disable file system caching at the file system level, and I assume that is a OS function.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    From v9.5 manual:

    ---------------------------
    Prior to Version 9.5, the keyword FILE SYSTEM CACHING was implied if neither NO FILE SYSTEM CACHING nor FILE SYSTEM CACHING was specified. With Version 9.5, if neither keyword is specified, the default, NO FILE SYSTEM CACHING, is used. This change affects only newly created table spaces. Existing table spaces created prior to Version 9.5 are not affected. This change applies to AIX, Linux®, Solaris, and Windows with the following exceptions, where the default behavior remains to be FILE SYSTEM CACHING:
    AIX JFS
    Solaris non-VxFS
    Linux for System z®
    All SMS temporary table space files
    Long Field (LF) and Large object (LOB) data files in SMS permanent table space files
    ---------------------------


    I wonder why filesystem caching is enabled for SMS temporary tablespaces and disabled for DMS temporary tablespaces. Customer reported that reorg times increased after enabling CIO (I believe it was done at the filesystem level using the mount command). I don't have any other details at this time.

    Do you think tempspace's bufferpool needs to be increased after disabling filesystem caching?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by db2girl View Post
    I wonder why filesystem caching is enabled for SMS temporary tablespaces and disabled for DMS temporary tablespaces. Customer reported that reorg times increased after enabling CIO (I believe it was done at the filesystem level using the mount command). I don't have any other details at this time.

    Do you think tempspace's bufferpool needs to be increased after disabling filesystem caching?
    Not sure the answer to first question. It may be a compatibility issue with prior releases. SMS was the default temp tablespace type previously, but I think it is now DMS (which I don't understand either).

    If the bufferpool for the tempspace is too small, that could definitely account for the long reorg times when reorg is not done inside the regular tablespace for the table. It could affect other things besides reorgs if there were temp tables created that greatly exceeded the size of the bufferpool for the temp tablespace.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •