Connections database performance tuning

connections cnx

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.com
 
exportfolder=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 $database
 
echo "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.ddl
 
db2 connect reset
 
echo "db2 -tvf db_maintenance_$database.ddl" >> $exportfolder/db_maintenance.sh
done
 
chmod +x $exportfolder/db_maintenance.sh
 
echo
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.

Leave a Reply

Your email address will not be published. Required fields are marked *