Technical Blog Post
Abstract
Hands-on example for PRUNE HISTORY command
Body
The PRUNE HISTORY command is very useful tool to remove the unnecessary archive log files, maintaining the "restore set" which is the most recent full database backup including any restores of that backup image. This document illustrates the PRUNE HISTORY command with the command line examples.
To see working of PRUNE HISTORY command, the following setup is done to make some archvive log files with backup images.(For the script detail, please check ref 1)
# 1. 200 row insert
# 2. tablespace USERSPACE1 backup
# 3. 200 row insert
# 4. full backup
# 5. 200 row insert
# 6. tablespace USERSPACE1 backup
After setting these, we will check the following options in PRUNE HISTORY command to see how the database history and archive log files are affected.
1. BASIC PRUNE HISTORY
2. WITH FORCE OPTION
3. AND DELETE
4. WITH FORCE OPTION AND DELETE
(The PRUNE LOGFILE command is deprecated from as V10.1 written in the knowledge center - ref 2)
-------------------------------------------------------
1. BASIC PRUNE HISTORY: "prune history, keeping a restore set"
I tested PRUNE HISTORY command with the timestamp(2018-04-19-02.25.00) which is after the last tablespace(USERSPACE1) backup. Even though I use that timestamp, the history entries from last full backup forward are NOT PRUNED by the command. That's because DB2 maintains the "restore set" in case that the restore is the only key to recover the database.
1-1. setup script run and db2 list history output
$ db2 list history all for tstv111 | egrep "B P|B D|X D"
B D 20180419022308001 F N S0000000.LOG S0000000.LOG
X D 20180419022311 1 D S0000000.LOG C0000000
X D 20180419022316 1 D S0000001.LOG C0000000
X D 20180419022322 1 D S0000002.LOG C0000000
B P 20180419022334001 F D S0000003.LOG S0000003.LOG
X D 20180419022336 1 D S0000003.LOG C0000000
X D 20180419022342 1 D S0000004.LOG C0000000
B D 20180419022353001 F D S0000005.LOG S0000005.LOG
X D 20180419022358 1 D S0000005.LOG C0000000
X D 20180419022404 1 D S0000006.LOG C0000000
B P 20180419022416001 F D S0000007.LOG S0000007.LOG
c.f. each egrep arguments show the following.
X D:log archive
B P:tablespace backup
B D:full backup
1-2. PRUNE HISTORY command after the last tablespace backup
(woongc@tstserv) /home/woongc/technote_prune
$ db2 connect to tstv111
Database Connection Information
Database server = DB2/AIX64 11.1.3.3
SQL authorization ID = WOONGC
Local database alias = TSTV111
(woongc@tstserv) /home/woongc/technote_prune
$ db2 prune history 20180419022500
DB20000I The PRUNE command completed successfully.
1-3. The entries for full backup forward are not PRUNED by the command
(woongc@tstserv) /home/woongc/technote_prune
$ db2 list history all for tstv111 | egrep "B P|B D|X D"
B D 20180419022353001 F D S0000005.LOG S0000005.LOG
X D 20180419022358 1 D S0000005.LOG C0000000
X D 20180419022404 1 D S0000006.LOG C0000000
B P 20180419022416001 F D S0000007.LOG S0000007.LOG
X D 20180419023225 P D S0000007.LOG C0000000
2. WITH FORCE OPTION: "prune history, NOT keeping a restore set"
Different from #1, this option deletes all the history entires based on the timestamp.
2-1. setup script run and db2 list history output
(woongc@tstserv) /home/woongc/technote_prune
$ db2 list history all for tstv111 | egrep "B P|B D|X D"
B D 20180419024329001 F N S0000000.LOG S0000000.LOG
X D 20180419024333 1 D S0000000.LOG C0000000
X D 20180419024337 1 D S0000001.LOG C0000000
X D 20180419024344 1 D S0000002.LOG C0000000
B P 20180419024355001 F D S0000003.LOG S0000003.LOG
X D 20180419024357 1 D S0000003.LOG C0000000
X D 20180419024405 1 D S0000004.LOG C0000000
B D 20180419024416001 F D S0000005.LOG S0000005.LOG
X D 20180419024421 1 D S0000005.LOG C0000000
X D 20180419024428 1 D S0000006.LOG C0000000
B P 20180419024439001 F D S0000007.LOG S0000007.LOG
2-2. PRUNE HISTORY command after the last tablespace backup
(woongc@tstserv) /home/woongc/technote_prune
$ db2 prune history 20180419024500 WITH FORCE OPTION
DB20000I The PRUNE command completed successfully.
2-3. all entries are deleted from the list history for that database
(woongc@tstserv) /home/woongc/technote_prune
$ db2 list history all for tstv111
List History File for tstv111
Number of matching file entries = 0
3. AND DELETE: "prune history and delete archive logs related to that history, keeping a restore set"
With "AND DELETE" option, you can not only prune the history itself but also delete the archive log files related the pruned history. However, as you can expect, history and archive log files which are related to the restore set are not deleted.
3-1. setup script run and db2 list history output/ls -al output for archive log path
(woongc@tstserv) /home/woongc/technote_prune
$ db2 list history all for tstv111 | egrep "B P|B D|X D"
B D 20180419023804001 F N S0000000.LOG S0000000.LOG
X D 20180419023808 1 D S0000000.LOG C0000000
X D 20180419023812 1 D S0000001.LOG C0000000
X D 20180419023820 1 D S0000002.LOG C0000000
B P 20180419023831001 F D S0000003.LOG S0000003.LOG
X D 20180419023833 1 D S0000003.LOG C0000000
X D 20180419023840 1 D S0000004.LOG C0000000
B D 20180419023850001 F D S0000005.LOG S0000005.LOG
X D 20180419023855 1 D S0000005.LOG C0000000
X D 20180419023901 1 D S0000006.LOG C0000000
B P 20180419023912001 F D S0000007.LOG S0000007.LOG
/tmp/logarch/woongc/TSTV111/NODE0000/LOGSTREAM0000/C0000000:
total 8800
drwxr-x--- 2 woongc build 4096 Apr 19 02:32 .
drwxr-x--- 3 woongc build 256 Apr 19 01:25 ..
-rw-r----- 1 woongc build 32768 Apr 19 02:23 S0000000.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:23 S0000001.LOG
-rw-r----- 1 woongc build 397312 Apr 19 02:23 S0000002.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:23 S0000003.LOG
-rw-r----- 1 woongc build 397312 Apr 19 02:23 S0000004.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:24 S0000005.LOG
-rw-r----- 1 woongc build 397312 Apr 19 02:24 S0000006.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:00 S0000007.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:00 S0000008.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:01 S0000009.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:01 S0000010.LOG
-rw-r----- 1 woongc build 352256 Apr 19 02:13 S0000011.LOG
3-2. PRUNE HISTORY command after the last tablespace backup
(woongc@tstserv) /home/woongc/technote_prune
$ db2 prune history 20180419024000 and delete
DB20000I The PRUNE command completed successfully.
3-3. History entries as well as archive log files are removed by the command
(woongc@tstserv) /home/woongc/technote_prune
$ db2 list history all for tstv111 | egrep "B P|B D|X D"
B D 20180419023850001 F D S0000005.LOG S0000005.LOG
X D 20180419023855 1 D S0000005.LOG C0000000
X D 20180419023901 1 D S0000006.LOG C0000000
B P 20180419023912001 F D S0000007.LOG S0000007.LOG
/tmp/logarch/woongc/TSTV111/NODE0000/LOGSTREAM0000/C0000000:
total 5256
drwxr-x--- 2 woongc build 4096 Apr 19 02:40 .
drwxr-x--- 3 woongc build 256 Apr 19 01:25 ..
-rw-r----- 1 woongc build 417792 Apr 19 02:39 S0000005.LOG
-rw-r----- 1 woongc build 397312 Apr 19 02:39 S0000006.LOG
-rw-r----- 1 woongc build 266240 Apr 19 02:37 S0000007.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:00 S0000008.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:01 S0000009.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:01 S0000010.LOG
-rw-r----- 1 woongc build 352256 Apr 19 02:13 S0000011.LOG
4. WITH FORCE OPTION AND DELETE: "prune history and delete archive logs related to that history, NOT keeping a restore set"
As we can easily expect, this will do #2 and #3 together. I also added this test result to show the exact result from the commands.
4-1. setup script run and db2 list history output/ls -al output for archive log path
(woongc@tstserv) /home/woongc/technote_prune
$ db2 list history all for tstv111 | egrep "B P|B D|X D"
B D 20180419030910001 F N S0000000.LOG S0000000.LOG
X D 20180419030913 1 D S0000000.LOG C0000000
X D 20180419030917 1 D S0000001.LOG C0000000
X D 20180419030924 1 D S0000002.LOG C0000000
B P 20180419030935001 F D S0000003.LOG S0000003.LOG
X D 20180419030938 1 D S0000003.LOG C0000000
X D 20180419030944 1 D S0000004.LOG C0000000
B D 20180419030955001 F D S0000005.LOG S0000005.LOG
X D 20180419031000 1 D S0000005.LOG C0000000
X D 20180419031006 1 D S0000006.LOG C0000000
B P 20180419031018001 F D S0000007.LOG S0000007.LOG
(woongc@tstserv) /home/woongc/technote_prune
$ ls -al /tmp/logarch/woongc/TSTV111/NODE0000/LOGSTREAM0000/C0000000
total 8464
drwxr-x--- 2 woongc build 4096 Apr 19 03:11 .
drwxr-x--- 3 woongc build 256 Apr 19 01:25 ..
-rw-r----- 1 woongc build 32768 Apr 19 03:09 S0000000.LOG
-rw-r----- 1 woongc build 417792 Apr 19 03:09 S0000001.LOG
-rw-r----- 1 woongc build 397312 Apr 19 03:09 S0000002.LOG
-rw-r----- 1 woongc build 417792 Apr 19 03:09 S0000003.LOG
-rw-r----- 1 woongc build 397312 Apr 19 03:09 S0000004.LOG
-rw-r----- 1 woongc build 417792 Apr 19 03:10 S0000005.LOG
-rw-r----- 1 woongc build 397312 Apr 19 03:10 S0000006.LOG
-rw-r----- 1 woongc build 417792 Apr 19 03:02 S0000007.LOG
-rw-r----- 1 woongc build 417792 Apr 19 03:02 S0000008.LOG
-rw-r----- 1 woongc build 245760 Apr 19 03:04 S0000009.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:01 S0000010.LOG
-rw-r----- 1 woongc build 352256 Apr 19 02:13 S0000011.LOG
4-2. PRUNE HISTORY command after the last tablespace backup
(woongc@tstserv) /home/woongc/technote_prune
$ db2 prune history 20180419031020 WITH FORCE OPTION AND DELETE
DB20000I The PRUNE command completed successfully.
4-3. History entries as well as archive log files are removed by the command
(woongc@tstserv) /home/woongc/technote_prune
$ db2 list history all for tstv111
List History File for tstv111
Number of matching file entries = 0
(woongc@p6db2serv) /home/woongc/technote_prune
$ ls -al /tmp/logarch/woongc/TSTV111/NODE0000/LOGSTREAM0000/C0000000
total 3624
drwxr-x--- 2 woongc build 4096 Apr 19 03:12 .
drwxr-x--- 3 woongc build 256 Apr 19 01:25 ..
-rw-r----- 1 woongc build 417792 Apr 19 03:02 S0000007.LOG
-rw-r----- 1 woongc build 417792 Apr 19 03:02 S0000008.LOG
-rw-r----- 1 woongc build 245760 Apr 19 03:04 S0000009.LOG
-rw-r----- 1 woongc build 417792 Apr 19 02:01 S0000010.LOG
-rw-r----- 1 woongc build 352256 Apr 19 02:13 S0000011.LOG
For command detail, please check the following knowledge center link.
------------------------------------
ref 1 setup script for PRUNE HISTORY command test
# setting
db2stop force
db2start
db2 drop db tstv111
db2 create db tstv111
mkdir /tmp/logarch
db2 "update db cfg for tstv111 using LOGARCHMETH1 DISK:/tmp/logarch"
db2 update db cfg for tstv111 using LOGFILSIZ 100
# this one is for rapid growth for log archive.
db2 backup db tstv111 to /dev/null
db2 connect to tstv111
db2 "create table largetbl (c1 char(254), c2 char(254), c3 char(254), c4 char(254), c5 char(254), c6 char(254), c7 char(254), c8 char(254), c9 char(254), c10 char(254), c11 char(254), c12 char(254), c13 char(254), c14 char(254), c15 char(254))"
db2 terminate
# 1. 200 row insert
db2 connect to tstv111
i=0
while [[ i -lt 200 ]]
do
date; echo "[info] $i th row inserted"
db2 +c "insert into largetbl values ('wchoi0$i', 'wchoi1$i', 'wchoi2$i', 'wchoi3$i', 'wchoi4$i', 'wchoi5$i', 'wchoi6$i', 'wchoi7$i', 'wchoi8$i', 'wchoi9$i', 'wchoiA$i', 'wchoiB$i', 'wchoiC$i', 'wchoiD$i', 'wchoiE$i')"
(( i=i+1 ))
done
db2 terminate
# 2. tablespace USERSPACE1 backup
db2 "backup db tstv111 tablespace(USERSPACE1)"
# 3. 200 row insert
db2 connect to tstv111
i=0
while [[ i -lt 200 ]]
do
date; echo "[info] $i th row inserted"
db2 +c "insert into largetbl values ('wchoi0$i', 'wchoi1$i', 'wchoi2$i', 'wchoi3$i', 'wchoi4$i', 'wchoi5$i', 'wchoi6$i', 'wchoi7$i', 'wchoi8$i', 'wchoi9$i', 'wchoiA$i', 'wchoiB$i', 'wchoiC$i', 'wchoiD$i', 'wchoiE$i')"
(( i=i+1 ))
done
db2 terminate
# 4. full backup
db2 backup db tstv111
# 5. 200 row insert
db2 connect to tstv111
i=0
while [[ i -lt 200 ]]
do
date; echo "[info] $i th row inserted"
db2 +c "insert into largetbl values ('wchoi0$i', 'wchoi1$i', 'wchoi2$i', 'wchoi3$i', 'wchoi4$i', 'wchoi5$i', 'wchoi6$i', 'wchoi7$i', 'wchoi8$i', 'wchoi9$i', 'wchoiA$i', 'wchoiB$i', 'wchoiC$i', 'wchoiD$i', 'wchoiE$i')"
(( i=i+1 ))
done
db2 terminate
# 6. tablespace USERSPACE1 backup
db2 "backup db tstv111 tablespace(USERSPACE1)"
------------------------------------
ref 2
The PRUNE LOGFILE command is deprecated and might be removed in a future release. Use the PRUNE HISTORY command instead.
------------------------------------
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
UID
ibm13286035