Connections

Moving Databases from DB2 9.7 to 10.1 on Windows 2012 R2 for C3-C5 upgrade.

Those people familiar with Connections will be aware of the different methods to upgrade.   The one that we use most often, due to the fact that we have the best chance of success, is the Side-by-Side migration. With this migration you essentially setup a new environment at the version you are upgrading to , then transition your data. This also allows for the old version to be available to the customer, so that historical data is still available.

We mainly use DB2 for the backend, and most of time it’s hosted on a Linux machine. But some times we come across Windows.

This is one of those cases….

I ran into an issue with migration of DB2 servers between versions of DB2, hosts and Operating Systems.
Moving from DB2 9.7 on Windows 2003 – DB2 10.1 on Windows 2012 Server R2.

Firstly, here is an overview of the process used.

On source host – this requires an outage of your Connections host.
Login as DB2ADMIN to the local machine.
Databases are backed up offline using the DB2 Control Center.
Backups are to Files.
On Target DB2 server
Logged as Domain user
Copy from the Source host to the Target host the DB Backups.
Switch to DB2Admin
Restore Databases. Nice thing with this is that the databases were automagically upgraded to 10.1.

The issue

When the databases were restored to the DB2 10.1 server, DB2Admin on the Target machine did not get the correct permissions and authorities to the databases. Even though DB2Admin was explicitly listed in the Database, this did not help.
The problem propagated itself further in the DB2 Upgrade wizards in Connections. When the DBUpgrade wizard ran, it could not verify which Connections version the databases were. This leads me onto the next thing..how do the Connections wizards identify what Connections version the database is?
Each database has a specific table that holds what version of Connections the database is. Being IBM, having these be the same table would be way too easy. The Table for HOMEPAGE is HOMEPAGE.SCHEMA.

When viewing the data in HOMEPAGE.SCHEMA, as DB2Admin, nothing was returned, We could see no data.
DB2Admin had no rights to the database, which makes no sense.. DB2Admin should be god.

The fix

Was to perform the following on the Target DB2 Server before the restore.

Set the DB2_RESTORE_GRANT_ADMIN_AUTHORITIES registry variable BEFORE preforming the restore into a new database.
Example:

db2stop
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=ON
db2start

Just to automate the restore process, I created a batch script.

 D:
 db2 RESTORE DATABASE BLOGS USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022174241 TO "D:" INTO BLOGS WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE DOGEAR USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022174313 TO "D:" INTO DOGEAR WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE FILES USER db2admin USING 1password FROM "D:\DB2TFR2" TAKEN AT 20141022174401 TO "D:" INTO FILES WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE FORUM USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180225 TO "D:" INTO FORUM WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 REM db2 RESTORE DATABASE HOMEPAGE USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180322 TO "D:" INTO HOMEPAGE WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE OPNACT USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180348 TO "D:" INTO OPNACT WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE PEOPLEDB USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180432 TO "D:" INTO PEOPLEDB WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE SNCOMM USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180459 TO "D:" INTO SNCOMM WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1
 db2 RESTORE DATABASE WIKIS USER db2admin USING password FROM "D:\DB2TFR2" TAKEN AT 20141022180525 TO "D:" INTO WIKIS WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1

I performed the above, restored the Databases. This time the DB’s took considerable time to restore….more stuff happening! I was now able to successfully access the Databases, and run the wizards.
Happy days,

DB2 10.1 and Windows 2012 – A warning.

So I’m installing a new environment for a customer, doing what you usually do.

I go to this page to get the parts that required for my installation.

I download, then start installing.

So first up is my DB2 server. I kick off the install……

db2fail

I tried everything…nothing worked. I rechecked the environment.

Windows 2012 R2…there was my issue.

The install that comes with Connections for DB2 ESE  10.1 neat.

Support for R2 Windows 2012 was introduced in FP4. Download here

Hope this saves someone some time stuffing around!

 

SQL Locking on SNCOMM DB with MS SQL Server

Recently I was asked by a customer to investigate the poor performance of their Connections 4.5 CR3 environment. They had noticed that the servers were slowing, and eventually Connections ground to a halt.

Checking the System.out logs on the InfraCluster servers revealed that issues were being experienced with process hangs and process queuing.We then traced this back to the Databases, as it appeared like anything that relied on Communities were slowly failing. We found our problem.

The SNCOMM database had a lock from a long running task. Analysing the lock showed that this was the select statement it was running:

@P0 nvarchar(4000))   SELECT       TC.COMMUNITY_UUID as COMMUNITY_UUID,   TC.NAME as NAME,   TC.CREATED as CREATED,   TC.CREATED_BY as CREATED_BY,   TC.LASTMOD as LASTMOD,   TC.LASTMOD_BY as LASTMOD_BY,   TC.LOWER_NAME as LOWER_NAME,   TC.PLAIN_DESCR as PLAIN_DESCR,        '' as IMAGE,        '' as DESCRIPTION,   '' as DESCRIPTIONEX,       TC.MEMBER_COUNT as MEMBER_COUNT,      TC.PEOPLE_COUNT as PEOPLE_COUNT,       TC.GROUP_COUNT as GROUP_COUNT,   TC.COMMUNITY_THEME as COMMUNITY_THEME,       '' as CREATEDBY_DIRECTORY_UUID,   '' as CREATEDBY_DISPLAY,   '' as CREATEDBY_EMAIL,    '' as LASTMODBY_DIRECTORY_UUID,   '' as LASTMODBY_DISPLAY,   '' as LASTMODBY_EMAIL,     TC.COMMUNITY_TYPE as COMMUNITY_TYPE,    TC.HANDLE as HANDLE,     TC.PARENT_UUID as PARENT_UUID,   TC.ORG_ID AS ORG_ID,   TC.INTERNAL_ONLY AS INTERNAL_ONLY,   TC.OWNER AS OWNER,   TC.MEMBER_UPDATED as MEMBER_UPDATED,   TC.REF_UPDATED as REF_UPDATED,   TC.IMAGE_UPDATED as IMAGE_UPDATED,       TC.PRE_MODERATION as PRE_MODERATION,       TC.POST_MODERATION as POST_MODERATION   FROM   SNCOMM.COMMUNITY TC       WHERE      TC.COMMUNITY_UUID=@P0

Removing the lock resolved the issue, everything in the environment “trued” and all was well with the world again.

The customer then did some investigation into what changes were made in the environment. Turns out through some pretty awesome sleuth work that a new user had been brought on that day. This user was added to a Community that had >10 sub-communities. This was the clue..

In Connections 4.5 (unsure which version) a new feature to update the membership of sub-communities when a user was added to the parent community was introduced. Running this code for under 10 sub-communities, no problem. More than 10; LOCK.

PMR was logged and the resulting investigation showed that this was a reproducible error. Bad news is that it also exists in version 5, but this will be resolved in an upcoming fixpack.

 

Increasing the library file size of a Community.

Just had to update the file size of a Community. Sounds easy right??

I swear IBM make this stuff as complex as possible…..

To increase the size limit of a Community, you need it’s UID usually found as the last part in the url.

https://blaf.com./communities/service/html/communityview?communityUuid=2672ad0a-9646-4c2f-8094-3b552322d79e

 But what happens if you aren’t a member of the Community and can’t access it? What happens if all you have is the name?

You need to do this:

Start wsadmin

Launch Communities administration:

>execfile("communitiesAdmin.py")

Then you need to get all communities, and find the name. This is done by:
wsadmin> all=CommunitiesService.fetchAllComm()
wsadmin> CommunitiesListService.filterListByName(all, "community name") 

You will get output like the following:

[{lastModBy=[John User, A6F3CFCB-E9F5-3CEE-CA25-6F1000815F04], created=6/2/13 4:38:17 PM EST, tags=[], type=private, name=APACBlah, uuid=2672ad0a-9646-4c2f-8094-3b552322d79e, memberSize=36, lastMod=10/22/13 2:25:31 PM EST, description= This is a subcommunity for APAC staff who have completed their initial launch training for . , createdBy=[John User, A6F3CFCB-E9F5-3CEE-CA25-6F1000815F04]}]
 You now have the UUID.. but now you need to find the corresponding Library ID. This is done in the Files Admin tool.

Launch files admin,

wsadmin>execfile("filesAdmin.py")
Files Administration initialized.

We then need to find the ID of the corresponding Library. This is found by issuing the following command,

wsadmin>FilesLibraryService.getByExternalContainerId("2672ad0a-9646-4c2f-8094-3b552322d79e")
APAC staff who have completed their initial launch training for the project., createDate=Sun Jun 02 16:38:18 EST 2013, policyId=00000000-0000-0000-0000-000000000001,
externalContainerId=2672ad0a-9646-4c2f-8094-3b552322d79e, themeName=default, label=We251c6a45e07_482a_b5c7_a13bbb0654ca, title=APACBlah,
ownerUserId=00000000-0000-0000-0000-000000000000, type=community, id=9e1b8069-a132-4ea6-a32c-948634a520cc, externalInstanceId=We251c6a45e07_482a_b5c7_a13bbb0654ca, lastUpdate=Mon Oct 21 17:08:37 EST 2013}

We then need to create the new Policy

Create a new policy, i.e we need to create a policy for 1 Gb of files in the community.

Use the following command. Anything over 2 GB needs an L at the end. format of (name, size)

wsadmin>FilesPolicyService.add("1 GB", 1073741824) 

A policy will be created, and an id given. Take note of this ID.

A policy was added with the new id 82e5ae13-efa7-4cc7-bb37-1b8d68b521cc

Now you need to apply the new policy to the Community. Format of command is (ID,Policy)

 wsadmin>FilesLibraryService.assignPolicy("9e1b8069-a132-4ea6-a32c-948634a520cc","82e5ae13-efa7-4cc7-bb37-1b8d68b521cc")
The policy with the id 82e5ae13-efa7-4cc7-bb37-1b8d68b521cc is now assigned to the library with the id 9e1b8069-a132-4ea6-a32c-948634a520cc .

 Simple hey????

A tick box on the Community administration page would kill you?

OAuth2 error in Activity stream popout

I’ve just finished with installing IBM Docs; an install post will follow shortly.

Users were reporting issues of the Activity stream popout failing with “An error has occurred”

Selecting more details, reveals the following.

000002a7 BasicOAuth2Re W BasicOAuth2Request exception
java.lang.NullPointerException
at java.util.zip.InflaterInputStream.<init>(InflaterInputStream.java:77)
at java.util.zip.GZIPInputStream.<init>(GZIPInputStream.java:65)
at java.util.zip.GZIPInputStream.<init>(GZIPInputStream.java:76)

I then backtraced the changes we had made. As the issue referred to GZIP, I thought of compression and what changes I’d made in the installation of Docs.

One step of this installation is to modify the httpd.conf file on the IBM HTTP Server to enable compression for various data streams. This is done by invoking the mod_deflate directive.

The main winners when this form of compression is enabled is the end user on a low bandwidth connection. Users on a local LAN the benefits aren’t as drastic, but it’s still a good idea.

For example, text can be compressed by up to 70%. That baeing said, some types of data don’t lend well to compression, i.e jpg, pdf, etc.

Read here for IBM’s technote on enabling mod_deflate.

Anyway, back to my error.  I had added the following  changes to our httpd.conf file as part of the docs install. Please note these are all commented out.

#AddOutputFilterByType DEFLATE application/atom+xml 
  #AddOutputFilterByType DEFLATE application/atomcat+xml 
  #AddOutputFilterByType DEFLATE application/javascript 
  #AddOutputFilterByType DEFLATE application/json 
  #AddOutputFilterByType DEFLATE application/octet-stream 
  #AddOutputFilterByType DEFLATE application/xhtml+xml 
  #AddOutputFilterByType DEFLATE text/css 
  #AddOutputFilterByType DEFLATE text/plain 
  #AddOutputFilterByType DEFLATE text/xml 
  #AddOutputFilterByType DEFLATE text/xsl

By a method of deduction, we worked out that the “application/octet-stream” was the culprit for killing the Activity stream popout. We’ve disabled this entry, and all works again.

Hope this saves someone half a day of stuffing around!

Updating Connections 5 WAS to 8.5.5.3

Connections 5 runs on WebSphere Application Server 8.5.x.

We recently deployed on the 8.5.5.2 release, but as part of keeping the WAS stack as up to date as possible, I updated to the recent release 8.5.5.3. We were experiencing an error that is resolved in the update.

Details on this fix are located here

Just to save time, if you are going to do the same, uninstall fix IFP119108, or you’ll get the error below when you go to update using IIM.

WASError

Hope this saves time for someone out there.

EDIT…It appears that IBM have decided not to support Connections on this version of WAS.

So if you need to update to resolve an issue in the WAS stack, beware…