Speeding up DB2 Backups

You may also like...

5 Responses

  1. vineet says:

    Please elaborate “If you have a database that is currently focused on a single tablespace, you can move tables online from tablespace using ADMIN_MOVE_TABLE.Due to RI restrictions, you’ll want to be on 10.1, fixpack 2 or higher to make this reasonable.” in your blog

    • Ember Crooks says:

      Prior to 10.1, fixpack 2, ADMIN_MOVE_TABLE cannot handle moving tables with foreign keys defined. With fixpack 2 or higher of 10.1 or any fixpack of 10.5, ADMIN_MOVE_TABLE can handle tables with foreign keys. Before those, you have to have some offline time for tables with foreign keys – drop foreign keys and then use ADMIN_MOVE_TABLE and recreate the foreign keys or something. See my blog post on Changing the Data Type of a DB2 Column (Method 2) for an example of how to use ADMIN_MOVE_TABLE on a table with RI prior to 10.1 fixpack2.

  2. Abdul Qader says:

    Hi Ember,

    I was analyzing this case of backups where two different backups are having huge difference in duration of backups

    Backup 1:

    Database Version : DB2 V10.1 FP 5
    Operating System : Aix
    Hostname : Host1
    Database Name : Sample (34 GB)
    Backup Type :Online Netbackup
    Backup Compression: No
    Parallelism :8 (value obtained from db2diag.log)
    Buffer size and No of buffers :4097 / 8 = 128 MB (value obtained from db2diag.log)
    Util_Heap_Size:155 MB of 320 MB of heap space assigned. (value obtained from db2diag.log)
    Backup duration: 10 mins

    Backup 2:

    Database Version : DB2 V10.1 FP 5
    Operating System : Aix
    Hostname : Host1
    Database Name : Sample (34 GB)
    Backup Type :Online Netbackup
    Backup Compression: YES
    Parallelism :5 (value obtained from db2diag.log)
    Buffer size and No of buffers :2561 / 10 = 100 MB (value obtained from db2diag.log)
    Util_Heap_Size:155 MB of 320 MB of heap space assigned. (value obtained from db2diag.log)
    Backup duration: 1 hour 15 mins

    So if we see the differences above the major difference are parallelism, buffer size and compression. Is there any way to speed up backup compression. ?

    Thanks
    Abdul Qader

    • Ember Crooks says:

      You have a factor of network speed and activity on the netbackup target as well. The number of sessions used would make a difference as well. I do see a slowdown with compression, but the time for using the compress option on a backup command is less than the time it takes me to take the backup and then compress it. You may also want to be aware that when backing up to a vendor device, using compression on the backup command may negate any deduplication on the target device, so with vendor devices like netbackup, it may be better to choose not to compress.

  1. September 22, 2015

    […] Speeding up DB2 Backups […]

Leave a Reply

Your email address will not be published. Required fields are marked *