Solved

db2 9.7 windows auto_db_backup with compress

Posted on 2012-04-02
19
1,589 Views
Last Modified: 2012-11-19
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
Comment
Question by:snowdog_2112
19 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 37797741
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
 
LVL 4

Expert Comment

by:sathyaram_s
ID: 37797766
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
 

Author Comment

by:snowdog_2112
ID: 37798224
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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 37798273
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
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 37798315
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
 

Author Comment

by:snowdog_2112
ID: 37828766
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 37828835
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
 

Author Comment

by:snowdog_2112
ID: 37889793
Haven't had a moment to try this...sorry for the delay...
0
 

Author Comment

by:snowdog_2112
ID: 37994647
Still havent....
Not enough hours in the freaking day...
0
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38358936
I sure would like to know if the OP ever got this working....
0
 

Author Comment

by:snowdog_2112
ID: 38574294
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38574371
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
 

Author Comment

by:snowdog_2112
ID: 38574405
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 38574557
Hmm...   Odd.  


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



Kent
0
 

Author Comment

by:snowdog_2112
ID: 38574588
Thanks - looking forward to your results.
0
 

Author Comment

by:snowdog_2112
ID: 38592104
Kdo - did you have a chance to look at this?  Thanks again for all the time, I;d like to award the points.
0
 

Author Comment

by:snowdog_2112
ID: 38614410
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question