203 Lotus blogs updated hourly. Who will post next? Home | Blogs | Search | About 
 
Latest 7 Posts
Version 10 and beyond – I Aint Dead
Fri, Nov 3rd 2017 10
IBM Docs 2 CR3 is here – and with some cool new features
Tue, Oct 31st 2017 9
Issues with IBM Connections 5 / 5.5 and Chrome 60
Fri, Aug 18th 2017 3
Connections 6 migration observations
Tue, Jul 25th 2017 7
It’s that time of year again ..
Fri, Jun 16th 2017 5
Let’s get Stitch talking – Project #talkie pt 3
Mon, Jun 5th 2017 6
Building the AIY – Project #talkie pt2
Sat, May 27th 2017 7
Top 10
Version 10 and beyond – I Aint Dead
Fri, Nov 3rd 2017 10
IBM Docs 2 CR3 is here – and with some cool new features
Tue, Oct 31st 2017 9
FEB / Connections Surveys – custom LDAP attributes
Wed, Jul 1st 2015 7
Can you spare an item to help raise money for two amazing causes?
Wed, Jan 13th 2016 7
What’s New In ICS Computing Webinar
Wed, Jul 6th 2016 7
Project #talkie pt1 – getting to grips with a rasberrypi
Sat, May 27th 2017 7
Building the AIY – Project #talkie pt2
Sat, May 27th 2017 7
Connections 6 migration observations
Tue, Jul 25th 2017 7
Let’s get Stitch talking – Project #talkie pt 3
Mon, Jun 5th 2017 6
Who knew my love for StarWars would win me a prize
Sat, May 27th 2017 5


Migrating Connections DB from Oracle to DB2 part 3
Twitter Google+ Facebook LinkedIn Addthis Email Gmail Flipboard Reddit Tumblr WhatsApp StumbleUpon Yammer Evernote Delicious
Sharon    

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:IBMConnectionsConfigEnginelibDBT_HOMEdbt.jar;D:IBMOracleojdbc6.jar;D:IBMSQLLIBjavadb2jcc.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir D:IBMConnectionsConfigEnginelibDBT_HOMElogs -xmlfile D:IBMConnectionsConfigEnginelibDBT_HOMEfilesactivities.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:

 

 

The post Migrating Connections DB from Oracle to DB2 part 3 appeared first on Social Shazza.



---------------------
http://feedproxy.google.com/~r/socialshazza/~3/13CHsFMJ3LM/
May 27, 2017
3 hits



Recent Blog Posts
10
Version 10 and beyond – I Aint Dead
Fri, Nov 3rd 2017 2:15p   Sharon Bellamy
You are a Connections / WebSphere admin what do you care about Domino? I hear people ask – well actually I care a lot .. I use these products every day – I can’t do my job with out Domino / Verse / Notes and Sametime. After what can only be described as a lack of love for the Domino/Notes stack IBM have made a HUGE announcement in partnering with the development team at HCL Technologies to bring a new version to the beloved Domino stack. IBM signs strategic partnership with HCL
9
IBM Docs 2 CR3 is here – and with some cool new features
Tue, Oct 31st 2017 12:10p   Sharon Bellamy
So at last IBM Docs 2 CR3 has been released and with it some awesome new features that the community have been asking for (thank you IBM). What’s new in Docs 2 CR3? LOADS of new features and fixes including … The most exciting is probably the ability to deploy the Conversion server on linux (finally)!! You can directly deploy Conversion server on Linux when installing IBM Docs. If you already have IBM Docs installed with Conversion server on Windows, you can migrate existing Conve
3
Issues with IBM Connections 5 / 5.5 and Chrome 60
Fri, Aug 18th 2017 9:58a   Sharon Bellamy
There is a known issue with Connections 5 CR4 and all versions of Connections 5.5 when using Chrome version 60. It affects events not being displayed and errors relating to events in the UI. It has also been reported that comments can be affected also. After multiple BP’s and Customers reported this IBM Support have come up with a fix very quickly. The fix you need is IFLO92844 – currently it is only available from IBM support – not sure how long it will be until it is availabl
7
Connections 6 migration observations
Tue, Jul 25th 2017 4:00p   Sharon Bellamy
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. A
5
It’s that time of year again ..
Fri, Jun 16th 2017 5:38p   Sharon Bellamy
I am Running for Feel the Force Day …. My partner in crime Sam will be joining me again this year, along with Jonathan and Mandy will make up the #TeamRun4FTFD to raise funds. Jonathan has cerebal palsy and he doesn’t let that slow him down. Mad on trucks, cars and any kind of awesome vehicle (just see Shaw on Tour for details), he has recently done a bunch of exciting stuff including running (in a race chair), ice skating and trampolining. Mandy is the biscuit queen Famous for her
6
Let’s get Stitch talking – Project #talkie pt 3
Mon, Jun 5th 2017 2:41p   Sharon Bellamy
So after building the kit and testing that it worked .. It was time to get creative .. there are some examples on what to do code wise on the AIY kit voice site First I had to go through the billing set up and processes. Even though the code is free, and the kit is free and they give you $300 worth of credit – you still have to set up billing. It was a pain as I already had a cloud dev account for some of the APIs I use on bit of my sites, but eventually I worked it out – I may have
7
Building the AIY – Project #talkie pt2
Sat, May 27th 2017 11:55p   Sharon Bellamy
Before I could attempt to get creative with the code examples – we first had to build the kit and make sure it worked with the things and code that was provided. I have used the images from the aiyprojects.withgoogle.com/voice page. They are great pictures and show step by step how to put things together, I may have forgotten to take step by step pics as we built it too. Prep the SD Card With the voice SD image we downloaded in pt1 – use etcher to flash the SD card and get it ready f
5
Who knew my love for StarWars would win me a prize
Sat, May 27th 2017 10:29p   Sharon Bellamy
On May the 4th (also known as StarWars day – you work it out) one of my good friends and former co worker (not sure how he put up sitting opposite me for 2 and a half years ) Mr Dave Sheryn pinged me with an interesting link to a starwars costume competition .. “That’ll be fun”, I thought, “will give it ago .. have a snowballs chance in hell of winning” – so I posted this pic of me With the message O well if we are showing off star wars costumes .. thi
7
Project #talkie pt1 – getting to grips with a rasberrypi
Sat, May 27th 2017 9:45p   Sharon Bellamy
What’s all this #talkie stuff about then? So I have been posting stuff on facebook twitter and instragram with the #projecttalkie or #talkie hash tag .. Most of you know that I costume .. and as well as my love for star wars (and my mandalorian costume), I also LOVE to costume as Stitch … You can see me here as my favourite fluffy blue alien experiment (626) with my good pal and all around lovely Captain America friend Mr James Budd. Again most of you know that i am a HUGE supporte
3
Migrating Connections DB from Oracle to DB2 part 3
Sat, May 27th 2017 8:48p   Sharon Bellamy
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 s




Created and Maintained by Yancy Lent - About - Planet Lotus Blog - Advertising - Mobile Edition