PRUNE HISTORY question

[Phil Sherman]

UDB LUW (Windows) 8.1 FP9a

I issued a PRUNE HISTORY 20050819 AND DELETE and was surprised to see
that the archived log files were not physically deleted. The history
record of the 20050818 backup was correctly deleted from the history
file. I then issued a PRUNE LOGFILE PRIOR TO Snnnnnnn.LOG command which
did delete the logs. Both commands completed with no errors. LOGRETAIN
parameter is set to RECOVERY.

What does the 'AND DELETE' option do?
Phil Sherman

[Pierre Saint-Jacques]

Is AND DELETE really right?
A look up in the Command Ref. gives:
Command Syntax

-PRUNE----+-HISTORY--timestamp----+--------------------+-+---><

| '-WITH FORCE OPTION--' |
'-LOGFILE PRIOR TO--log-file-name--------------'
So, I'm wondering??Than ks, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Phil Sherman" <ps******@ameri tech.net> a écrit dans le message de news:
k4************* **@newssvr22.ne ws.prodigy.net. .. UDB LUW (Windows) 8.1 FP9a

I issued a PRUNE HISTORY 20050819 AND DELETE and was surprised to see that
the archived log files were not physically deleted. The history record of
the 20050818 backup was correctly deleted from the history file. I then
issued a PRUNE LOGFILE PRIOR TO Snnnnnnn.LOG command which did delete the
logs. Both commands completed with no errors. LOGRETAIN parameter is set
to RECOVERY.

What does the 'AND DELETE' option do?
Phil Sherman

[Phil Sherman]

My interpretation of the manual is that it's supposed to delete the log
files. This should avoid having to look up the first active log file in
the oldest backup in the history file to delete obselete logs.
From my manual:

IBM® DB2® Universal Database Command Reference Version 8.2 SC09-4828-01

Command syntax:
PRUNE HISTORY timestamp [WITH FORCE OPTION] [AND DELETE]
PRUNE LOGFILE PRIOR TO log-file-name

Command parameters:

HISTORY timestamp
Identifies a range of entries in the recovery history file that will be
deleted. A complete time stamp (in the form yyyymmddhhmmss) , or an
initial prefix (minimum yyyy) can be specified. All entries with time
stamps equal to or less than the time stamp provided are deleted from
the recovery history file.

WITH FORCE OPTION
Specifies that the entries will be pruned according to the time stamp
specified, even if some entries from the most recent restore set are
deleted from the file. A restore set is the most recent full database
backup including any restores of that backup image. If this parameter is
not specified, all entries from the backup image forward will be
maintained in the history.

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. Note:
If you are archiving logs via a user exit program, the logs cannot be
deleted using this option.

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.

Phil Sherman
Pierre Saint-Jacques wrote:

Is AND DELETE really right?
A look up in the Command Ref. gives:
Command Syntax

-PRUNE----+-HISTORY--timestamp----+--------------------+-+---><

| '-WITH FORCE OPTION--' |
'-LOGFILE PRIOR TO--log-file-name--------------'
So, I'm wondering??Than ks, Pierre.

[Pierre Saint-Jacques]

I stand corrected or should I say updated as I used the wrong version of
manuals.
I think, maybe, perhaps, ... the DELETE would then delete the log files in
the archive IF you use LOGARCHMETH1 or 2 in the parms.
To my way of thinking, this would be the only way that DB2 would know where
to find the log files to delete.

The parm. works with other parm (NUM_DB_BACKUPS ) = 3.
The timestamp will identify the oldest back entries to delete and their
associated logs if it is older (3) than the timestamp entry.

That's MHO but I can't verify from here, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Phil Sherman" <ps******@ameri tech.net> a écrit dans le message de news:
vQ************* **@newssvr22.ne ws.prodigy.net. ..

My interpretation of the manual is that it's supposed to delete the log
files. This should avoid having to look up the first active log file in
the oldest backup in the history file to delete obselete logs.
From my manual:

IBM® DB2® Universal Database Command Reference Version 8.2 SC09-4828-01

Command syntax:
PRUNE HISTORY timestamp [WITH FORCE OPTION] [AND DELETE]
PRUNE LOGFILE PRIOR TO log-file-name

Command parameters:

HISTORY timestamp
Identifies a range of entries in the recovery history file that will be
deleted. A complete time stamp (in the form yyyymmddhhmmss) , or an initial
prefix (minimum yyyy) can be specified. All entries with time stamps equal
to or less than the time stamp provided are deleted from the recovery
history file.

WITH FORCE OPTION
Specifies that the entries will be pruned according to the time stamp
specified, even if some entries from the most recent restore set are
deleted from the file. A restore set is the most recent full database
backup including any restores of that backup image. If this parameter is
not specified, all entries from the backup image forward will be
maintained in the history.

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. Note:
If you are archiving logs via a user exit program, the logs cannot be
deleted using this option.

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.

Phil Sherman
Pierre Saint-Jacques wrote:

Is AND DELETE really right?
A look up in the Command Ref. gives:
Command Syntax

-PRUNE----+-HISTORY--timestamp----+--------------------+-+---><

| '-WITH FORCE OPTION--' |
'-LOGFILE PRIOR TO--log-file-name--------------'
So, I'm wondering??Than ks, Pierre.