1. Forum
  2. Usenet
  3. COMP.DATABASES.IBM-DB2

  • Overhead, transferrate, dropped table recovery

    From Troels Arvin@21:1/5 to All on Wed Jan 18 19:43:37 2017
    Hello,

    When looking at db2look output for an old tablespace, I saw:

    CREATE LARGE TABLESPACE ...
    ...
    EXTENTSIZE 64
    PREFETCHSIZE 96
    ...
    OVERHEAD 7.500000
    TRANSFERRATE 0.060000
    DROPPED TABLE RECOVERY OFF

    This got me thinking: When using a SAN storage system with automatic
    tiering between SSD and various kinds of rotating disk, what should one
    put for OVERHEAD and TRANSFERRATE? Does DB2 even care about those values nowadays?

    I suppose EXTENTSIZE and PREFETCHSIZE could matter, but I would hope that
    DB2 is somewhat smart about prefetching etc. Or?

    I've turned dropped table recovery off because I believe I've once seen
    it contribute to a state where a tablespace had trouble releasing free
    space after a tablespace-full situation. But my memory could be wrong. At
    any rate: I'm rather scared of restoring tables into a live tablespace
    any way, so it's unlikely that I'll make use of DROPPED TABLE RECOVERY.
    Does someone have experiences/opnions to share on this?

    --
    Regards,
    Troels Arvin

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Luiz da Silva@21:1/5 to Troels Arvin on Thu Jan 19 05:56:00 2017
    On Wednesday, January 18, 2017 at 8:43:54 PM UTC+1, Troels Arvin wrote:
    Hello,

    When looking at db2look output for an old tablespace, I saw:

    CREATE LARGE TABLESPACE ...
    ...
    EXTENTSIZE 64
    PREFETCHSIZE 96
    ...
    OVERHEAD 7.500000
    TRANSFERRATE 0.060000
    DROPPED TABLE RECOVERY OFF

    This got me thinking: When using a SAN storage system with automatic
    tiering between SSD and various kinds of rotating disk, what should one
    put for OVERHEAD and TRANSFERRATE? Does DB2 even care about those values nowadays?

    I suppose EXTENTSIZE and PREFETCHSIZE could matter, but I would hope that
    DB2 is somewhat smart about prefetching etc. Or?

    I've turned dropped table recovery off because I believe I've once seen
    it contribute to a state where a tablespace had trouble releasing free
    space after a tablespace-full situation. But my memory could be wrong. At
    any rate: I'm rather scared of restoring tables into a live tablespace
    any way, so it's unlikely that I'll make use of DROPPED TABLE RECOVERY.
    Does someone have experiences/opnions to share on this?

    --
    Regards,
    Troels Arvin

    Steve Rees just published an excellent article exactly about that: http://www.idug.org/p/bl/et/blogaid=589

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)