DB2 LUW : DB2 V10.5 FP8 Prune history and delete may not delete them as expected

Body

Question:

Why does "prune history and delete" retain history entries and LOG files on V10.5 FP8?

Cause:

It is an known expected behavior.

Answer:
Here is a sample test scenario.
----------
#!/bin/ksh

db2 drop db db1
export ARCHMETH=/tmp/archmeth
rm -rf $ARCHMETH
mkdir $ARCHMETH

db2 create db db1
db2 connect to db1
db2 "create table t1(c1 int)"
db2 update db cfg for db1 using LOGFILSIZ 10 LOGPRIMARY 6 LOGSECOND 2
db2 update db cfg for db1 using LOGARCHMETH1 DISK:$ARCHMETH
db2 backup db db1 to /dev/null
db2 connect to db1
db2 get db cfg for db1 | egrep -i 'auto_del_rec_obj|rec_his_retentn|num_db_backups'
       > db2_get_db_cfg
db2 prune history `date +"%Y%m%d%H%M%S"` and delete

# main

CNT=1
MAX=12
while [ "$CNT" -le "$MAX" ]
do
  db2 connect to db1
  db2 "insert into t1 select rand()*1000 from syscat.columns fetch first 2000 rows only"
  db2 backup database db1 online to /dev/null
  CNT=`expr $CNT + 1`
done

db2 connect to db1
db2 list history all for db1 > db2_list_hist_all_time1
ls $ARCHMETH/*/*/*/*/* > db2_ls_time1

db2 prune history `date +"%Y%m%d%H%M%S"` and delete

db2 list history all for db1 > db2_list_hist_all_time2
ls $ARCHMETH/*/*/*/*/* > db2_ls_time2

db2 terminate

echo '----- result -----'
cat db2_get_db_cfg
ls -l db2_list_hjist*
ls -l db2_ls*
tail -1 db2_ls_time1
tail -1 db2_ls_time2
----------
** It is not recommended to use /dev/null for a backup. It is a test case purpose only.

Here is an example result for V10.5 FP1 on AIX
----------
 Number of database backups to retain   (NUM_DB_BACKUPS) = 12
 Recovery history retention (days)     (REC_HIS_RETENTN) = 366
 Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF
-rw-rw-rw-    1 hidehy   build         55687 Apr 04 03:13 db2_list_hist_all_time1
-rw-rw-rw-    1 hidehy   build          2050 Apr 04 03:13 db2_list_hist_all_time2
-rw-rw-rw-    1 hidehy   build           936 Apr 04 03:13 db2_ls_time1
-rw-rw-rw-    1 hidehy   build            26 Apr 04 03:13 db2_ls_time2
S0000071.LOG
S0000071.LOG
----------
Reviewing the file size of db2_list_hist_all_timeX and db2_ls_timeX files, it tells that
"prune history xxxx and delete" deletes most of all history entries and LOG files since
size of two time2 files are smaller than time1 files.

Here is an example result for V11.1 Mod 1 FP1 on AIX
----------
 Number of database backups to retain   (NUM_DB_BACKUPS) = 12
 Recovery history retention (days)     (REC_HIS_RETENTN) = 366
 Auto deletion of recovery objects    (AUTO_DEL_REC_OBJ) = OFF
-rw-r--r--    1 hidehy   pdxdb2        56387 Apr 04 04:29 db2_list_hist_all_time1
-rw-r--r--    1 hidehy   pdxdb2        52414 Apr 04 04:29 db2_list_hist_all_time2
-rw-r--r--    1 hidehy   pdxdb2          936 Apr 04 04:29 db2_ls_time1
-rw-r--r--    1 hidehy   pdxdb2          884 Apr 04 04:29 db2_ls_time2
S0000071.LOG
S0000071.LOG
----------
Reviewing the file size of db2_list_hist_all_timeX and db2_ls_timeX files, it tells that
"prune history xxxx and delete" retains most of all history entries and LOG files since
size of two time2 files are almost same as time1. Not same symptom as V10.5 FP1 above.

Here is a list, some versions/FixPacks results.
---------------------------------------
DB2 Ver.  : histry : log file
---------------------------------------
V9.7 FP4  : delete : delete
V9.7 FP10 : delete : delete
V10.1 FP1 : delete : delete
V10.1 FP6 : retain : retain
V10.5 FP1 : delete : delete
V10.5 FP8 : retain : retain
V11.1M1FP1 : retain : retain
---------------------------------------
** delete = work as V10.5 FP1.  retain = work as V11.1 Mod 1 FP1

The differences between "delete" and "retain" versions/FixPacks may be caused by APAR
and/or enhancement code.  We would address that the later one would be worked as
expected, such as V10.1 FP6, V10.5 FP8 and V11.1 M1FP1.

Since the scenario sets (NUM_DB_BACKUPS) = 12, when run "prune history xxxx and delete",
if the number of valid full backups in the history file is equal to or greater than 12 ,
DB2 needs to retain last 12 valid full backups in the history file and delete entries
before/earlier than these last 12 valid full backups. DB2 needs to retain these history
entries and its LOG files as designed.

Side note:
- If "db2 backup database db1 to /dev/null" which is default offline backup in the above
  scenario, it will delete most of all history entries and LOG files.
- If there are 11 backups (less than NUM_DB_BACKUPS = 12), the prune will delete most of
  all history entries and LOG files.

Note:
This behavior might be changed without notice in the future. We can confirm whether this
article is valid or not by running above scenario.
Please contact your Sales Rep to submit a potential design change towards a future
release. Or please open a ticket at Request For Enhancement.
 

Related Knowledge Center pages:
PRUNE HISTORY/LOGFILE command
BACKUP DATABASE command
num_db_backups - Number of database backups configuration parameter

Related other general DB2 related information pages:
Here is an up-to-date article for each versions of DB2 latest FixPack:
Recommended Fix Packs for DB2 for Linux, UNIX and Windows
 

Here is one of helpfull links to collect data for further investigation.
Collecting data: Read first for DB2 for Linux, UNIX, and Windows products
 

Here is one of good articles, we would recommed to review:
DB2 FAQ - Frequently Asked Questions about DB2 for Linux, UNIX and Windows
 

Here is the main page for all DB2 versions' manuals.
IBM DB2 database product documentation

Here about end of support dates for all DB2 versions.

DB2 Distributed end of support (EOS) dates
 

Hope this helps.