Connections 6 migration observations

I am sure that lots of you have installed or started migrations to Connections 6 by now .. but I have found a couple of gotchas across the installs and updates that I have managed so far.

I am listing them here for sanities sake as I know when we google an error – this blog will show up ūüôā

 

Connections 6 itself

 

Generally this was a nice smooth install and all worked as it should in a clean 6 environment

On testing a migration though I found issues when trying to migrate some of the DBs.

After dropping and restoring the DBs some were not updating to the latest DB2 version – this is resolved by running the update DB command

db2 upgrade db <DBNAME>

this upgrades the DB to the latest DB2 version – I run this after each restore command now to ensure the DB does update

for example

db2 restore db BLOGS from E:\install\V55_Backup\db2_backup taken at 20170718123342 ON E:\ into BLOGS
db2 upgrade db BLOGS

 

The metrics DB would not restore as it was complaining about heap size – after a quick google on the error I found a page suggesting just to change the application heap size using the following command

db2 UPDATE DB CFG FOR METRICS USING APPLHEAPSZ 4000

This resolved the problem and the metrics DB could be restored

 

I have also seen issues with the Connections DB update wizard when the databases have a lot of data. In a mature environment I generally run through the wizard and save the update commands to a text file. Then execute them manually from the db2 command window – for example

Activities
 1. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\activities\db2\upgrade-55CR2-60.sql
 2. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\activities\db2\appGrants.sql
 3. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\activities\db2\reorg.sql
 4. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\activities\db2\runstats.sql

Blogs
 1. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\blogs\db2\upgrade-55-60.sql
 2. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\blogs\db2\appGrants.sql
 3. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\blogs\db2\reorg.sql
 4. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\blogs\db2\runstats.sql

Bookmarks
 1. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\dogear\db2\upgrade-55-60.sql
 2. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\dogear\db2\appGrants.sql
 3. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\dogear\db2\reorg.sql
 4. E:\IBM\SQLLIB\bin\db2cmd -c -w -i db2 -td@ -vf connections.sql\dogear\db2\runstats.sql

.................. ETC.

For some reason if you have large DBs the wizard struggles massively. Running the commands manually from the command window also allows you to dump each commands logs out into a text file if you need to. Great for debugging and used a LOT when I ran through the AIX Oracle to Windows DB2 migration last year.

 

Once the DBs were restored and updated run the reorg and runstats against each of the DBs before you start it up and if you can also run the clearScheduler.sql for each DB.

That should sort out the majority of DB issues you may see during the update.

 

Connections TouchPoint

 

Again a fairly easy process once you work out what the documentation is talking about – things of note

The document speaks of copying the touchpoint folder contents to the htdocs directory –

 

 

 

 

 

 

you have to copy the entire folder – for example

Copy files from E:\install\Connections\Touchpoint\touchpoint to E:\IBM\HTTPServer\htdocs\touchpoint the wording in the documentation doesn’t really make that clear.

All of the paths etc in the documenation are case sensitive.

Another thing of note which has tripped me up twice now so I have to blog it (thank you Ben for the extra eyes on this one) is the contents of the touchpoint.deploy.properties

The touchpoint.deploy.properties which is included in the install files is different from the example the documentation gives, we basically mix the two and have the contents of the file looking like this

# when install to a clustered environment, provide clusterName.
# when install to a standalone server, provide node and server name.
clusterName=apps
#nodeName=icbvtDB2Node01
#serverName=server1

# setting required REE custom properties
ree.prop.image.upload.path=E:/IBM/CnxData/shared/touchpoint/upload_pic
ree.prop.profiles.app.entrypoint.host=connections.url.com
ree.prop.profiles.app.entrypoint.scheme=https
ree.prop.profiles.app.entrypoint.port=443

NOTE:¬† If using windows the image upload path must have / the linux way not \ the windows way. It will fail if you don’t even if you have it in “E:\IBM\etc…” it appears to be a java / python thing and the error will complain that it can not pass the properties file if the slashes are not /

 

IBM Docs CR2

 

To use IBM Docs with Connections 6 you must update to CR2 for Docs – that is fine (and see previous blog posts about issues with applying the CR2 fix).

There is an awesome gotcha that my good friend Roberto pointed me in the direction of the fix for – this one was a good one (thank you Marti for writing the blog)

The symptoms are Docs editing works fine, but when attempting to view a file the error : CLFAF400W: Can’t access the document repository appears

Its a super quick fix – edit the viewer-config.json which can be found in the (i.e \IBM\WebSphere\AppServer\profiles\Dmgr01\config\cells\<cell name>\IBMDocs-config)

Under the section

"components": [
 {
 "config": {
 "uploadRepository": "lcfiles",

find the line

 "class" : "com.ibm.concord.viewer.lc3.repository.LCFilesEJBRepository",

and replace it with

 "class": "com.ibm.concord.viewer.lc3.repository.LCFilesCMISRepository"

Just above that you can see the files_path

 "files_path": "E://IBM//CnxData//shared/files/upload"

Add the additional info to that section

 "j2c_alias": "connectionsAdmin", 
 "s2s_method": "j2c_alias", 
 "server_url": "https://connections.url.com/files",
 "files_path": "E://IBM//CnxData//shared/files/upload"

So the full section will now look like this:

 "components": [
 {
 "config": {
 "uploadRepository": "lcfiles", 
 "adapters": [
 {
 "config": {
 "j2c_alias": "connectionsAdmin", 
 "s2s_method": "j2c_alias", 
 "server_url": "https://connections.url.com/files",
 "files_path": "E://IBM//CnxData//shared/files/upload"
 }, 
 "id": "lcfiles", 
 "class": "com.ibm.concord.viewer.lc3.repository.LCFilesCMISRepository"
 },

 

Save the file and do a full sync once a restart of the viewer app is done the viewer will now work.

 

One other thing of note when it comes to migrating the Docs data and DBs is that almost every time you do a migration the wasadmin user will be out of sync and you will see duplicate user issues in the log.

easily resolved by running the syncMember command from the DMGR\bin directory

wsadmin.bat -lang jython

execfile("filesAdmin.py")

FilesMemberService.syncMemberExtIdByLogin("wasadmin")

 

Other things you should do once the docs data has been migrated is to run the generate thumbnails command (also from wsadmin in the filesAdmin section)

wsadmin.bat -lang jython

execfile("filesAdmin.py")

FilesThumbnailService.generateForAllFiles()

 

Also to migrate any drafts from the docs installer directory i.e

wsadmin.bat -lang jython -f E:/IBM/ConnectionsDocs/Docs/installer/docs/tasks/start_migration_tool.py

 

Hopefully you won’t come across any or more than one of these issues, but if you do hopefully there will be a quick resolution.

If I find anything else I will be sure to blog them.

Migrating Connections DB from Oracle to DB2 part 3

In part 3 of the series we will actually attempt to migrate the data.

 

Migrating Connections DB from Oracle to DB2

In my experience the database migration is always most time consuming, so I always do the database first

Use the text file of Commands that we created in part 2.

  • Back up the exisitng Connections databases
  • Drop database, create database, app grants (for homepage also initdata, and re org and run stats)
  • CR update scripts
  • Pre DB fixer script
  • Run the DBT command
  • Application specific scripts (IBM provided addional fixup scripts for Blogs, Files and Wikis for me)
  • Post DB fixer script
  • Re org script
  • Run stats script

I run the commands one database at a time. This made it much easier for troubleshooting issues and ensuring that each database was in a consistent migrated state before moving onto the next – if I had issues I made notes in the commands text file and moved on to the next DB.

Homepage took a very long time to migrate as there was a LOT of data here, the other DBs I had issues with were Files, Wikis and Blogs which are the ones that IBM expect to cause issues. I will use Blogs as an example on how to troubleshoot later.

I also had some DBs that were already using DB2 like IBM Docs, Surveys app for Communities and ProjectExec – we backed these up also and migrated them across to the server in the sameway that we would do a normal DB migration – Droped, restored and checked the DB roles and permission. These all came across with no issues.

 

Troubleshooting

It goes without saying that you should ALWAYS RUN THIS ON A TEST / DEV SERVER FIRST!

If i could have this as scrolling flashing marquee text with it playing a siren I would. I CAN NOT emphasise enough how important it is that you should run this in a test environment – which we did 3 times before actually doing it for real.

The most troubleshooting I did on this project revolved around the dbt transfer script (see example below).

The database schemas are quite different between types and even between OS versions so there will be a lot of tinkering to resolve the problems. Typically its where one DB type expects a NULL and the other expects a NOTNULL

Firstly run the script

java -cp D:\IBM\Connections\ConfigEngine\lib\DBT_HOME\dbt.jar;D:\IBM\Oracle\ojdbc6.jar;D:\IBM\SQLLIB\java\db2jcc.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir D:\IBM\Connections\ConfigEngine\lib\DBT_HOME\logs -xmlfile D:\IBM\Connections\ConfigEngine\lib\DBT_HOME\files\activities.xml -sourcepassword sourcepassword -targetpassword targetpassword

If there are problems it will be shown in the console and written out to a log file.

for example you may see an error like this:

[07/20/16 13:22:56.756 PDT] com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #50: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=3, TABLEID=5, COLNO=1, DRIVER=3.65.110

All of these issues I saw related to the NULL / NOTNULL issue.

The next step is to make a note of the tablespace id, table id and column number as we need those for an sql query.

Fire up your DB client and run the following query

 

SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME
 FROM SYSCAT.TABLES AS T, SYSCAT.COLUMNS AS C
 WHERE T.TBSPACEID = 3 AND T.TABLEID = 5 AND C.COLNO = 1 AND C.TABSCHEMA = T.TABSCHEMA AND C.TABNAME = T.TABNAME

Use the TBSPACEID, TABLEID & COLNO from the error message

The query will report back the DBName (as the TABSCHEMA), the table name (as TABNAME) and the column name (as COLNAME)

 TABSCHEMA     TABNAME             COLNAME
 ---------     ---------------     --------
 BLOGS        ROLLERLOGINNAME        USERNAME

Now you have the name of table and column with the null/not null issue.

The next step is to edit the createDB.sql script to make the change in the example here remove the not null from the username field as below.

create table BLOGS.rollerloginname (
    userid             varchar(48) not null,
    username           varchar(255),
    ORG_ID             varchar(36) not null
) IN BLOGSREGTABSPACE@

 

Once that change has been made the process needs to start again…

 

  • Drop database, create database, app grants (for homepage also initdata, and re org and run stats)
  • CR update scripts
  • Pre DB fixer script
  • Run the DBT command

Rinse and repeat until the DB transfers all the tables over correctly. This part of the process took the longest. Working out which tables to remove the not nulls from was SO time consuming. I kept a record of all the tables / columns I changed for my records more than anything else, at least the Post DB fixer script puts things back to the way it should be – and if you do hit issues with that you can manually fix them as you have a record.

 

ALTER TABLE BLOGS.ROLLERLOGINNAME ALTER COLUMN USERNAME SET NOT NULL;

 

So hurrah .. after all of that mucking about we have the Connections 5 data from Oracle on AIX in to DB2 on Windows – now what?

Well I backed it ALL up, including the existing V5 DB2 databases Docs, FEB and Projexec and migrated it to Version 5.5.

On the whole this part of the process wasn’t too bad and it was a good test for a real live migration ..

 

Migrating to Live

The whole migrating to live took about 21 hours in total – including waiting time for the DBs to transfer and the steps were are follows

On the Live5 machines

  • Stop Connections
  • Backup the File system
  • Back up the DB2 databases that need migrating (FEB, Docs, Projexec)

On the Dev5 DB machine (that I had been using for testing)

  • Drop database, create database, app grants (for homepage also initdata, and re org and run stats)
  • CR update scripts
  • Pre DB fixer script
  • Run the DBT command
  • Application specific scripts (IBM provided addional fixup scripts for Blogs, Files and Wikis for me)
  • Post DB fixer script
  • Re org script
  • Run stats script
  • Restore the backed up Live DB2 databases (FEB, Docs, Projexec)
  • Backup the DBs – so we now have a FULL set of V5 DB2 data
  • Copy backups to LIVE 55 DB2 machine

On the Live5.5 machines

 

  • Stop Connections
  • Drop the DB2 databases
  • Restore the V5 DB2 databases
  • Set numb dbs to at least 25
  • Run the upgrade scripts to update them to V5.5 – ran these manually not using the GUI as the amount of data caused issues
  • Run the CR updates
  • Run the Docs DB2 Fixes
  • Run the FEB DB fixer from the FEB admin GUI
  • Started Connections
  • Cleared the scheduled tasks
  • Post migration steps
  • Restarted Connections

And we were live ūüôā

This took WAY longer than it should have done because of all the issues we had with the DBT to begin with .. but hopefully info in these posts will hope anyone who needs to do this pre PINK.

This was a LONG journey – but we all learned a lot in the process of doing this .. the main lesson learnt was don’t attempt this unless you HAVE to ūüôā

If you missed the other 2 parts of this series you can find them here:

  • Part 1
  • Part 2
  • Part 3 – this page you are reading ūüôā

 

 

Migrating Connections DB from Oracle to DB2 part 2

In part 2 of this series we will cover preparing the system and the commands and files required.

There is a fair bit of file creation to do and configuring of the Database Transfer Tool (DBT)

 

Migrating Connections DB from Oracle to DB2

Step 3 – Prepare a system to recieve the data

As I stated previously do not think you can recycle an existing Connections server if it has been installed with the DB type you are migrating away from as it causes issues with files particually.

In my case I had an existing V5 dev system using the test oracle DB. I backed it all up – Existing DB2 databases, Docs configuration files, file system (connections shared data) and docs/viewer shares – we will need this later.

Blow that system away and rebuild a clean, fresh V5 system – should only take a few hours ūüôā

Once you have a clean V5 system – make sure it all works .. then back up the empty DBs and filesystem files.

Its essential that you know the system works before attempting to migrate data.

Step 4 – Prepare files and commands

4.1 Prepare the Database Transfer Tool

Create the DBT_HOME directory

On the db2 server create the DBT_HOME directory under <CONNECTIONS_ROOT>\ConfigEngine\lib

On my system this was D:\IBM\Connections\ConfigEngine\lib\DBT_HOME

Under DBT_HOME create additional directories of files to hold the xml files required for migration and logs for all logs relating to the migration

Locate the dbt.jar from the Connections machine (<CONNECTIONS_ROOT>\ConfigEngine\lib in my case  D:\IBM\Connections\ConfigEngine\lib)  and copy it to the newly created DBT_HOME directory on the DB2 server.

 

Create the XML files required

Create an XML document for each application DB that is to be copied over and place it in the the DBT_HOME\files folder

D:\IBM\Connections\ConfigEngine\lib\DBT_HOME\files

Example below of the activites.xml :

<dbTransfer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database role="source"
driver="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@oracledbhost:port:dbinstancename"
userId="OAUSER"
schema="ACTIVITIES"
dbType="oracle"/>
<database role="target"
driver="com.ibm.db2.jcc.DB2Driver"
url="jdbc:db2://db2host:50000/opnact"
userId="db2admin"
schema="ACTIVITIES"
dbType="DB2"/>
<table sourceName="activities.system_lastmod" exclude="true"/>
</dbTransfer>

 

The source in this example is is oracle the target is db2

Once completed you will have the follwing xml documents in the DBT_HOME\files directory

  • activities.xml
  • blogs.xml
  • dogear.xml
  • files.xml
  • forum.xml
  • homepage.xml
  • metrics.xml
  • mobile.xml
  • people.xml
  • sncom.xml
  • sncomcal.xml
  • wikis.xml

Ensure the DB drivers are accessible

It is essential that the oracle drivers are accessible by the DB2 (target) server.

Oracle driver & DB2 driver paths – we need these later.

D:\IBM\Oracle\ojdbc6.jar
D:\IBM\SQLLIB\java\db2jcc.jar

 

V5 & V5.5 Wizards are required

When building the commands to migrate the DB some scripts are missing from the V5 Wizards directory. After speaking with IBM its safe to use the V5.5 wizard files for the missing scripts.

 

Replace the connections.sql scripts

Replace the SQL scripts given to you by IBM into the relevant directories .. in my case they were for files, wikis and blogs

 

4.2 Prepare the commands required

Prepare the commands for each DB  they will comprise of the following:

drop database, create database, app grants (for homepage also initdata, and re org and run stats)

CR update scripts

Pre DB fixer script

the DBT command string which has the following syntax

java -cp <path to DBT home>\DBT_HOME\dbt.jar;<Oracle driver>\ojdbc6.jar;<db2 driver>\db2jcc.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir <path to DBT home\logs>\DBT_HOME\logs -xmlfile <path to DBT home\files>\DBT_HOME\files\<application xml>.xml -sourcepassword sourcepassword -targetpassword targetpassword

Application specific scripts (IBM provided addional fixup scripts for Blogs, Files and Wikis for me)

Post DB fixer script

Re org script

Run stats script

 

== Activities Example==

cd C:\InstallTemp\Connections\V5\Wizards\connections.sql\activities\db2

db2 -td@ -vf dropDb.sql

db2 -td@ -vf createDb.sql

db2 -td@ -vf appGrants.sql

cd C:\InstallTemp\Connections\V5\CR3\50cr3-database-updates\50cr3-database-updates\From-50\db2\

db2 -td@ -vf 50-CR1-activities-db2.sql

cd C:\InstallTemp\Connections\V55\connections.sql\activities\db2

db2 -tvf predbxfer50.sql

cd C:\InstallTemp\Connections\V5\Wizards\jvm\win\jre\bin

java -cp D:\IBM\Connections\ConfigEngine\lib\DBT_HOME\dbt.jar;D:\IBM\Oracle\ojdbc6.jar;D:\IBM\SQLLIB\java\db2jcc.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir D:\IBM\Connections\ConfigEngine\lib\DBT_HOME\logs -xmlfile D:\IBM\Connections\ConfigEngine\lib\DBT_HOME\files\activities.xml -sourcepassword sourcepassword -targetpassword targetpassword


cd C:\InstallTemp\Connections\V55\connections.sql\activities\db2

db2 -tvf postdbxfer50.sql

cd C:\InstallTemp\Connections\V5\Wizards\connections.sql\activities\db2

db2 -td@ -vf reorg.sql
db2 -td@ -vf runstats.sql

note: The RED section of the example script above should be run in a standard command window. The other scripts should be run from a db2cmd window.

 

I have a long list of commands in a text file that contains one of these blocks for each application a example of this can be downloaded here to use as a template for the syntax – note: that you should use this at your own risk, take advice from IBM and ensure that all the paths and passwords are set correctly before running.

One of the main reasons for dropping and creating manually is that the gui db wizards seem to have real issues – unsure why, but when we ran the db scripts manually they worked no issue – also there is an advantage that if we do see problems the script logs can be piped to a specific file. This was very helpful with all of the troubleshooting we had to do. If you do see issues with any of the commands they can be piped to a log to send to IBM – for example:

db2 -td@ -vf createDb.sql > C:\Install\temp\activities_createDb.txt

This was a lifesaver / timesaver for troubleshooting any db commands – as each command can be output to easily isolate what the issue is.

 

Step 5 – BACKUP

Before we start to do anything  we are going to make some backups. Stop the existing V5 DEV server now we know its working and back it up. Take a copy of the clean DBs (including additional DBs such as FEB for surveys, CONCORD for Docs and any 3rd party tool DBs such as PROJEXEC etc.)  and the file system (Connections data\shared, docs share, viewer share etc.). In my case I had to prove that it was working prior to the DB migration so this back up was essential.

If you are going to run a test against your live systems data ensure that you also get a good back up of the file system when the system is down, if possible run the test when the live system is down (out of hours / on a weekend etc.) as the DB peice of the migration is very time consuming and you may need to troubleshoot issues.

The basic rule of thumb is treat this test like any normal Connections DB migration or update Stop it all and back it up.

 

What’s next?

Now we are all prepared .. we can attempt a test at the data migration .. check back in a few days for part 3 of Migrating Connections DB from Oracle to DB2 – where the fun really begins

Migrating Connections DB from Oracle to DB2 part 1

For those of you that follow me on twitter, you will have noticed that I have posted a number of tweets relating to a fun project I am working on.

I have been working with a customer to move them from Connections 5 to Connections 5.5 – Not a massive issue – EXCEPT they are currently using Oracle for the Connections DB and they can not upgrade it to the version required by 5.5 for quite some time, as most of their business systems require the version of Oracle currently running.

This has been a pain .. like you wouldn’t believe .. what should be a simple process has turned into the project from hell!! BUT I am not one to throw the towel in so we have persevered. I must say I was not overly enthused by some of the issues and mistakes that have been made on our side and IBMs but we do have light at the end of the tunnel.

I am going to work through a series of Blog posts to help anyone else who needs to do this ..

Migrating Connections DB from Oracle to DB2

Overview

  1. Open a PMR – you will need specific help from IBM as this is an unsupported action – they will help you though. Be VERY specific and clear in exactly what you are trying to achieve as it can lead to confussion.
    • State the version of the DB you are moving from – in this case Oracle 11.2 on AIX
    • State the version of the DB are moving too – DB2 10.1 on Windows
    • State why you are doing this – as this is important for justifying the support time to help. In our case the customer is a current Connections 5 and Docs user, we have a requirement to upgrade Connections to¬† V5.5 to take advantage of the new features. As Connections 5.5 requires Oracle 12 and the customer can not update the underlying oracle system a move to DB2 is the best course of action. DB2 for Connections will be managed independantly from the rest of the applications.
    • Test the migrated Oracle data with DB2 and Connections 5 to ensure the system is working as expected.
    • Migrate the tested data to Connections 5.5
    • Then do it for real in live
  2. Make sure you have data to test – in our case there is a Connections 5 dev environment pointing to an oracle DB.
  3. Prepare a system to recieve the data – The Connections 5 dev system already had the correct version of DB2 installed as we were using IBM Docs and FEB (for Connections Surveys). We simply created the additional Connections 5 DBs on the dev machine ready to accept the data.
  4. Become familiar with the DBT – The Database Transfer Tool is the only way to get the data out of one system and into another. There are files to prepare and commands to comprise. Have a good text editor, with tabs, you will need it – notepad isn’t going to cut it.
  5. Get good BACKUPS – Backup the clean Connections 5 DBs before attempting to migrate you may need to roll it back, also ensure you back up any existing DBs you have (such as CONCORD for Docs or FEB for Surveys).
  6. Be preapred for multiple attempts – There is a lot of trail and error and fixing issues and trying again. This is not a one time deal.
  7. Logs and SQL queries – There will be a lot of looking at logs and running SQL queries, editing the create db scripts and running again. If you aren’t comfortable with that find someone to help you as you will be by the time we are done.

Things to note

I assumed that becuase I already have a DEV version 5 Connections system I could use that. Create new DBs, migrate the data, switch the datasources to the new database and we should be good – NO – THAT DOESN’T WORK!!

When Connections is installed and you select the type of DB that you are installing against, that is hardcoded somewhere in a config that we couldn’t find .. we WASTED SO MUCH TIME trying to get that sorted and eventually had to give up and reinstall.

If you have an existing system – BACK IT UP, blow it away and reinstall. Don’t waste time and effort trying to fix it .. files will not work.

 

Step 1 – Open a PMR

You MUST do this. IBM will help you even though they do not officially support moving DB types and OS’s. It is much easier if you are staying on the same OS or same DB type, but moving both isn’t fun. This should theoretically be a once only deal – once moved from Oracle AIX to Windows DB2 there will be no going back.

As I stated in the overview be VERY specific and explain exactly what you are trying to achieve. Give IBM the exact DB versions with fixes so they can give you new versions of any DB create scripts they need. In my case Files, Wikis and Blogs needed significant changes to the database create scripts. IBM also sent additional scripts for the fixup post migration so ensure you have those. Back up the original connections.sql folder under the V5 wizards directory (connections.sql.backup in my case) and add any changes to connections.sql.

 

Step 2 – Test data

Make sure you have data to test with. Initially I used the Connections 5 DEV system to test but have since moved on to live data, but the test data has to be from a working system so you can compare.

You must have access to read and write to the DBs as this is required to actually migrate the data. Back up the file system to copy the shared data over.

There may also be differences in data between your live and dev systems so be prepared to edit scripts again and keep copies of all the scripts and workings out you have done.. it will help you document it (yes I said the D word .. Write it all down, you will thank me for it later).

 

Check out Part 2 where we will actually prepare the system and write everything we need to actually get on and do the data migration.

Returning user login issues – fun with the connections databases

I recently had an issue with a user having issues logging in to certain applications.

The user could log in to some apps but recieved an error on others – for instance profiles, activities, communities and forums the user was fine.

Each applications database holds information regarding the user login and their external / directory / guid id – this guid is unique to the user – I found after extensive investigation that this user had orphaned entries in the other application DB tables.

The SQL queries and statements used to resolve this were *fun* to work out and they may be slightly different for each user that has this issue, but it should be fairly straight forward to work out once you know what the issue is.

*NOTE* below is the solution that I used to resolve the issue – it will be / may be different for each user with a similar problem. It is advised where possible to test this on a back up of the DB to ensure it resolves the issue. Always back the databases up before making any change.

Firstly look up the user in the profiles database and gather thier login id (prof_uid) and external directory id (prof_guid)

Once you have these you are ready to start the investigation.

In the case of this user the SQL was as follows :

CORRECT GUID / EXT / DIRECTORY ID = C7B75D042B4C7C7B8025791100311ADA

== BLOGS ==

select * from blogs.rolleruser where username =’jsmith’;
select * from blogs.rollerloginname where username like ‘jsmit%’;
get the user id = 62537efa-3959-42a4-84f3-5e1fdc8cfac0
select * from blogs.rollerloginname where userid = ‘62537efa-3959-42a4-84f3-5e1fdc8cfac0’;
delete from blogs.rollerloginname where userid = ‘62537efa-3959-42a4-84f3-5e1fdc8cfac0′;
delete from blogs.rolleruser where username =’jsmith’;

== DOGEAR ==

select * from dogear.personlogin where loginname like ‘jsmit%’;
get the person_id = 436b98eb-59a9-420f-90d6-22b7a4926e00
select * from dogear.personlogin where person_id = ‘436b98eb-59a9-420f-90d6-22b7a4926e00’;
delete from dogear.personlogin where person_id = ‘436b98eb-59a9-420f-90d6-22b7a4926e00′;
select * from dogear.person where person_id=’436b98eb-59a9-420f-90d6-22b7a4926e00′;
delete from dogear.person where person_id=’436b98eb-59a9-420f-90d6-22b7a4926e00’;

== FILES ==

SELECT * FROM FILES.USER_TO_LOGIN where login_id = ‘jsmith’;
delete FROM FILES.USER_TO_LOGIN where login_id = ‘jsmith’;
select * from FILES.LIBRARY where title like ‘John Smit%’;
get label – C53524EEDB0F84E8802578C5002676AD
delete from FILES.LIBRARY where label =’C53524EEDB0F84E8802578C5002676AD’;
SELECT * FROM FILES.”USER” where name = ‘John Smith’;
delete FROM FILES.”USER” where name = ‘John Smith’;

== forums not an issue – has the correct GUID ==

select * from forum.df_memberlogin where loginname like ‘jsmit%’;
get memberid = d1140454-09ac-4484-a50e-ce914e573e7d

== HOMEPAGE ==

select * from homepage.loginname where loginname like ‘jsmit%’;
get person_id = cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a

select * from homepage.loginname where person_id = ‘cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a’;
delete from homepage.loginname where person_id = ‘cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a’;

select * from homepage.person where displayname = ‘John Smith’;
get person_id of the incorrect GUID – db306bce-40cc-413a-b93c-1ad61a24cdae

select * from homepage.hp_ui where person_id in (‘cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a’,’db306bce-40cc-413a-b93c-1ad61a24cdae’);
make note of any person IDs that bring back any entries – cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a
and ui_ids – 45cbd2dc-aefa-46f4-9607-654ddab953d8

select * from homepage.hp_tab_inst where ui_id like ‘%45cbd2dc-aefa-46f4-9607-654ddab953d8’;
make note of full ui_id – 45cbd2dc-aefa-46f4-9607-654ddab953d8
make a note of tab_inst_id b1072db9-c553-4a04-8366-e7d26a415edb b9114d19-4d97-42b0-8760-580cc956abe8

select * from HOMEPAGE.HP_WIDGET_INST where tab_inst_id in (‘b1072db9-c553-4a04-8366-e7d26a415edb’,’b9114d19-4d97-42b0-8760-50cc956abe8′);
delete from HOMEPAGE.HP_WIDGET_INST where tab_inst_id in (‘b1072db9-c553-4a04-8366-e7d26a415edb’,’b9114d19-4d97-42b0-8760-580cc956abe8′);

delete from homepage.hp_tab_inst where ui_id = ’45cbd2dc-aefa-46f4-9607-654ddab953d8′;

delete from homepage.hp_ui where person_id in (‘cf48e29d-7d89-4f8a-acf0-47b9a8bcb98a’);

delete from homepage.person where person_id in (‘db306bce-40cc-413a-b93c-1ad61a24cdae’);

correct person id = e8238bbd-255f-4609-8a54-e28128f3e66b

== Activities is ok – is the correct GUID==

select * from activities.oa_memberlogin where loginname like ‘jsmit%’;
get memberid – CACG7F00000152B3E7EBA823194CED0000C6

SELECT * FROM ACTIVITIES.OA_MEMBERPROFILE where memberid=’CACG7F00000152B3E7EBA823194CED0000C6′;

== Communities is ok has the correct GUID ==

select * from sncomm.memberlogin where loginname like ‘jsmit%’;
get member_uuid – 0e56702f-9f37-4f2c-b295-2dd3250da726

select * from sncomm.memberprofile where display = ‘John Smith’;

== Wikis ==
SELECT * FROM WIKIS.”USER” where name like ‘John%’;
select * from wikis.user_to_login where login_id like ‘jsmit%’;
delete from wikis.user_to_login where login_id = ‘jsmith’;
delete from wikis.user_to_login where login_id = ‘jsmith@org.com’;

==

When these statements had been run the user can log in correctly as the additional orphaned entries have been removed.

Please note that due to the data and the contstraints on the database that there may be additional statements required – the SQL above is a guide on how I resolved the issue.

It was a FUN FUN FUN one to sort out .. I do love a good problem:)