Solved

db2 9.7 windows auto_db_backup with compress

Posted on 2012-04-02
19
1,557 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:Kdo
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
 
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:
Kdo 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:Kdo
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

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

Expert Comment

by:Kdo
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:Kdo
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:Kdo
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Creating and Managing Databases with phpMyAdmin in cPanel.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now