Tuesday, January 23, 2018

IBM Connections Database performance tuning - Windows scripts

Mostly as a reminder to myself, here´s how I do weekly performance tuning on the Connections databases.

I prefeer working with Linux servers when it comes to IBM Connections and DB2. But customers also use Microsoft Windows. So here´s a Windows DB2 tuning guide for you :-)
Doing the Database Maintenance and performance tuning for the Connections databases on Linux, I usually follow Nico Meisenzahls procedure, which work great on Linux!

But when it comes to DB2 running on Windows servers, the scripts are a bit different when it comes to looping through the databases.

The Wizard contains almost all the scripts you need. The only thing that is missing, is for the IBM Docs, Concord, database.
And, as Nico suggests, the best result from running runstats and reorg is to first run runstats, then reorg and then runstats again. This script takes that under consideration.

The Connections databases are looped through in 2 different manners. First, the database scripts for Activities, Blogs, Communities, Dogear, Dorum, Mobil and Profiles are looped through.
For those databases, the Wizard has 2 sql scripts for, the "runstats.sql" and the "reorg.sql".

But for the remaining databases, PushNotification, Files, Homepage, Metrics and Wikis, the script names are "updatestats.sql" and "reorg.sql". Pluss, the Communities Database has a Calendar, which has its own runstats and reorg files.
This is why the code show 3 different loops, so that the correct scripts for the spesific databases are run.

So, here´s what I use for the Connections databases on Windows:

Notice at the end of the script that I run a DB2 command which calls a  "concord_stats_reorg.txt" file as input. This is the updatestats and reorg script for the IBM Docs database, which is not included in the Wizard db scripts.
At the bottom of this blog entry, you can see the content of this file as well.
(This input file was given to me from my man Nico).

First, I create a script called "databaseMaintenance.bat" and insert the following:


@echo off REM databaseMaintenance.bat REM Needed for variable expansion SETLOCAL ENABLEDELAYEDEXPANSION REM Set Connections Wizard Path, change this to match your environment: set WIZARDPATH=D:\IBM\Wizards REM get certain databases of db2 instance and loop through the list: FOR %%a IN (ACTIVITIES BLOGS DOGEAR FORUM MOBILE PROFILES) DO ( set DATABASES=%%a,!DATABASES! title Starting database %%a RUNSTATS on %date% at %time%... @ECHO Running RUNSTATS on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\runstats.sql title Starting database %%a REORG on %date% at %time%... @ECHO Running REORG on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\reorg.sql title Starting database %%a RUNSTATS on %date% at %time%... @ECHO Running RUNSTATS on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\runstats.sql ) @ECHO Successfully ran maintenance for %DATABASES% REM get certain databases of db2 instance and loop through the list: FOR %%a IN (PUSHNOTIFICATION FILES HOMEPAGE METRICS WIKIS) DO ( set DATABASES=%%a,!DATABASES! title Starting database %%a RUNSTATS on %date% at %time%... @ECHO Running RUNSTATS on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\updatestats.sql title Starting database %%a REORG on %date% at %time%... @ECHO Running REORG on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\reorg.sql title Starting database %%a RUNSTATS on %date% at %time%... @ECHO Running RUNSTATS on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\updatestats.sql ) @ECHO Successfully ran maintenance for %DATABASES% REM get Communities database of db2 instance: FOR %%a IN (COMMUNITIES) DO ( set DATABASES=%%a,!DATABASES! title Starting database %%a RUNSTATS on %date% at %time%... @ECHO Running RUNSTATS on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\calendar-runstats.sql DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\runstats.sql title Starting database %%a REORG on %date% at %time%... @ECHO Running REORG on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\calendar-reorg.sql DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\reorg.sql title Starting database %%a RUNSTATS on %date% at %time%... @ECHO Running RUNSTATS on Database %%a DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\calendar-runstats.sql DB2CMD.EXE -c -w -i DB2 -td@ -vf %WIZARDPATH%\connections.sql\%%a\db2\runstats.sql ) @ECHO Successfully ran maintenance for %DATABASES% REM Running Runstats, Reorg and Runstats on Concord database: title Starting database CONCORD REORG on %date% at %time%... @ECHO Running RUNSTATS on Database CONCORD db2 -tvf D:\scripts\concord_stats_reorg.txt @ECHO Successfully ran maintenance for CONCORD



Remember to change the WIZARDPATH variable to match your location of the Wizard directory.

And here´s the content of the "concord_stats_reorg.txt" input file:


CONNECT TO CONCORD;
RUNSTATS ON TABLE CONCORDDB.ASSOCIATEDWITH WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.CLIPBOARD WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.CUSTOMER_CREDENTIAL WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOCEDITORS WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOCUMENTSESSION WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_ACTIVITY WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_ENTITLEMENT WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_HISTORY WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_RECENTS WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_REFERENCE WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_REVISION WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.HKLMGR WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.HKLMPR WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.HKTASK WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.HKTREG WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.MESSAGE WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.ORG_ENTITLEMENT WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.PRODUCT WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.SUBSCRIBER WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.TASK WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.TASKHISTORY WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.USERPREFERENCE WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.USER_DOC_CACHE WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.USER_ENTITLEMENT WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
FLUSH PACKAGE CACHE DYNAMIC;
COMMIT;
REORG TABLE CONCORDDB.ASSOCIATEDWITH;
REORG TABLE CONCORDDB.CLIPBOARD;
REORG TABLE CONCORDDB.CUSTOMER_CREDENTIAL;
REORG TABLE CONCORDDB.DOCEDITORS;
REORG TABLE CONCORDDB.DOCUMENTSESSION;
REORG TABLE CONCORDDB.DOC_ACTIVITY;
REORG TABLE CONCORDDB.DOC_ENTITLEMENT;
REORG TABLE CONCORDDB.DOC_HISTORY;
REORG TABLE CONCORDDB.DOC_RECENTS;
REORG TABLE CONCORDDB.DOC_REFERENCE;
REORG TABLE CONCORDDB.DOC_REVISION;
REORG TABLE CONCORDDB.HKLMGR;
REORG TABLE CONCORDDB.HKLMPR;
REORG TABLE CONCORDDB.HKTASK;
REORG TABLE CONCORDDB.HKTREG;
REORG TABLE CONCORDDB.MESSAGE;
REORG TABLE CONCORDDB.ORG_ENTITLEMENT;
REORG TABLE CONCORDDB.PRODUCT;
REORG TABLE CONCORDDB.SUBSCRIBER;
REORG TABLE CONCORDDB.TASK;
REORG TABLE CONCORDDB.TASKHISTORY;
REORG TABLE CONCORDDB.USERPREFERENCE;
REORG TABLE CONCORDDB.USER_DOC_CACHE;
REORG TABLE CONCORDDB.USER_ENTITLEMENT;
COMMIT;
REORG INDEXES ALL FOR TABLE CONCORDDB.ASSOCIATEDWITH;
REORG INDEXES ALL FOR TABLE CONCORDDB.CLIPBOARD;
REORG INDEXES ALL FOR TABLE CONCORDDB.CUSTOMER_CREDENTIAL;
REORG INDEXES ALL FOR TABLE CONCORDDB.DOCEDITORS;
REORG INDEXES ALL FOR TABLE CONCORDDB.DOCUMENTSESSION;
REORG INDEXES ALL FOR TABLE CONCORDDB.DOC_ACTIVITY;
REORG INDEXES ALL FOR TABLE CONCORDDB.DOC_ENTITLEMENT;
REORG INDEXES ALL FOR TABLE CONCORDDB.DOC_HISTORY;
REORG INDEXES ALL FOR TABLE CONCORDDB.DOC_RECENTS;
REORG INDEXES ALL FOR TABLE CONCORDDB.DOC_REFERENCE;
REORG INDEXES ALL FOR TABLE CONCORDDB.DOC_REVISION;
REORG INDEXES ALL FOR TABLE CONCORDDB.HKLMGR;
REORG INDEXES ALL FOR TABLE CONCORDDB.HKLMPR;
REORG INDEXES ALL FOR TABLE CONCORDDB.HKTASK;
REORG INDEXES ALL FOR TABLE CONCORDDB.HKTREG;
REORG INDEXES ALL FOR TABLE CONCORDDB.MESSAGE;
REORG INDEXES ALL FOR TABLE CONCORDDB.ORG_ENTITLEMENT;
REORG INDEXES ALL FOR TABLE CONCORDDB.PRODUCT;
REORG INDEXES ALL FOR TABLE CONCORDDB.SUBSCRIBER;
REORG INDEXES ALL FOR TABLE CONCORDDB.TASK;
REORG INDEXES ALL FOR TABLE CONCORDDB.TASKHISTORY;
REORG INDEXES ALL FOR TABLE CONCORDDB.USERPREFERENCE;
REORG INDEXES ALL FOR TABLE CONCORDDB.USER_DOC_CACHE;
REORG INDEXES ALL FOR TABLE CONCORDDB.USER_ENTITLEMENT;
COMMIT;
RUNSTATS ON TABLE CONCORDDB.ASSOCIATEDWITH WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.CLIPBOARD WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.CUSTOMER_CREDENTIAL WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOCEDITORS WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOCUMENTSESSION WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_ACTIVITY WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_ENTITLEMENT WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_HISTORY WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_RECENTS WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_REFERENCE WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.DOC_REVISION WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.HKLMGR WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.HKLMPR WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.HKTASK WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.HKTREG WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.MESSAGE WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.ORG_ENTITLEMENT WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.PRODUCT WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.SUBSCRIBER WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.TASK WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.TASKHISTORY WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.USERPREFERENCE WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.USER_DOC_CACHE WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
RUNSTATS ON TABLE CONCORDDB.USER_ENTITLEMENT WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS;
FLUSH PACKAGE CACHE DYNAMIC;
COMMIT;
CONNECT RESET;


And now, it´s just a matter of creating a Windows Scheduled task to launch this script on a weekly basis. Run this task as the "db2admin" user.

And thanks to my buddy Nico!