Backup and unused pages?

[Troels Arvin]

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?

[Jeremy Rickard]

Post by Troels Arvin
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