Connections database performance tuning

Nico Meisenzahl
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:

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

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 resetecho "db2 -tvf db_maintenance_$database.ddl" >> $exportfolder/db_maintenance.sh
done
chmod +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.

ICS & IBM Connections

Stories related to IBM Connections and other ICS topics by Nico Meisenzahl.

Nico Meisenzahl

Written by

Senior Consultant @panagenda, @soccnx & @DockerRosenheim team member. Blogger, speaker & IBM Champion. Loves DevOps, K8s. His desk is a ping pong table.

ICS & IBM Connections

Stories related to IBM Connections and other ICS topics by Nico Meisenzahl.