Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1743
  • Last Modified:

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.
0
snowdog_2112
Asked:
snowdog_2112
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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.


Kent
0
 
sathyaram_sCommented:
Hi,  AFAIK it is not possible to configure COMPRESSION.

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

See
http://www.ibm.com/developerworks/data/library/techarticle/dm-0801ganesan/

An example is provided in sqllib/samples/automaintcfg/

HTH


Sathyaram
0
 
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:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.ha.doc%2Fdoc%2Ft0021095.html
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
momi_sabagCommented:
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
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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" > 
  <BackupTarget>
   <DiskBackupTarget >
     <PathName/>
   </DiskBackupTarget>
  </BackupTarget>
 </BackupOptions>

to

 <BackupOptions mode="Online" > COMPRESS
  <BackupTarget>
   <DiskBackupTarget >
     <PathName/>
   </DiskBackupTarget>
  </BackupTarget>
 </BackupOptions>



Good Luck,
Kent
0
 
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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.


Kent
0
 
snowdog_2112Author Commented:
Haven't had a moment to try this...sorry for the delay...
0
 
snowdog_2112Author Commented:
Still havent....
Not enough hours in the freaking day...
0
 
Kent OlsenData Warehouse Architect / DBACommented:
I sure would like to know if the OP ever got this working....
0
 
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!!!!!
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.


Kent
0
 
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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hmm...   Odd.  


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



Kent
0
 
snowdog_2112Author Commented:
Thanks - looking forward to your results.
0
 
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.
0
 
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).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now