|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>ConfigEnginelib
On my system this was D:IBMConnectionsConfigEnginelibDBT_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>ConfigEnginelib in my case D:IBMConnectionsConfigEnginelib) 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_HOMEfiles folder
Example below of the activites.xml :
<table sourceName="activities.system_lastmod" exclude="true"/>
The source in this example is is oracle the target is db2
Once completed you will have the follwing xml documents in the DBT_HOMEfiles directory
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.
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_HOMEdbt.jar;<Oracle driver>ojdbc6.jar;<db2 driver>db2jcc.jar com.ibm.wps.config.db.transfer.CmdLineTransfer -logDir <path to DBT homelogs>DBT_HOMElogs -xmlfile <path to DBT homefiles>DBT_HOMEfiles<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==
db2 -td@ -vf dropDb.sql
db2 -td@ -vf createDb.sql
db2 -td@ -vf appGrants.sql
db2 -td@ -vf 50-CR1-activities-db2.sql
db2 -tvf predbxfer50.sql
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
db2 -tvf postdbxfer50.sql
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:Installtempactivities_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 datashared, 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 ensire 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.
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
The post Migrating Connections DB from Oracle to DB2 part 2 appeared first on Social Shazza.
Nov 25, 2016
| Recent Blog Posts
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
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
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
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
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
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
Docs 2.0 CR2 – here we go again|
Sat, May 27th 2017 8:05p Sharon Bellamy
I am blogging this mainly so I remember how to solve this
Big thanks to fellow ICS community member and all round great guy Robert Farstad for blogging this
Again you can see a problem when patching Docs 2 to CR2 as it can’t find the FQDN of the hosts.
The code has changed so it’s not possible to use the work around that we had previously.
There is a slight twist to getting it to work which Rob has explained fabulously so thank you mate .. you saved me a lot of grief .. and even IBM
Connections 6 system requirements are available|
Wed, Mar 29th 2017 2:21p Sharon Bellamy
thanks to my friend and co-speaker Roberto Boccadoro for sharing these ..
you can find the system requirements for Connections 6 here
The basic info is:
IBM install manager 1.8.5
DB2 11.1.0 / Oracle 18.104.22.168.0 (enterprise) / MS SQL 2016
TDI 7.1.1 fix pack 6
Connections 6 is going to be released on Friday 31st of March – happy downloading and installing
The post Connections 6 system requirements are available appeared first on Social Shazza.
IBM Cloud migration and management Webinar|
Mon, Feb 6th 2017 2:27p Sharon Bellamy
I have the pleasure of giving another webinar on Wednesday at 4pm UK / 11 am EST – with my friend and IBMer Luis Guirigay
We will be talking about migrating to the cloud and user management once you are there ….
Join this webinar to learn more. Register here.
Are you interested in running some or all of your messaging platform in the Cloud? Interested in IBM Verse? Worried about how to manage the user-lifecycle in a hybrid or cloud environment? Not sure about how or where to start?
Want to go to IBM Connect .. be nice to a Champion for a discount|
Thu, Jan 19th 2017 11:01a Sharon Bellamy
Being an IBM Champion is great and it does come with some benefits.
Today all IBM Champions are happy to announce that we can offer YOU a discount of $100 for IBM’s upcoming ICS event – IBM Connect in San Francisco
So what do you need to do – just reach out to an IBM Champion tell us why you want to go to Connect and what your expectations are and as if by magic we will give you the dscont code.
Now is a great time to register .. and I wish I was going to be there with you all