50 DB2 Nuggets #19 : Tech Tip - Don't drink and archive. Archive safely!
YogeshGawali 060001M1GN Visits (3507)
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 :
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
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.