Database Maintenance Automation

Regular database maintenance is an important step to improving overall TADDM performance. The scripts attached here will assist in properly automating and scheduling this database maintenance from the TADDM server, rather than scheduling on the database server. The script makes a remote connection to the database using the TADDM database account.

This has been tested on 7.2.1.4, 7.2.2.x, and 7.3.0.x, on Windows and Linux, against Oracle 11g and DB2 10.1.4, 10.5.3.

Special Prerequisite

If you are using TADDM 7.2.2 prior to FP1 or TADDM 7.2.1 prior to FP6 and using DB2 as your back-end database then you need to see the following technote to upgrade the gen_db_stats.jy before proceeding.

http://www-01.ibm.com/support/docview.wss?uid=swg1IV78520

UPDATE OCTOBER 2015: The DB2 APAR IT05733 will be included in DB2 versions;

9.7 Fix Pack 11
10.1 Fix Pack 6
10.5 Fix Pack 7

If this fix is applied, the following DB2 setting must be set;

db2set -immediate DB2_SELECTIVITY=DSCC

TADDM 7.3.x DB2 database creation script (make_db2_db.sh) includes this setting.

Once DB2 is at one of the levels above and DB_SELECTIVITY is set to DSCC, AUTO RUNSTATS may be used for DB2. If you enable AUTO RUNSTATS then gen_db_stats script execution is no longer required and can be manually removed from the dbmaint_wrapper script (below).

To enable auto-runstats for DB2, issue the following command as the instance owner where TADDM is the name of the database.

db2 CONNECT TO TADDM
db2 UPDATE DB CONFIG USING AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_RUNSTATS ON
db2 DISCONNECT TADDM

Installation

Download the files described below to the $COLLATION_HOME/custom directory on the primary storage server in a streaming environment (or domain server in a domain environment). You may need to create the directory 'custom'. $COLLATION_HOME is by default /opt/IBM/taddm/dist (C:\ibm\taddm\dist). Set all the shell scripts and Jython scripts to executable on Linux.

There are references to purge_change_history.jy in this package. If you would like run purges of the TADDM change history (recommended) during database maintenance windows, you will need to download and install the purge scripts.

Manual Usage

It is recommended to use the automation scripts included with the database maintenance script, but the script can be ran manually by issuing dbmaint.jy on Linux and dbmaint.bat on Windows.

It is recommended to shutdown TADDM before running database maintenance. Tables will be locked during reorg and this could cause problems for TADDM if it remains running.

The script expects the runstats file to be generated and put under dist/bin before run. Before running, run dist/bin/gen_db_stats.jy > dist/bin/TADDM_table_stats.sql (make sure to use this exact location and file name). If using the database maintenance automation scripts below this will be done automatically on each run.

Automating Database Maintenance

It is an important best practice to run database maintenance on a regular basis. The following section describes some tips and provides some tools to get you started in automating database maintenance. The following scripts assist in automation of database maintenance form the TADDM primary storage server.

• dbmaint.jy - Main Python script that executes the database maintenance remotely.
• dbmaint.bat - Windows wrapper script for dbmaint.jy.
• dbmaint_wrapper.sh/dbmaint_wrapper.ps1 - This is the main wrapper file that drives the entire process. It calls the stop_taddm.ps1/sh script to stop TADDM, rolls some of the logs through the roll_logs.bat script (Windows only), executes the offline backup hook script, executes the TADDM generate stats script, executes the dbmaint.bat/jy database maintenance, calls the start_taddm.ps1/sh script to start TADDM, and sends an e-mail notification using PowerShell or mailx and the Notification.ps1 script (Windows only). For Windows only, set the $email variable at the top of dbmaint_wrapper.ps1 to enable email notification.
• stop_taddm.sh/stop_taddm.ps1 - This file reads the server names from the txt files (discovery-servers.txt, etc) and using either Windows tools (%WINDIR%\system32\sc.exe) or SSH with public key trust, stops TADDM on all of the servers listed. It is important that this script run under an account that can reach the remote TADDM service without being prompted for a password. For Linux, this script assumes that public key trust is set up between the primary storage server and the other servers not requiring a password. For 7.2.1, edit the file and change references from CDTService to CDTService7.2.1.
• roll_logs.bat - This file reads the server names from the txt files (discovery-servers.txt, etc) and using Windows file sharing, rolls the error.log and TADDM.log because TADDM will not roll these logs. This script is set up by default to assume that the TADDM installation directory matches the primary storage server.
• start_taddm.sh/start_taddm.ps1 - This file reads the server names from the txt files (discovery-servers.txt, etc) and using either Windows tools (%WINDIR%\system32\sc.exe) or SSH with public key trust, starts the TADDM on all of the servers listed. It is important that this script run under an account that can reach the remote TADDM service without being prompted for a password. For Linux, this script assumes that public key trust is set up between the primary storage server and the other servers not requiring a password. For 7.2.1, edit the file and change references from CDTService to CDTService7.2.1.
• secondary-storage-servers.txt and discovery-servers.txt - These files define your environment for the other scripts to use. Before running any other scripts, make sure you list all your servers (one on each line) in these files. If you are in a domain deployment, you do no need to edit these files because the scripts will act on the local host.
• dbmaint.properties (Linux only) - This file controls properties for dbmaint_wrapper.sh on Linux.
• dbmaint_offbkp.sh (Linux only) - This is an empty script that gets called by the dbmaint_wrapper.sh script where you can place your own custom scripting to run an offline backup of the database. This is optional.
• Notification.ps1 - This is a PowerShell script to send e-mail notifications via SMTP. Edit the script to set the default $from variable and $smtpServer variable for your environment.

On Windows, you may need to install PowerShell if you are running on Windows 2003. Also, make sure that you run 'Set-ExecutionPolicy Unrestricted' for both the 32-bit and 64-bit PowerShell at the command prompt. This should only need done once.

Now simply use the Windows task scheduler or cron to schedule execution of dbmaint_wrapper.ps1/sh. It is recommended to run DB maintenance weekly. For Windows, there is a local security policy setting for Windows 2008 that can prevent the automated script from accessing the other servers on the network. The policy name is "Network access: Do not allow storage of passwords and credentials for network authentication" and is found under "SECPOL.MSC | Security Settings | Local Policies | Security Options". If this is set to enabled then the scripts will be unable to remotely control the other servers. Also ensure that the scheduled task is run as the TADDM local user and "run with highest privileges" is enabled so that the TADDM service can be accessed for start and stop.