Technical Blog Post
Abstract
50 DB2 Nuggets #26 : Tech Tip - Set up scheduled automatic maintenance backup and monitor it.
Body
If you need regular database backup during night or weekends, use DB2 automatic maintenance feature.
Here is DB2 V11.5 Information Center URL:
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.admin.ha.doc/doc/t0051357.html
1) How automatic maintenance jobs run?
The automatic maintenance feature has two phases. 1) Evaluation and 2) Execution.
1) Evaluation: Check whether we do task. Evaluation activity starts in every 2 hours since database is activated.
2) Execution: Do the task
Once automatic maintenance parameter for database sets enabled and policy is registered, the backup job run during maintenance window.
Automatic maintenance (AUTO_MAINT) = ON
Automatic database backup (AUTO_DB_BACKUP) = ON
If the following message in db2diag.log, scheduled job ran successfully.
START : Automatic job "Backup database online" has started on database SAMPLE, alias SAMPLE
STOP : Automatic job "Backup database online" has completed successfully on database SAMPLE, alias SAMPLE
2) How to make policy for scheduled job.
If there is no current running automatic maintenance job, refer to InfoCenter or edit the following example.
In the samples directory (SQLLIB/samples/automaintcfg in Linux and UNIX environments and SQLLIB\samples\automaintcfg in Windows environments) there are four XML files that contain example automated maintenance policy specification.
db2 "call sysproc.automaint_set_policyfile('MAINTENANCE_WINDOW','mywinmaint.xml')"
db2 "call sysproc.automaint_set_policyfile( 'AUTO_BACKUP', 'myAutoBackup.xml' )"
Suppose I need to run backup at midnight every day as system is busy during regular business hour.
- Maintenance window starting time: 22:30 (for 4 hours every day night).
- Number of previous backup is less than: 1
- Time since last backup: 1 day
- Log space consumed since last backup: 25600 * 4KB.
I have the following output for the backup.
db2 "call sysproc.automaint_get_policyfile('MAINTENANCE_WINDOW','mywinmaint.xml')"
<?xml version="1.0" encoding="UTF-8"?>
<DB2MaintenanceWindows
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
<!-- Online Maintenance Window -->
<OnlineWindow Occurrence="During" startTime="22:30:00" duration="04" >
<DaysOfWeek>All</DaysOfWeek>
<DaysOfMonth>1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31</DaysOfMonth>
<MonthsOfYear>All</MonthsOfYear>
</OnlineWindow>
</DB2MaintenanceWindows>
The criteria for running backup job is that numberOfFullBackups < 1, or timeSinceLastBackup > 24 hours, or logSpaceConsumedSinceLastBackup > 25600.
db2 "call sysproc.automaint_get_policyfile( 'AUTO_BACKUP', 'myAutoBackup.xml' )"
<?xml version="1.0" encoding="UTF-8"?>
<DB2AutoBackupPolicy
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
<!-- Backup Options -->
<BackupOptions mode="Online">
<BackupTarget>
<DiskBackupTarget>
<PathName>C:\DB2\NODE0000\SQL00002\DB2AUTOBACKUPS</PathName>
</DiskBackupTarget>
</BackupTarget>
</BackupOptions>
<!-- Frequency of automatic backups -->
<BackupCriteria numberOfFullBackups="1" timeSinceLastBackup="24" logSpaceConsumedSinceLastBackup="25600"/>
</DB2AutoBackupPolicy>
One of those 3 criteria meet, scheduled job will be kicked off.
Regarding log space consumed, it is calculated as follows:
Suppose last log file in latest backup image is S0009594.LOG and archived log file in db2diag.log before the schedule is S0010385.LOG. Database configuration for LOGFILSIZ is 2048
10385-9594 = 791, So 791 * 2048 = 1619968 * 4KB
Or health snapshot display evaluation output which shows log space consumed.
I got message in db2diag that the job ran successfully.
STOP : Automatic job "Backup database online" has completed successfully on database SAMPLE, alias SAMPLE
3) Troubleshooting
- If schedule job didn't run last night, check database parameter and policy
- Collect db2 alert and health snapshot if the evaluation activity performed successfully.
db2 "get alert cfg for databases"
db2 "get alert cfg for database on SAMPLE "
If health indicator db.db_backup_req sets as disabled on "Threshold or State checking", health snapshot doesn't collect information.
Indicator Name = db.db_backup_req
Threshold or State checking = Disabled
Then update alert configuration with the following command, and wait next 2 hours after database is activated.
db2 "update alert configuration for database on SAMPLE using db.db_backup_req set THRESHOLDSCHECKED YES"
Health snapshot display current status of Health indicator. It shows last backup was taken at 2014-05-07 and log space consumed "12000" 4KB.
db2 "get health snapshot for db on SAMPLE show detail with full collection"
Indicator Name = db.db_backup_req
Value = 0
Evaluation timestamp = 09-05-2014 17:07:39.059000
Alert state = Normal
Additional information = The last successful backup was taken at "20140507171000". The log space consumed since this last backup has been "12000" 4KB pages. Automation for database backup is set to "ON". The last automated backup returned with SQLCODE = "N/A". The longest estimated backup time is "N/A".
- Build timeline for activating database and evaluation job.
Suppose maintenance window sets from 06:00 AM until 10:00 AM. The instance started at 6:00 AM and database is activated at 9:00 AM, then maintenance job would fail. Evaluation will start 11:00 AM(2 hours later), which is out of maintenance window so that backup will not run.
So you need to make sure database is activated at least 2 hours before.
[{"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
ibm11141498