Results 1 to 3 of 3

Thread: FSC and VARCHAR

  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: FSC and VARCHAR

    DB2 v9.5 on AIX 6.1
    prod. database, we are using only SMS tablespaces and FSC is enabled by default for them. Looking for ways to improve performance so I have some questions:

    If I
    - turn off FSC for existing TSs;
    - move tables with LOBs to TS with FSC;

    would I get noticable performance improvement?

    As LONG VARCHAR is depricated in 9.5 and VARCHAR should be used, does it mean that db2 caching/BPs will not work for VARCHAR either?

    Thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Turning off FSC (File System Caching) will free up memory to the OS, plus stop the "double buffering" that is happening. It should improve performance since the OS CPU cycles that were being used for the OS to do caching are no longer need, and it will free up RAM that DB2 will then be able to use. Moving the LOB data to a separate TS that still utilizes FSC will only be helpful if you access the LOB data very frequently--where it will be in the OS cache.

    I do not speak for IBM, but I am fairly certain that since LONG VARCHAR is supposed to be replaced by VARCHAR, that DB2 will continue to put the VARCHAR data into the bufferpools.

    Andy

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    You must aim for:
    - regular db column definitions are cached by the bufferpool (=good)
    - LOB columns are not cached by the bufferpool so activate a tablespace with FSC for those (2nd best)
    DMS tablespaces are very suitable because you can divide the row into regular and LOB colomns by specifying more than 1 tablespace for 1 table.
    In your case, using SMS only (why?) you can split the table and make 1 dedicated LOB table (FSC entabeled tablespace) with a FK-relationship to the original table 9placed in the NO FSC tabspace).
    Create a userview in which you join the 2 and no application programmer will ever notice anything.

Posting Permissions

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