Some issues with Migration of IBM Connections 5.0 to 5.5

I had some new issues while migrating a customer environment from IBM Connections 5.0 to 5.5 (Oracle) and I would like to document them here:

ORA-01722: invalid number during Homepage upgrade

 

While running the script “homepage/oracle/upgrade-50CR4-55.sql” we got the following errors in the log file:

The reason for that was, that the sequence of the columns in the table “HOMPAGE.NR_DISCOVERY_VIEW” was different from the sequence of the columns when you create that table from scratch. I guess it was because this table has been upgraded already many times (since IBM Connections 3.0). We had another (test) environment where the sequence of the columns were correct (but the database of that environment has been created from scratch somewhere in Connections 4.5 or so).

The SQL script does read the content of a database row into an array and then uses this array to insert the data to another table. While inserting the data via an array, the target table needs to have exactly the same order of the columns in order to insert the values into the right column.

To solve the issue I changed the following statement from

to

which then solved the issue.

No migrated entries in “Discover” view of the Homepage

 

After the migration, the “Discover” view on the Homepage was empty. Only new entries appeared but all entries from before the migration were not displayed. I solved that by changing the same SELECT statement mentioned above from

to

The old statement writes the value “23” to that column in the target table, regardless of the original value. The original value of all entries in our case was “17”. So we just removed the “23” in front of the column name so that the correct values were written to the target table. Afterwards the “Discover” view has been populated correctly with the values from before the migration.

Additional Homepage java migration necessary for 5.0 to 5.5

 

We also realized that a Java migration for Homepage is not only necessary while migrating from 4.5 to 5.0 but also while migrating from 5.0 to 5.5. This is not currently mentioned in the documentation. There is one hint here, but the description in this chapter is currently not correct and should not be used that way. The correct way to do the Java migration from 5.0 to 5.5 for Homepage including the missing JAR file can be found in that technote.

Be aware of the fact that the parameter for JDBC URL, DB User and DB password do not have a prefix like “-dburl / -dbuser / -dbpassword” but just written without that. This is different from the Java migration for version 4.5 to 5.0. And also make sure you got the newest version of that JAR file in the technote. It has been updated January 11th, 2017 with a new version (fixed bugs for Oracle).

Wrong documentation which scripts should be run for Homepage migration

 

The documentation has currently a wrong description which SQL scripts you should run for upgrading Homepage if your DB schema version is “479”. The documentation currently says:

but it should read:

I requested to update the documentation with all the issues found, so hopefully in future the documentation will be correct again.

Create tasks in “Remember The Milk” with Siri and the Apple Watch

I am a long year user of “Remember The Milk” (RTM) to manage all my tasks. Although they do still not have a native app on the iPhone, you are able to use Siri on the Apple Watch to automatically create a task in RTM without touching your iPhone.

This is really something I am using very often throughout the day. Especially as Audi has killed Siri in my A3 if the iPhone is connected via Bluetooth to the car system. Siri on the watch is still working like a charm.

Here is described how to enable Siri with RTM on the iPhone.

IBM Connections 5.5 CR2 available

Since last week, IBM Connections 5.5 CR2 is available. Here are some important links:

Download IBM Connections 5.5 CR2

Download IBM Connections 5.5 CR2 Database Update Scripts

IBM Connections 5.5 CR2 Fix List

Updating IBM Connections 5.5

Update Strategy for IBM Connections 5.5

IBM Connections 5.5 CR requirements for IBM FileNet for use with Connections Content Manager (CCM) incl.uding Download Links

IBM Community Surveys 8.5/8.6: Updating the Sonata services for IBM Connections 5.5 CR2

IBM Cognos Wizard for IBM Connections 5.5 Cumulative Refreshes (CRs)

Step-by-step example of applying Component Refresh 2 (CR2) to IBM Connections 5.5 with CCM

 

 

 

 

Publishing PGP Keys in DNS

As I now have secured my DNS server with DNSSEC, I was able to publish my public  PGP key also via DNS. There are two different possibilities to do that:

PKA (public key association)

This puts a pointer where to obtain a key into a TXT record. At the same time that can be used to verify that a key belongs to a mail address. You can find more about that here (only in German).

My DNS TXT record looks like that:

The part before “._pka.” is the local part of my mail address (“michael”) and the part after “._pka.” is the domain name “urspringer.de”. The “v=pka;” specifies the version of PKA (currently V1). The value “fpr=7F3F203B94F85C3B7969BF58C5F5860FF6160414” is the finger print of my PGP key. The value “uri=http://www.urspringer.de/media/Michael_Urspringer.asc” specifies the URL where my PGP public key can be downloaded.

You can test if it is working e.g. with gpg like that:

 

OPENPGPKEY

With an OPENPGPKEY resource record the complete key is stored in DNS. You need to have a Bind9 version of  9.9.7 or  9.10.2 and newer.

The name under which the record is located is built up as described below:

  • the local part of the email address associated with the key, hashed with the SHA2-256 hash function and truncated to the first 28 octets;
  • the _openpgpkey label;
  • the domain part of the email address.
  • The record data is the PGP public key

It looks something like that:

To create that record you can use the tool on this website. Just put in your PGP public key, select “Standard (OPENPGPKEY)” as output format and click on “Generate”.

You can test if it is working with OPENPGPKEY.info.

 

Debian Jessie: Upgrade BIND9 to newer version

I wanted to update BIND9 on my Debain Jessie Linux system to a newer version than the currently packaged version 9.9. Here are the steps to upgrade it to version 9.11:

This will install the new version of BIND9 to “/usr/local/sbin”. So you can have installed the new version of BIND9 in parallel to the packaged version of Debian Jessie (which resides in “/usr/sbin”). So in case of problems you can always go back to the original BIND9 version.

You now need to modify the start script of BIND9 so that thew new version will be used. You can do that with the following steps:

  1. Stop BIND9 with “service bind9 stop”
  2. Make a backup of the original start script: “cp /etc/init.d/bind9 /etc/init.d/bind9.ORG”
  3. Open “/etc/init.d/bind9” in an editor
  4.  Add the path “/usr/local/sbin” at the end of the path statement so that it looks like “PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin”
  5. Replace all “/usr/sbin/named” to “/usr/local/sbin/named” and all “/usr/sbin/rndc” to /usr/local/sbin/rndc”.
  6. Make a backup of the original service definition: “cp /lib/systemd/system/bind9.service /lib/systemd/system/bind9.service.ORG”
  7. Open “/lib/systemd/system/bind9.service” in an editor
  8. Replace all “/usr/sbin” with “/usr/local/sbin”
  9. Run the following commands:
    ln -s /etc/bind/named.conf /etc/named.conf
    ln -s /etc/bind/rndc.key /etc/rndc.key
  10. Start BIND9 again with “service bind9 start”

Now BIND9 should run with the new version.

Technical Changes Behind the Scene …

Long time ago since I posted something here. The reason for that is, that I did some massive technical changes in the background.

First, all my stuff is now running on a plain Linux virtual server (based on Debian Jessie) instead of a Linux server manged via Plesk. It was simply too much overhead and very hard to figure out where to do changes which will not be overwritten by Plesk later again. So adding new stuff or configuring existing things is much easier now.

I also improved my internal mail system. In the past I used already my own mail server but forwarded all mails to Google Mail as I really like the Gmail front end and especially the archive and search functionality. Antispam had also been outsourced to Spamfence. Both worked very well for me but all my mails were sent and stored to third party providers. So changed that and now everything is done on my own server. I reconfigured Postfix so that most of the spamming attempts is now already blocked on the SMTP level and never reaches my system. The very few spammers, who are successful in delivering mails, are filtered out by SpamAssassin which now works very well. Same is true for dangerous mails like viruses, executables etc. I added also support for SPF and DKIM both for incoming and outgoing mails

I installed Dovecot as IMAP server together with Sieve for mail filtering and as front end I am now using Roundcube. I like its user interface and it has built-in support for PGP encryption already. I added all my mails since 2013 from Google to Dovecot so that I can do at least some basic searching.

If I really need some more sophisticated search features I have all mails archived to my local computer via MailStore Home. I tried to implement Solr to search within my IMAP account but that was not yet successful as it always chrashes while searching virtual folders. So there is still something to improve in future 🙂

I am now also hosting my own DNS server, which was in the past something my provider did for me. The main reason for that is, that I wanted to implement DNSSEC (secure DNS) together with DANE for verifiying my SSL certificates and my provider was not yet supporting DNSSEC. So I switched all my domains to INWX and installed my own DNS server which now work as the primary and the INWX servers work as secondary.

In addition, I needed to switch all my SSL certificates from StartSSL to something else because of  a security issue. As I needed something where I can get several certificates with no or very low cost I have choosen LetsEncrypt as my new SSL provider.

This works pretty well (I am using this script as basis) and has only one disadvantage: All certificates need to be refreshed every 90 days. Although this normally is not really a problem it could lead to problems because all my certificates are now protected by DANE and the certificate’s fingerprint needs to be updated in my DNS as soon as the certificate changes. So this will still be a challenge in the next weeks.

Last but not least, I decided to have a backup server for SMTP and DNS so I needed to built and configure that as well. My main hosting provider is still Host Europe (I have very good experience with them in the last years) but for a backup server (which is not really needed ;-)) it was to expensive. So my backup server is a small virtual machine hosted by IP Interactive, a small provider near by my home.

To be able to monitor all that stuff, I added all servers and services to my Icinga monitoring system on my Raspberry Pi which now monitors over 22 hosts and almost 80 services.

All servers capable of sending SYSLOG messages were consolidated to a central SYSLOG server also hosted on my Raspberry Pi with Loganalyzer as front end.

Doing all that was quite some work but I have again learned very much about Linux, SMTP, DNS and other stuff. So it was worth the effort. But I guess that now explains that I did not have much time to write blog entries but hopefully this will now change again … 🙂

Some links which helped me in configuring some of these things:

https://vpsineu.com/blog/how-to-setup-and-configure-a-master-dns-bind-server-in-debian-wheezyjessie/
http://blog.mansshardt.net/bind9-dns-server-einrichten-unter-debian/
https://www.digitalocean.com/community/tutorials/how-to-configure-bind-as-an-authoritative-only-dns-server-on-ubuntu-14-04

https://www.digitalocean.com/community/tutorials/how-to-setup-dnssec-on-an-authoritative-bind-dns-server–2
https://t37.net/no-more-expired-dnssec-zones-with-bind-9-9-inline-signing.html

https://www.linode.com/docs/email/running-a-mail-server
https://www.linode.com/docs/email/postfix/email-with-postfix-dovecot-and-mysql
https://www.debinux.de/2015/05/mailserver-from-scratch-debian-8/
https://www.skelleton.net/2015/03/21/how-to-eliminate-spam-and-protect-your-name-with-dmarc/

 

IBM Connections: Find out which Communities do have a Sharepoint widget added

A customer would like to know which IBM Connections Communities have a Sharepoint widget added. The following SQL statement should give the UUID and the name of these Communities:

The customer also wanted to send a mail to all creators of  these Communities. So he wanted to get a list of names and mail adresses of them. The following SQL statement creates that list:

As the original creator of a Community might no longer be in the company, it would be better to get a list of all Community owners who do have at least one Community with a Sharepoint widget added. You get that list with this statement:

You can use the same statements to find out about other widgets. Just replace “SharePointFiles” with the widget id of your choice.

The statements do work for ORACLE and you may need to adapt them for DB2.

IBM HTTP Server: Better logrotate for HTTP server logs in Linux

To rotate your IBM HTTP Server logs (or Apache HTTP server logs) on a daily (or whatever) basis, you normally are using something like

The disadvantage of that is, that the current log file name changes every day. So if you have e.g. a log viewer open to watch the log file, you need to adapt that file name  every day.

A better solution for that is a tool called “cronolog“. It can be used in a similar way like

It then creates a directory with the date as the name and places the log file of that day inside that directory. In addition it creates a symbolic link with the original name (“access_log”) and also a symbolic name to the log file of the day before (“access_log_before”).

Both file names will not change and therefore can be monitored without changing anything in the log viewer.

The tool should be part of all Linux distributions and can be installed e.g. with “yum -y install cronolog” (for  Redhat; use the package install command for your distribution instead).

IBM Websphere: Use arrow key with wsadmin in Linux

Other than in Windows you cannot use the arrow keys to correct a command line of recall your last command with IBM Websphere wsadmin command line in Linux. In order t get that working you need a little tool called “rlwrap“.

Just install it with “yum -y install rlwrap” (for Redhat Linux; choose the install command valid for your distribution) and then you can run wsadmin with ” rlwrap ./wsadmin.sh”.

I normally create a short command file with the following content and place it in “/home/<user>/bin” so that I can call it from everywhere: