Managing Db2 Warehouse transaction logs

When your instance is running many transactions, your archive log directory can fill and you might have to remove log files to free up space.

About this task

You can either manually delete unneeded log files and directories or use the prune history command.

If the Db2 Warehouse instance is failing because of a lack of space, prior to pruning logs you can edit the associated PVC and increase the size that is allocated. The storage class that you are using must have allowVolumeExpansion set to True.

Note: Before you remove log directories, make sure that no backup or restore operation is running. You can use the db2 list utilities command to confirm.

Procedure

Use one of these methods to manage log files:

Manually delete unneeded log directories and files

By default, Db2 Warehouse archives transaction logs to disk with the following settings:

First log archive method (LOGARCHMETH1): DISK:/mnt/bludata0/db2/archive_log/
Number of primary log files (LOGPRIMARY): 20
Number of secondary log files (LOGSECOND): 30

The following formula is used to determine how many logs are kept in each multiple logical node (MLN):

(log primary + log secondary) * 2

So by default, (20 + 30) * 2 or 100 logs are kept.

To maintain this number of logs, assume that you have a deployment of one MLN. Archived logs are kept in sub-directories whose name starts with "C" in the /mnt/bludata0/db2/archive_log/db2inst1/BLUDB/NODE0000/LOGSTREAM0000 directory.

So if directories from C0000000 to C0000005 exist, you would remove C0000000 to C0000004.

Then, within the C0000005 directory, you would keep only the latest 100 log files. The file names begin with "S." If the C0000005 directory has S0000000.LOG through S0000200.LOG, you would keep S0000101.LOG through S0000200.LOG.

You then repeat this process for each MLN, NODE0000 to NODExxxx.

To remove the log directories, use the following command while logged in as the db2inst1 user:

sudo rm -r directory

Use the Db2 Warehouse prune history command

Find the timestamp of the logs that you want to prune (up to and including) by running the following command:

db2 list history all for database-name | egrep "B P|B D|X D"

The command returns the timestamps of the following storage items:

X D:log archive
B P:tablespace backup
B D:full backup

The basic prune history command, db2 prune history timestamp, allows Db2 Warehouse to maintain a restore set. If you use db2 prune history timestamp with force option Db2 Warehouse does not keep the restore set. You can also use command parameters such as AND DELETE to specify that associated log archives are physically deleted when the history file entry is removed, and LOGFILE PRIOR TO log-file-name to specify the log files to delete by file name.

For more details, see the following topics: