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

It depends entirely on your data. I have seen 25-50% reduction.

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

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

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-'

Thank you Marcus.

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

All you need is the COMPRESS keyword and DB2 will use the supplied compression code.

How does one restore from compressed backup?

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

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.

Thank you Marcus
very nice indeed!!!

does anyone know howto do a compressed backup with an external library?

jhp
black_