RDBMS : DB2 UDB v7.2 fp 9
O/S : AIX 5.1

Here are the sequence of events; I took a backup of our test database, loaded more data and did reorg, runstat and rebind and then took the backup again. I was surprised to see that the second backup image was smaller than the first one even with more data. What I concluded and you guys can agree reorg did the trick.

So, my question is that, is it safe to assume that we should do reorg before taking a backup!!! This will save space and time it takes to do the backup.


Your comments are appreciated.

dollar

It might save space, but could also take more space if the percent free is high enough. Percent free re-creates empty space on each page during a reorg.

Starting in Version 8 Fixpak 4, compression is available for backups.

Starting in Version 8 Fixpak 4, compression is available for backups.


For info, A couple of tests on Linux indicated that the compressed backup is about 1/3rd of the uncompressed one ...

Reorgs should probably be viewed as separate from backups. Reorgs are most effect based on an event (recent loads or mass deletes). There are several examples in the manuals of SQL that can be used to determine when a reorg would be applicable. Also, some tablespaces may be pre-formatted with extra space in anticipation of an upcoming influx of data.

I would also make sure I have a good backup before I do a re-org, If the re-org fails for any reason, it can cause data corruption. This is rare, but before fp8, there's a bug when a reorg didn't finish well, it wouldn't write out the data to the table, and our db crashed and we couldn't get it back up.


The fix was in FP8.

I just did a backup with the compress option and it was 42% of the size of an uncompressed backup. I assume that results will vary somewhat depending on the data.

I did not find the compress option available when doing a backup from the Control Center (fixpak 4).

I am yet to test with LOBs .... The compression achieved with LOBs may be different that the rest of the 'traditional' data ...

Did you have LOBs in your database ?

Sathyaram

No LOB’s in the database I backed up. I had mostly varchar columns, which may not compress as much as fixed length character data or numeric data with lots of leading zeros.