DB2 database maintenance

You must maintain the TADDM DB2 database regularly to ensure acceptable performance.

The following DB2 utilities are available:

REORG: After many changes to table data that are caused by the insertion, deletion, and updating of variable length columns activity, logically sequential data might be on non-sequential physical data pages. Because of that, the database manager performs extra read operations to access data. Reorganize DB2 tables to eliminate fragmentation and reclaim space by using the REORG utility. Use the REORG utility as needed, if RUNSTATS takes longer than typically to complete, or the DB2 REORGCHK command indicates a need for it. Shut down the TADDM server when you run the REORG utility, as during an offline table or index reorganization (data defragmentation), applications can access but not update the data in tables. Since TopologyBuilder runs frequently, even without discovery, such locks might cause unpredictable results within the application.

RUNSTATS (manual statistics collection): The DB2 optimizer uses information and statistics in the DB2 catalog to determine the best access to the database, which is based on the query that is provided. Statistical information is collected for specific tables and indexes in the local database when you run the RUNSTATS utility. When significant numbers of table rows are added or removed, or if data in columns, for which you collect statistics, is updated, the RUNSTATS command must be used to update the statistics. For optimal performance, complete the RUNSTATS task weekly, or daily in situations, where there is high database activity. Lack of updated statistics might cause severe performance degradation within TADDM. You can run the RUNSTATS utility while the TADDM server is running. TADDM requires specific RUNSTATS format that is described later, and DB2 AUTO_RUNSTATS option must be turned off.

AUTO_RUNSTATS (automatic statistics collection)

You can enable the automatic statistics collection, also known as auto-runstats, to let DB2 decide whether the TADDM database statistics must be updated. The RUNSTATS utility is run in the background and the database statistics are always up to date.

To enable automatic statistics collection, you must set the parameters AUTO_MAINT, AUTO_TBL_MAINT, and AUTO_RUNSTATS to ON. Run the following command:

CONNECT TO <db alias>
UPDATE DB CONFIG USING AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_RUNSTATS ON

where <db alias> is the name of your database.

Restriction: You can use this utility only when you have DB2 APAR IT05733 installed and the DB2_SELECTIVITY=DSCC parameter set. The DB2 APAR IT05733 is included in the following and later releases of DB2:

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

To set the DB2_SELECTIVITY=DSCC parameter on DB2 version 10.x, run the following command:

db2set -immediate DB2_SELECTIVITY=DSCC

Note: DB2 9.7 does not support the -immediate parameter. To set the DB2_SELECTIVITY=DSCC parameter in this version, run the db2set DB2_SELECTIVITY=DSCC command and restart DB2.

Note: If the TADDM user upgrades the DB2 version in a TADDM installation, then compatible version of the driver should also be updated. You can ask your DBA for db2jcc.jar from the TADDM DB2 server, or you can download the one appropriate for your version of DB2 here:http://www-01.ibm.com/support/docview.wss?uid=swg21363866. Once you have it, stop TADDM, copy it to dist/lib/jdbc/, confirm permissions are correct so that the TADDM user can read the file and then start TADDM. Repeat this step on all TADDM servers in your environment.

DB2 HEALTH MONITOR: It is good practice to run the DB2 health monitor against the TADDM database to proactively monitor if conditions changed such that RUNSTATS, or REORG, or any other tuning is required. The health monitor can alert a database administrator of potential system health issues. The health monitor proactively detects issues that might lead to hardware failures, or to unacceptable system performance, or capability. Thanks to the proactive health monitoring, you can address an issue before it becomes a problem that affects system performance.

DB2 PERFORMANCE ANALYSIS SUITE

When a DB2 problem is suspected, the Performance Analyst tool can quickly analyze a DB2 snapshot that is taken during the time of the problem and suggest actions. You can download this tool at https://www.ibm.com/developerworks/community/groups/community/perfanalyst.

To take a DB2 snapshot for TADDM, complete the following steps:

Connect to your TADDM database from the DB2 server and run the following command:

db2 -tf updmon.sql

where the updmon.sql file contains the following entries:

UPDATE MONITOR SWITCHES USING BUFFERPOOL ON ;
UPDATE MONITOR SWITCHES USING LOCK       ON ;
UPDATE MONITOR SWITCHES USING SORT       ON ;
UPDATE MONITOR SWITCHES USING STATEMENT  ON ;
UPDATE MONITOR SWITCHES USING TABLE      ON ;
UPDATE MONITOR SWITCHES USING UOW        ON ;
UPDATE MONITOR SWITCHES USING TIMESTAMP  ON ;
RESET MONITOR ALL

After step 1 is completed, run the DB2 get monitor switches command to check whether they are all set. They all must have status ON.
Run the process, with which you have performance issues.
At appropriate intervals, while the slow process is running, run the following command from DB2:
```
db2 get snapshot for all on <dbname> > <dbname>-dbsnap.out
```
Run this command from the same window that you ran the command in step 1. This command cannot be run with the use of a script.
Run the snapshots by using a different time stamped output file each time. Run them with such intervals that there are three, or four snapshots during the process, but the time between the runs does not exceed 1 hour.

Once the snapshot is collected, analyze them with the Performance Analyst tool, starting with the last snapshot. For example, high CPU and high average execution time on the statement tab for a query that is run many times generally indicates an optimization issue, which can be resolved with the RUNSTATS utility. A high overflow percent on the tables tab can indicate a need for the REORG utility. Check the buffer pool tab to ensure that there are no alerts, too small buffer pool might lead to poor performance.

Before you begin

After any major maintenance that generates a schema change, for example, after you apply a fix pack, you must generate the TADDM_table_statistics.sql file on the TADDM storage server. The file is needed for the RUNSTATS database maintenance tasks that you must perform regularly. TADDM requires a special format to update database statistics because of a DB2 limitation when handling columns with large common prefixes such as the class names, which are used extensively within TADDM. For this reason, do not use DB2 AUTO_RUNSTATS option, use the RUNSTATS syntax that you generate by completing the following steps. However, if you have DB2 APAR IT05733 installed and the DB2_SELECTIVITY=DSCC parameter set, you can use the AUTO_RUNSTATS option.

Note: The following instructions are given for the Linux and UNIX operating systems. To perform database maintenance on the Windows operating system, use the corresponding .bat script instead of the .sh script.

To generate the TADDM_table_stats.sql file, complete the following steps:

Run the following command:
```
cd $COLLATION_HOME/bin
```
Run the following command, where tmpdir is a directory, where this file can be created:
```
./gen_db_stats.jy > tmpdir/TADDM_table_stats.sql
```
In a streaming server deployment, run this command on the primary storage server.
Copy the file to the database server, or provide it to your database administrator (DBA) to run against the TADDM database as shown in step 2 in the Procedure. Update database statistics at least weekly, or more often, if there are large changes to any tables.

To perform maintenance on a DB2 database, complete the following steps:

To use the REORG utility, complete the following steps:
1. On the database server, place the following SQL query, which generates the REORG TABLE commands, in a file:
```
select 'reorg table '||CAST(RTRIM(creator) AS VARCHAR(40))||'. 
 "'||substr(name,1,60)||'" ; ' from sysibm.systables where creator
 = 'dbuser' and type = 'T' and name not in ('CHANGE_SEQ_ID') 
 order by 1;
```
  where dbuser is the value from com.collation.db.user=.
  Note: Make sure that the letter case of dbuser is the same as for the value that is specified in the database's sysibm.systables table, column creator.
2. Stop the TADDM server.
3. At a DB2 command line, connect to the database and run the following commands:
```
db2 –x –tf temp.sql > cmdbreorg.sql
db2 –tvf cmdbreorg.sql > cmdbreorg.out
```
4. Make sure that the REORG utility was successful by checking the cmdbreorg.out file for errors.
5. Start the TADDM server.
To use the RUNSTATS utility, complete the following steps. Automate the process to run at least weekly.
1. On the database server, run the TADDM-specific RUNSTATS command by using the output that you generated earlier:
```
db2 -tvf tmpdir/TADDM_table_stats.sql  > table_stats.out
```
2. Make sure that the RUNSTATS utility was successful by checking the table_stats.out file for errors.