We'd like to use the auto_del_rec_obj and rec_his_retentn cfg
parameters to delete old backup images and archived log files. This is
working fine, expect in a HADR configuration. Backup images that are
taken on the standby server are stored in that side's history file
and, although in the same shared directory, are not purged by the
primary server. So far, pretty logical. Since the standby isn't
active, why should it bother pruning it's history file automatically?

My real question comes when trying to implement the retention
behaviour myself using the PRUNE command. Apparently, the PRUNE
command needs a database connection (which it obviously cannot get on
a HADR standby). I just can't figure out why it needs this. Why is
access to the history file not enough to perform a prune operation?
Can anyone clarify this one for me? Thanks in advance.

--
Frederik Engelen

Hi Frederik,

I'm curious about one thing, what are you doing to produce backup
images on the standby server? Are you disabling HADR and running a
DB2 BACKUP command there, or something else?

As far as the PRUNE command, historically the easy answer is that it
has just always been this way. The DB history and the PRUNE command
have existed since DB2 V2, long before there was ever such a thing as
HADR. A better answer, believe it or not, is that I just don't think
we have ever heard about this being a requirement before. Having said
that, these days the PRUNE command does have capabilities that require
a connection to an active DB, such as automatic deletion of archived
logs and backup images.

Hope that helps.
Kelly Rodger

On Feb 18, 3:18*pm, kdr <krod... (AT) ca (DOT) ibm.com> wrote:
Hello Kelly,

Thanks for your response. When I mentioned 'backups taken on the
standby', I did meant that the database had primary role on that
server at the time the backup was taken. 'Former primary' might have
been a better chouice of words. We have a script running on both sides
to take the nightly backup: whoever is primary, gets to take it.

Both sides use the same NFS share to store archived log files and
backup images. Of course, it's somewhat far fetched to expect the
primary to clean up the other one's recovery objects, since they are
in a different history file. So I tried to resolve this by performing
the PRUNE operation manually on the standby and that's where my real
question starts.

Interestingly, your answer explicitely states what I don't understand,
namely that the PRUNE command needs a database connection to perform
his job. Why isn't access to the history file enough? Even for these
new features, I don't immediately see the reason.

Kind regards,

Frederik

Thanks Frederik, I understand now. In your environment do you tend to
have a significant amount of time where the 2nd site acts as the HADR
primary? I actually believe that is somewhat rare, although certainly
reasonable, where most environments I've seen would identify a primary
site where all backups are produced, and the 2nd host would be used
for short periods of time only when the primary host is unavailable.

The requirement for a connection is primarily historic, going back a
decade or two when there was no real need for PRUNE to be permitted
without an active DB. LIST HISTORY was unusual back then, as one of
the few DB commands that has to be available when the DB doesn't even
exist. Is it possible for that to change, possibly in a future
release. It's just a simple matter of programming. ;-)

Having said that, since the DB histories are not completely identical
between the HADR hosts, even if the command was permitted there would
still be a concern about the pruning and object deletion logic not
deleting what you might expect. The basic rule is that an object can
be deleted if it exceeds both NUM_DB_BACKUPS and REC_HIS_RETENTN,
which could result in retaining more than expected with backups and
log archives only being created from one host at a time. Perhaps
that's exactly what you're observing.

For now, if you do run the BACKUP periodically on both hosts it will
automatically PRUNE according to those parameters. You can also run
the PRUNE command yourself after a takeover if you feel that you need
to clean up.

Hope that helps.
Kelly Rodger

On 2/18/10 1:35 PM, Kelly Rodger wrote:


Kelly,

Does PRUNE actually remove archived logs (i.e. if
logarchmeth1=DISK:/a/path)? I was under the impression that it will
only remove logs from the active log path (i.e. if logarchmeth1=retain)
- the 9.7 documentation says,

LOGFILE PRIOR TO log-file-name
Specifies a string for a log file name, for example
S0000100.LOG. All log files prior to (but not including)
the specified log file will be deleted. The logretain
database configuration parameter must be set to RECOVERY
or CAPTURE.



Thanks,

Hi Ian,

I actually think of the two flavours of the PRUNE command as distinct,
independent commands. You're right about the PRUNE LOGFILE command.
It is for pruning logs from the active log path, not the log
archives. The earlier discussion in this thread was about the PRUNE
HISTORY command, or at least that's what I was talking about. ;-)

Kelly Rodger

On 2/22/10 7:50 AM, Kelly Rodger wrote:
OK -- you were talking about log files in the earlier discussion, but
I now take it to mean that the history file entries referring to those
log files are getting pruned, not the actual log files themselves.

This is unfortunate, though -- it would be a really nice enhancement if
PRUNE LOGFILE could handle cleaning up log files that are archived by
IBM-supplied logarchmeth[1|2] methods (DISK, TSM).


Thanks for the clarification!


Ian

Hi Ian.

You might want to take a look at the "AND DELETE" option of the "PRUNE
HISTORY" command:

From http://publib.boulder.ibm.com/infoce...r0001992.html:

<quote>
AND DELETE
Specifies that the associated log archives will be physically deleted
(based on the location information) when the history file entry is
removed. This option is especially useful for ensuring that archive
storage space is recovered when log archives are no longer needed. If
you are archiving logs via a user exit program, the logs cannot be
deleted using this option.
If you set the auto_del_rec_obj database configuration parameter to
ON, calling PRUNE HISTORY with the AND DELETE parameter will also
physically delete backup images and load copy images if their history
file entry is pruned.
</quote>

I believe that PRUNE LOGFILE was intended for use with "LOGRETAIN" and
thus I can see why it would not do the deletion of archives, because
in that configuration DB2 would not be aware of where, if anywhere,
the user may have archived the log files.

I would be interested to know if you are using PRUNE LOGFILE and if so
to what end.

Regards,
- Steve P.
---
Steve Pearson
IBM DB2 UDB Development
Portland, OR, USA

On Feb 23, 1:21*pm, "Steve Pearson (news only)" <stevep...@my-
deja.com> wrote:
What is needed is a merged history files between the HADR pair of
databases. This would allow auto_del_rec_obj to clean up backups and
archive log files regardless of which instance was managing the
database at the time. In situations where the backups and logs are
accessible from both servers, it makes sense that the complete
database history is available from both nodes. If successful backup
events and archive log events were logged, the information could be
shipped to the HADR partner, then the history file for the logical
database would be complete across the pair.

auto_del_rec_obj is only looked at when a backup is taken. If a HADR
take-over event occurs, then the archive logs created by the alternate
server may exist indefinitely if not enough backups are taken on the
alternate server to qualify the num_backups and rec_hist_retn
thresholds.

Norm