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".
This is why the code show 2 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 COMMUNITIES 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 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!

Friday, January 12, 2018

I´m selected as an ICS IBM Champion for 2018.

I received some VERY GOOD NEWS in my inbox just now.

Congratulations, you're an IBM Champion!Hello, and welcome! After reviewing and evaluating your contributions to the IBM technology community over the past 12 months, IBM is happy to announce that you have been selected as an IBM Champion for 2018.
I am both humbled and honored. 

First of all, I want to thank all of you who nominated me to become an IBM Champion for 2018!! It´s so nice to be appreciated for my contributions in the ICS Community, and this is a huge feather in my cap and that my contributions and knowledge ain´t all that bad :-D


This year it actually happened, I´m now in an elite group of other great IBM Champions. 

I hope I can live up to my "peers". I´m not sure I can call them my peers yet, but I will try my very best to live up to the same standards as you guys, so that I can in fact call you my peers!

2018 is shaping up to be a great year!

Tuesday, December 19, 2017

Connections reminder: Not having the nodeagents running while rolling out iFixes is a bad idea!

I had a PMR with IBM recently. Something broke in IBM Connections after the customer applied some iFixes.
The result was that Communities could not find a Cookie.js javascript and SystemOut.log was showing
AjaxFramework E net.jazz.ajax.internal.util.TraceSupport error lconn.core.util.Cookie: Unable to access any resource associated with lconn.core.util.Cookie

The reason this was happening was the fact that the nodeagent was stopped. Only the DMGR was running. And the updateWizard reported every iFix installation as a successful one!!

I asked IBM why the updateWizard did not check if such an important thing as the nodeAgent was running or not. The answer was that there were several Enhancment Requests to the updateWizard, so that it actually checks if the NodeAgent is running before proceeding with installing the iFixes.

So, in order to fix the situation, I had to launch the updateWizard and uninstall every iFix. Then sync the nodes.
And then I reapplied the iFixes, this time with the nodeAgent running. Connections was working fine after that.


So, as a reminder: When you install a CR fixpack, you can do this while the NodeAgent is shut down.
When installing iFixes, you do have to have the NodeAgent running.


Thursday, November 2, 2017

Brainfart from me! ssl_enabled=true is really, really important even though the service is disabled!!

OMG, I just had a huge brainfart!!

I had an issue on a Connections 5.5 CR3 site where the option to get notified about "Libraries" was in the GUI.


But the problem was that ECM has never been installed on this system, nor on a previously migrated system!!

I also had the Indexing tasks set to "all_configured" which gave me indexing errors in SystemOut.log:
[11/1/17 23:16:02:974 CET] 0000170f InitialWorkFa E com.ibm.connections.search.index.process.initial.InitialWorkFactory createCrawlingWork CLFRW0295E: Problem starting indexing for the ecm_files service, as part of the indexing task with the following details: ecm_files
                                 com.ibm.connections.search.common.registries.CrawlerUnavailableException: CLFRW0313E: Attempt by crawler registry to retrieve an unconfigured search service ecm_files. Check your Lotus-Connections-config.xml file.
....

Head scratcher!

I looked everywhere. The LotusConnections-config.xml file had the "ecm_files" set to be disabled.
The notification-config.xml also gave me nothing.

I reached out to the fabulous Skype team and it turned out that this was a weird issue.

Until Christoph Stoettner reached out and asked to get a copy of the LotusConnections-config.xml file.
He then discovered that the "ecm_files" service had the "ssl_enabled" set to true.

<sloc:serviceReference bootstrapHost="admin_replace" bootstrapPort="admin_replace" clusterName="" enabled="false" serviceName="ecm_files" ssl_enabled="true">

I also noticed this earlier, and never gave it a second thought!!

In all my years of working with IBM Connections, I always thought that when the service had "enabled=false" then that was it! I though that it did not matter if "ssl_enabled=true" was set, because I was under the impression that this only had to do with the fact that you are enabling the service to work over HTTPS.

Christoph proved me wrong! Lesson learned!!

After setting the "ssl_enabled=false" for the "ecm_files" in the LotusConnections-config.xml file, synced the nodes and restarted the News application, then the "Libraries" was gone from the list of notification options in the GUI.


And I also restarted the Search application, and now I don´t have any indexing errors in the SystemOut.log file neither.

The only documentation about this is in a version 3.0.1 infocenter HERE.

So, to sum up: enabled=true means that you enable the service for HTTP. ssl_enabled means that you enable it for HTTPS. Enabled=false does not mean that you disable the entire service!

Thanks Christoph, I owe you not one, but two beers!