db2 9.7 windows auto_db_backup with compress

Is it possible to enable compression in the auto_db_backup wizard?

More specifically, I would like Automatic Maintenance to back up with compression and delete older backup files.

get database config

Shows auto_db_backup is enabled, but does not show any of the sub-settings, including destination, time window, and whether compression is enabled.

I see nothing in the Configure Autonatic Maintenance wizard to enable compression.  There is another backup task in the Task Center that also runs a backup with compression enabled.   I don't think the same user set up both tasks - seems to be redundant.

I am getting duplicate backups in the destination location, one is compressed, and one that is not.  Both jobs are configured to use the same destination.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Snowdog,

Are you using DB2 Express-C, or a fully licensed version of DB2?  As far as I know, backup compression is disabled in the Express version.

Hi,  AFAIK it is not possible to configure COMPRESSION.

For backup location, you have to configure the backup policy xml file


An example is provided in sqllib/samples/automaintcfg/


snowdog_2112Author Commented:
Not trying to be rude, but I already know "as far as I know" it's not possible.

This is licensed Workgroup edition.  As mentioned in OP, I am currently getting compression in the Task Center job, so I know compression *is* possible in my environment.

Ideally, I'd like to use only one tool: scheduled backups with compression *and* file automated cleanup (i.e., delete backups older than "n" days)...Like MS-SQL.

I'm looking for definitive confirmation.  I can't tell from the doc and the sample - the sample does not mention it, does that mean it's not possible?

Also checked here:
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

it can't be done
i don't remember where i read it, but i know for sure you can't have a compressed backup produced by the auto maintenance backup
Kent OlsenDBACommented:
Hi Snowdog,

I don't think that anyone way trying to be smart with you.  You're not a regular on the DB2 forum and we took an entry-level guess at your DB2 skill.

There is an xml file that DB2 uses to control the backup processes.  Sathyaram mentioned it earlier.  The file is in the SQLLIB path, on Windows it's probably:

  C:\Program Files\IBM\SQLLIB\samples\automaintcfg\DB2AutoBackupPolicySample.xml

Near line 60 in the unconfigured file are the online backup options.  You should be able to edit them directly into the XML.

Try changing the lines:

 <BackupOptions mode="Online" > 
   <DiskBackupTarget >


 <BackupOptions mode="Online" > COMPRESS
   <DiskBackupTarget >

Good Luck,

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
snowdog_2112Author Commented:
Kent - thanks for the update.  

I will definitely try that out!

I had poked around in the XML file in researching this issue, but didn't want to tweak it without knowing the ramifications.  I didn't see anything in the doc that mentioned the COMPRESS option.

As to my earlier comment - I apologize (I normally haunt the Windows, Cisco, VMware areas).  You can see in this thread alone there are 3 different answers - 1 says they don't think it can be done, 1 says it definitely can't, and 1 says it can.
Kent OlsenDBACommented:
I'm just more optimistic than most.  :)  I no longer have a licensed version of DB2 at my disposal to experiment with things like this, so until I can find some IBM documentation that tells me 'NO', I'm going to start with the belief that it can.  (IBM has a long history of including features that they don't explicitly talk about.)

I've never used XML to control a backup so I'm speaking from theory.  IF it can be done, it's done by passing the COMPRESS option to BACKUP command, and this is how you'd go about doing that.

snowdog_2112Author Commented:
Haven't had a moment to try this...sorry for the delay...
snowdog_2112Author Commented:
Still havent....
Not enough hours in the freaking day...
Kent OlsenDBACommented:
I sure would like to know if the OP ever got this working....
snowdog_2112Author Commented:
KDO - I'm finally getting to this!  (that wheel has finally gotten too squeaky)

One last question: once I edit that DB2AutoBackupPolicySample.xml file....how do I put the settings into the database?

I assume being a "sample" file, it's not the active settings on the database.  In fact, I have several databases, so I'd need to apply the settings to each db.

Thanks again!!!!!
Kent OlsenDBACommented:
Hi SnowDog.

Can you run the IBM Data Studio?  If so, run the Automatic Maintenance Wizard.  Configure anything.  :)  The look in the folder "Program Files\IBM" for the xml file.  (Search the folder for all *.xml files.)  It should be the most recently written XML file.

snowdog_2112Author Commented:
Great thought - I should have considered that (it's been a long day).

I have the Control Center app, and I modified the time window on the Auto Maintenance Wizard.  Nothing in \program files\ibm was modified.

Searching the entire C: drive, no XML files were modified at the time I finished the wizard.  The closest was an hour ago.

CustomCCTree.xml  - looks just to be UI stuff for the control center.
Kent OlsenDBACommented:
Hmm...   Odd.  

I don't have all of my tools here at home.  Let me check the office in the morning.

snowdog_2112Author Commented:
Thanks - looking forward to your results.
snowdog_2112Author Commented:
Kdo - did you have a chance to look at this?  Thanks again for all the time, I;d like to award the points.
snowdog_2112Author Commented:
Found it, but it does not seem to do the trick.

First, get the current settings.

from a db2 command line:

call sysproc.automaint_GET_policyfile( 'AUTO_BACKUP', 'AutoBackup.xml' )
   - this creates a file with the current settings.
   - edit this file as desired.  More info here: http://pic.dhe.ibm.com/infocenter/db2luw/v9r8/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0052291.html 

call sysproc.automaint_SET_policyfile( 'AUTO_BACKUP', 'AutoBackup.xml' )
   - this reads the edited XML file and applies it to the policy.

(I then verified the settings by running the GET to a different destination filename).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.