Technical Blog Post
50 DB2 Nuggets #19 : Tech Tip - Don't drink and archive. Archive safely!
For most of us, we refer the word 'ARCHIVE' in DB2 to DB2 Transactional Logs archiving.
There are other items that you can archive as well. Let's explore what all we can archive:
1. DB2 Transactional Logs - This refers to DB2 transactional log archiving. I assume you are familiar with this concept. Hence I won't discuss it in detail here. If you have any question, please feel free to ask us. You can add a comment to this article at the bottom.
2. DB2 Diagnostic Logs Archiving - This refers to archiving db2diag.log and <instance_name>.nfy file. We have seen few cases where our customers do not archive db2diag.log and notify file for several months/years. And when you see any problem on database server and want to review db2diag.log file, it takes several minutes to open the huge file and scroll down to correct erroneous entry of our interest. It is good practice to archive these files on regular basis. I personally recommend my customer to archive these files on monthly basis. You can write a cron job to archive the files at regular interval. The command 'db2diag -A' can be used to archive the current db2diag.log file. You can also think of archiving following files from ~/sqllib/db2dump :
3.trap/stack files and
4.older FODC directories
If a SQL query is running slow and dumping lot of error entries in db2diag.log file, then huge db2diag.log file can add to slow down of SQL query. For writing an error entry in db2diag.log file, db2sysc will take few milisec to open and write to huge db2diag.log file.
3. DB2 History File Archiving / Pruning - It refers to history file archival. As observed with db2 diagnostics logs, most of us do not look at history file size. It's size does matter to DB2 performance. History file keeps track of following events:
•A database or table spaces are backed up
•A database or table spaces are restored
•A database or table spaces are rolled forward
•A database is automatically rebuilt and more than one image is restored
•A table space is created
•A table space is altered
•A table space is quiesced
•A table space is renamed
•A table space is dropped
•A table is loaded
•A table is dropped (when dropped table recovery is enabled and you are using recoverable logging)
•A table is reorganized
•On-demand log archiving is invoked
•A new log file is written to (when using recoverable logging)
•A log file is archived (when using recoverable logging)
•A database is recovered
It is another good practice to keep pruning this file on regular interval basis, may be via cron job.
4. DB2 - Actual User Data Archiving
I highly recommend to archive the actual data from your production database if your business does not require it anymore. The older data in production database is always a headache. It consumes space and misguide DB2 optimizer sometimes. The range partitioning can be a reliable and efficient method to roll out the older data.
5. DB2 - Scripts and/or Logs files Archiving
Older version of scripts and the logs/output files generated from scripts can be archived or removed if not required.
Except DB2 transactional log files, please do not forget to compress the archived files in order to save the storage space.
Please be extra sensitive and careful before archiving any DB2 files.