Connections database performance tuning

Nico Meisenzahl

Follow

Feb 25, 2017 · 2 min read

To get a smooth running Connections environment you need to run some maintenance tasks on all of your DB2 databases. I have noticed several times in the past that the DB2 automatic maintenance tasks are not sufficient.

IBM provides SQL scripts to run those tasks on a regular basis. You will find them in following folders:

• <cnxroot>/xkit/connections.sql/
• <cnxwizard>/connections.sql/

Please run those scripts for every Connections database in following order:

1. Runstat
2. Reorg
3. Runstat

Those scripts are only available for Connections databases. If you use third party applications like Docs, FEB you will need to create your on maintenance scripts. Because of this I created a linux-based script to create custom maintenance scripts for all databases located on your DB2 database server.

Copy following code and run it with DB2 admin access:

#!/bin/bash
# Nico Meisenzahl
# nico.meisenzahl@panagenda.comexportfolder=db_maintenance
mkdir -p $exportfolder
rm -f ./$exportfolder/*databases=$(db2 list database directory | grep alias | awk '{print $4}' | sed '/TOOLSDB/d' | sort)for database in ${databases[@]}
do
db2 connect to $databaseecho "CONNECT TO $database;" >> $exportfolder/db_maintenance_$database.ddl
db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/RUNSTATS ON TABLE /g' | sed 's/$/ WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;/g' | sed '/SYS/d' >> $exportfolder/db_maintenance_$database.ddl
echo "FLUSH PACKAGE CACHE DYNAMIC;" >> $exportfolder/db_maintenance_$database.ddl
echo "COMMIT;" >> $exportfolder/db_maintenance_$database.ddl
db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG TABLE /g' | sed 's/$/;/g' | sed '/SYS/d' >> $exportfolder/db_maintenance_$database.ddl
echo "COMMIT;" >> $exportfolder/db_maintenance_$database.ddl
db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/REORG INDEXES ALL FOR TABLE /g' | sed 's/$/;/g' | sed '/SYS/d' >> $exportfolder/db_maintenance_$database.ddl
echo "COMMIT;" >> $exportfolder/db_maintenance_$database.ddl
db2 "list tables for all" | awk '{print $2"." $1}' | awk '/\w/ {print $0}' | sed '/^Schema.Table/d' | sed '/^record(s)./d' | sed 's/^/RUNSTATS ON TABLE /g' | sed 's/$/ WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;/g' | sed '/SYS/d' >> $exportfolder/db_maintenance_$database.ddl
echo "FLUSH PACKAGE CACHE DYNAMIC;" >> $exportfolder/db_maintenance_$database.ddl
echo "COMMIT;" >> $exportfolder/db_maintenance_$database.ddl
echo "CONNECT RESET;" >> $exportfolder/db_maintenance_$database.ddldb2 connect resetecho "db2 -tvf db_maintenance_$database.ddl" >> $exportfolder/db_maintenance.sh
donechmod +x $exportfolder/db_maintenance.shecho
echo ----------------------------------
echo 'Please review "db_maintenance_*.ddl" before running "db_maintenance.sh".'
echo ----------------------------------The script will create a folder called db_maintenance. Within this folder, you will find a db_maintenance_*.ddl file for each of your databases. It will also create a db_maintenance.sh which will run all maintenance scripts.