IBM Support

DB2 configuration for keeping Daily, Monthly, Yearly backups

Question & Answer


Question

What configuration needs to be put in place to keep DB2 database backup for Daily, Monthly and yearly retention when using the Tivoli Storage Manager?

Answer

To retain different sequences for the retention of DB2 backups, it is recommended to use a different Tivoli Storage Manager node: One node for Daily backups, another node for Monthly backups and a different node for the backups that would be kept Yearly. The DB2 database backup would need to be configured with the Tivoli Storage Manager API interface as follows:

In the dsm.sys options file specify the following Servername stanzas to accommodate the backups.

    Servername DB2Daily
    Nodename DailyNode
    ...
    Servername DB2Monthly
    Nodename MonthNode
    ...
    Servername DB2Yearly
    Nodename YearNode

The DB2 cfg would be configured to ensure the log files would also be associated for the Daily backups. For example:
    LOGARCHMETH1 TSM
    LOGARCHOPT1 -Servername=DB2Daily

The DB2 .../sqllib/userprofile file would point to the configuration files for the Tivoli Storage Manager API configuration, for example:
    export DSMI_CONFIG=/usr/tivoli/tsm/client/api/bin64/dsm.opt
    export DSMI_DIR=/usr/tivoli/tsm/client/api/bin64
    export DSMI_LOG=/tmp

Based on the example above, the dsm.opt file would contain the entry: SERVERNAME DB2Daily
Options files would also be created for the Daily, Monthly and Yearly backups, for example:
    /usr/tivoli/tsm/client/api/bin64/db2daily.opt would contain: SERVERNAME DB2Daily
    /usr/tivoli/tsm/client/api/bin64/db2monthly.opt would contain: SERVERNAME DB2Monthly
    /usr/tivoli/tsm/client/api/bin64/db2yearly.opt would contain: SERVERNAME DB2Yearly

For the monthly/yearly backups, this would need to use the different configuration.
  • If DB2 can be restarted, then update the DSMI_CONFIG to point to the db2monthly.opt file and perform the backup with this monthly configuration in place. Note, the LOGARCHOPT1 should not be updated as all the log files should always be stored under the DailyNode.
  • If DB2 cannot be restarted, then the configuration would be changed by modifying the contents of the DSMI_CONFIG file. For example, for the Monthly backup, the file pointed to by the DSMI_CONFIG would be updated to contain the entry SERVERNAME DB2Monthly

  • Note that it is critical for the dsm.opt file pointed to by the DSMI_CONFIG to be changed back to the DB2Daily value immediately after the Monthly/Yearly backup is complete.

The deletion of the backups can be automated for the Daily backups by enabling the NUM_DB_BACKUPS and REC_HIS_RETENTN parameters within DB2 (more information on these settings can be found in the DB2 information center). These settings will perform the deletions at the end of each full backup, removing those entries from the history file that are older than the daily retention that is set within these DB2 parameters. Note that this deletion processing will also remove the entries from the history file for any Monthly or Yearly backups, however, this is not a concern as these monthly/yearly backups are held under a different nodename and thus not affected by any deletion processing

To accommodate these DB2 automated deletions, the DailyNode would be need to be configured to allow the deletion of the backups by setting BACKDELETE=YES for the node definition on the Tivoli Storage Manager Server.

The db2adutl would be used to remove the older backups for the Monthly/Yearly nodes. This would require setting the DSMI_CONFIG within the OS for the configuration that is to be used for the Monthyl/Yearly node and then run the db2adult utility.

[{"Product":{"code":"SSGSG7","label":"Tivoli Storage Manager"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Client","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF025","label":"Platform Independent"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB26","label":"Storage"}}]

Product Synonym

TSM

Document Information

Modified date:
17 June 2018

UID

swg21571147