• Backup and unused pages?

    From Troels Arvin@21:1/5 to All on Sun Dec 10 10:37:54 2017
    Hello,

    Let's say that I have a tablespace T. I now clean up by deleting a number
    of big tables in the tablespace, e.g. bringing it down from 4 TiB to 2 TiB
    of used pages.

    I'm hoping that the deletion of the tables will also translate to a
    quicker online backup of T. But for that to happen: Is it required to run
    an "ALTER TABLESPACE t REDUCE MAX" before I will see quicker backups? -
    Or is the backup procedure clever enough to skip unused pages? Is there a difference between full, incremental or delta backups in this regard?

    --
    Regards,
    Troels Arvin

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jeremy Rickard@21:1/5 to Troels Arvin on Sun Dec 10 19:52:53 2017
    On Sunday, 10 December 2017 23:37:58 UTC+13, Troels Arvin wrote:
    Hello,

    Let's say that I have a tablespace T. I now clean up by deleting a number
    of big tables in the tablespace, e.g. bringing it down from 4 TiB to 2 TiB of used pages.

    I'm hoping that the deletion of the tables will also translate to a
    quicker online backup of T. But for that to happen: Is it required to run
    an "ALTER TABLESPACE t REDUCE MAX" before I will see quicker backups? -
    Or is the backup procedure clever enough to skip unused pages? Is there a difference between full, incremental or delta backups in this regard?

    --
    Regards,
    Troels Arvin

    Hi Troels,

    The backup includes only used pages, so backup size should reduce. However I doubt whether the backup is clever enough to use space map pages to skip reading big blocks of unused pages. Suspect the answer to that is no. It would add risk and complexity,
    also unnecessary CPU cost if the tablespace is well maintained. Might also be harder to parallelise?

    Your backup might run quicker nonetheless if writes are the bottleneck, or if your tablespace sizes were skewed.

    You could ask IBM support for a definitive answer on the internals.

    Jeremy

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