Etiquetas

Hands-on example for PRUNE HISTORY command

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.