Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    30

    Angry Unanswered: db2 backup compression ???

    Hi All,

    I need to know how much db2 compresses the backup image prior to FP4 and after FP4 (FP FixPak)

    I am working on db2 V 8.1 with FP4 for AIX 5.2 32 bit instance.

    All comments are welcomed

    Cheers,
    Pushp

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It depends entirely on your data. I have seen 25-50% reduction.
    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
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Pushp,
    As Marcus stated it depends. I have a database where I wanted to see what the compression was like. I backed it up without compression, then immediately again with compression. The first backup image was 2.4 GB where the second was 549 MB. That is about a 75% reduction.

    Andy

  4. #4
    Join Date
    Feb 2002
    Posts
    96
    I am working on db2 V 8.1 with FP3 for AIX 5.1 32 bit instance.
    I would like to know the command for "compressed backup". Is there any special syntax for this? Because i could not find the syntax in command reference. Thank you.

    Regards
    Prakash

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Backup compression was added in FP4 on version 8.

    4>>-BACKUP--+-DATABASE-+--database-alias------------------------->
    4 '-DB-------'
    4
    4>--+-------------------------------------+---------------------->
    4 '-USER--username--+-----------------+-'
    4 '-USING--password-'
    4
    4>--+---------------------------------------+--+--------+-------->
    4 | .-,---------------. | '-ONLINE-'
    4 | V | |
    4 '-TABLESPACE--(----tablespace-name-+--)-'
    4
    4>--+------------------------+----------------------------------->
    4 '-INCREMENTAL--+-------+-'
    4 '-DELTA-'
    4
    4>--+-----------------------------------------------------------------------------------------+-->
    4 +-USE--+-TSM--+--+-------------------------------+--+------------------------------+------+
    4 | '-XBSA-' '-2OPTIONS--+-2"options-string"-+-' '-OPEN--num-sessions--SESSIONS-' |
    4 | '-2@--2file-name-----' |
    4 | .-,-------. |
    4 | V | |
    4 +-TO----+-dir-+-+-------------------------------------------------------------------------+
    4 | '-dev-' |
    4 '-LOAD--library-name--+-------------------------------+--+------------------------------+-'
    4 '-2OPTIONS--+-2"options-string"-+-' '-OPEN--num-sessions--SESSIONS-'
    4 '-2@--2file-name-----'
    4
    4>--+----------------------------+--+---------------------+------>
    4 '-WITH--num-buffers--BUFFERS-' '-BUFFER--buffer-size-'
    4
    4>--+----------------+------------------------------------------->
    4 '-PARALLELISM--n-'
    4
    4>--+------------------------------------------------------------------+-->
    4 '-COMPRESS--+-----------------------------+--+-------------------+-'
    4 '-COMPRLIB--name--+---------+-' '-COMPROPTS--string-'
    4 '-EXCLUDE-'
    4
    4>--+------------------------------------+----------------------->
    4 '-2UTIL_IMPACT_PRIORITY--+----------+-'
    4 '-2priority-'
    4
    4>--+-------------------+---------------------------------------><
    4 '-WITHOUT PROMPTING-'
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Feb 2002
    Posts
    96
    Thank you Marcus.

  7. #7
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Smile

    hmmm... I'm curious can you just add
    the compress switch without specifying
    a compress library??, and if you have
    to add a library what are the size
    considerations? - like the entire database
    or is the compression dynamic?

    I'm very interested this is an extremely
    valuable option.

    HTH
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    All you need is the COMPRESS keyword and DB2 will use the supplied compression code.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Mar 2003
    Posts
    343
    How does one restore from compressed backup?

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The new Restore command:

    >>-RESTORE--+-DATABASE-+--source-database-alias----------------->
    4 '-DB-------'
    4
    4>--+-| restore-options |-+-------------------------------------><
    4 +-CONTINUE------------+
    4 '-ABORT---------------'
    4
    4restore-options:
    4
    4|--+-------------------------------------+---------------------->
    4 '-USER--username--+-----------------+-'
    4 '-USING--password-'
    4
    4>--+---------------------------------------------------+-------->
    4 +-TABLESPACE--+--------+----------------------------+
    4 | '-ONLINE-' |
    4 | .-,---------------. |
    4 | V | |
    4 +-TABLESPACE--(----tablespace-name-+--)--+--------+-+
    4 | '-ONLINE-' |
    4 +-HISTORY FILE--+--------+--------------------------+
    4 | '-ONLINE-' |
    4 '-COMPRESSION LIBRARY--+--------+-------------------'
    4 '-ONLINE-'
    4
    4>--+----------------------------+------------------------------->
    4 '-INCREMENTAL--+-----------+-'
    4 +-AUTO------+
    4 +-AUTOMATIC-+
    4 '-ABORT-----'
    4
    4>--+-------------------------------------------------------------------------------------------+-->
    4 +-USE--+-TSM--+--+-------------------------------+--+------------------------------+--------+
    4 | '-XBSA-' '-2OPTIONS--+-2"options-string"-+-' '-OPEN--num-sessions--SESSIONS-' |
    4 | '-2@--2file-name-----' |
    4 | .-,-------------. |
    4 | V | |
    4 +-FROM----+-directory-+-+-------------------------------------------------------------------+
    4 | '-device----' |
    4 '-LOAD--shared-library--+-------------------------------+--+------------------------------+-'
    4 '-2OPTIONS--+-2"options-string"-+-' '-OPEN--num-sessions--SESSIONS-'
    4 '-2@--2file-name-----'
    4
    4>--+---------------------+--+----------------------+------------>
    4 '-TAKEN AT--date-time-' '-TO--target-directory-'
    4
    4>--+-----------------------------+--+-----------------------+--->
    4 '-INTO--target-database-alias-' '-NEWLOGPATH--directory-'
    4
    4>--+----------------------------+--+---------------------+------>
    4 '-WITH--num-buffers--BUFFERS-' '-BUFFER--buffer-size-'
    4
    4>--+--------------------+--+----------------------+------------->
    4 '-DLREPORT--filename-' '-REPLACE HISTORY FILE-'
    4
    4>--+------------------+--+----------+--+----------------+------->
    4 '-REPLACE EXISTING-' '-REDIRECT-' '-PARALLELISM--n-'
    4
    4>--+----------------+--+-------------------+-------------------->
    4 '-COMPRLIB--name-' '-COMPROPTS--string-'
    4
    4>--+-------------------------+--+------------------+------------>
    4 '-WITHOUT ROLLING FORWARD-' '-WITHOUT DATALINK-'
    4
    4>--+-------------------+----------------------------------------|
    4 '-WITHOUT PROMPTING-'
    4


    Andy

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you use the IBM supplied compression routine (you don't specify a compression library), there are no changes needed for the restore. DB2 automatically recognizes whether it is compressed. Obviously, you can only restore to a V8 FP4 or higher instance.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Talking Nice

    Thank you Marcus
    very nice indeed!!!
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  13. #13
    Join Date
    Nov 2003
    Posts
    24
    does anyone know howto do a compressed backup with an external library?

    jhp
    black_

Posting Permissions

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