Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2002
    Posts
    456

    Arrow Unanswered: Reorg and Backup

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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 ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Oct 2003
    Posts
    87

    Re: Reorg and Backup

    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.

    Originally posted by dollar489
    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
    Oracle - DB2 - MS Access -

  5. #5
    Join Date
    Nov 2002
    Location
    Delaware
    Posts
    186
    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.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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



    Originally posted by Marcus_A
    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).
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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