Creating The Databases For Connections

Before we can install the Connections applications we must create the databases they will use.  The database server can be DB2, SQL or Oracle but the licensing for DB2 is part of your Connections licensing whereas SQL and Oracle would need to be separately licensed.  I tend to use whatever server the customer feels most comfortable supporting, if they are a big SQL or Oracle house I’ll use that.  For our purposes, and if the customer has no preference I like to use DB2.  One note about high availability, the license for DB2 includes the rights to use active/passive HADR for DB2  which means creating two servers which sync with each other but with only one active at a time. To make the active server passive and make the passive server active requires a manual switchover and will entail some amount of downtime.  For additional license cost DB2 offer a full HADR active/active solution but I won’t be discussing that on this blog.

We already installed DB2 so now we just need to run the DBWizard to create the databases.  It’s important to use the db2inst1 account we created during install as “owner” of the DB2 instance (on Windows it’s probably called db2admin) to create the databases. This ensures that the db2inst1 has the right security access to the databases and that later, when the Connections applications attempt to write to the databases, everything works.

Now I can login as db2inst1.  Don’t use “su” as that can throw errors, always login to the server as the db2inst1 (or in Windows db2admin) account.

Setting The Environment

The first thing we should do is make sure the instance of DB2 can run all the Connections databases.  Run the command

db2 update dbm cfg using numdb 20 to allow up to 20 databases to run in this single instance.  That’s a high number but in a reasonably small environment (less than 1000 concurrent users say) it’s fine. 

We also need to set the DB2 instance to use unicode before Connections installs, we can do that whilst we’re here by typing

db2set db2codepage=1208

Then stop and start DB2 to make both the above changes take effect

db2stop
db2start

Creating The Databases

IBM released Day 1 fixes for the DBWizards and you need to find and use these not the ones that shipped with 5.5.  They are on fixcentral and their filenames are

Linux: 5.5.0.0-IC-D1-DBWizard-LO87408_lin_aix.tar
Windows: 5.5.0.0-IC-D1-DBWizard-LO87408_Windows.zip

Using my root account I extract the tar file to a directory like this

mkdir /home2/db2inst1/DBWizard
cd /home/db2inst1/DBWizard
tar -xvf /opt/Software/5.5.0.0-IC-D1-DBWizard-LO87408_lin_aix.tar chown -R db2inst1 * (this recursively makes the file owner of all files in that directory db2inst1)

skitch.9

Launch the ./dbWizard.sh from the terminal session.  This has a graphical interface so if you don’t have one configured you will need to create a response file and run ./db2Wizard -silent -nameofresponsefile insteaad

skitch.8

skitch.7

The same dbWizard is used to create, update and delete databases, it can also just generate the SQL commands to enable you to manually do these activities yourself.  Depending on the complexity of the enviornment and / or if the Wizard starts throwing errors, I often take the SQL commands and manually run them myself so I can monitor and adjust them if necessary.

skitch.6

The second screen asks for (and defaults to) the location of my DB2 install as well as the account that owns that DB instance.  I am running the Wizard using that account to ensure there are no security issues with permissions on the newly created databases.

skitch.5

I have chosen not to create either the Cognos or the Connections Content Manager databases at this time beacuse I will not yet be installing those features.  When I come to install them later I will re-run this Wizard (or the latest version of this Wizard) and create them then.

skitch.2

The final screen shows all the commands that the Wizard is about to run to create each of the databases. These commands point to .sql files that were extracted into the DBWizard directory and hold instructions on how to build each database for each application.  I can save those commands to a file and keep them for reference or use them manually later.

If you want to look at what they are doing, the sql files and routines are under the connections.sql directory in the extracted Wizard location.

skitch.1

The wizard will now run through creating every database and logging the activity to the /home/db2inst1/lcwizard/log/dbWizard directory with one log file for each sql routine.  I like to review the log file activity as it progresses in case there are any issues – the entire process can take anywhere from 30 minutues to 2hrs depending on the speed of your disk.

skitch

Once the Wizard has finished, try to connect to one of the newly created databases from a terminal window to confirm the database is there and db2inst1 has the right access to it.

skitch.10

For example to connect to the Activities database I use
db2 connect to opnact

Next step: configuring LDAP and populating the profiles database

Installing DB2

I am going to use DB2 as our Database server rather than SQL or Oracle because that’s part of our Connections entitlement.  If you have expertise and licensing for either Oracle or SQL and prefer those servers then I would go with them instead.  This DB2 install for our Connections101 server is very simple, a single instance and standard accounts. Depending on your requirements for HA and/or your number of users you may need to give that more thought.

I downloaded and use the DB2 10.5.0.5 (fixpack 5) universal installer.  You can install this without installing 10.5 first.  I extracted the compressed file v10.5fp5_ntx64_universal_fixpack then checked the OS meets the install requirements using the pre-requisite check

From the directory /DB2/univeral run ./prereqcheck

DB2PreReqCheck

the pre requisite check is going to validate if all the correct OS libraries and settings are in place.  In my first check I was missing several necessary files including 32bit versions of some libraries that even the 64bit DB2 installer needs.

Installing Dependencies

Any libraries that are missing can be installed using yum – in this case I was missing gcc which I resolved with “yum install gcc”.  This is why I strongly recommend you don’t attempt a Linux install without a subscription in place to help you find and install all the libraries you need.

Each time I successfully installed a missing library I re-ran prereqcheck again.  In total it took less than 10 minutes to get all the libraries installed.

The final pre-requisite you may fail on is ensuring SELinux is disabled on the OS. By default it will be enabled and to disable it you can edit the file /etc/selinux/conf (in my RedHat deployment) using:

sudo vi /etc/selinux/config and setting SELINUX=disabled

Disable SELinux

Once all the pre-requisites are passed we can go ahead and install DB2 which we do by running ./db2setup from the “universal” directory the installer created (the same directory we ran db2prereqcheck from).

The default installer uses a GUI so you can either configure your SSH connection to use X11 forwarding or (I prefer) use either NoMachine or VNC.  X11 works well but can be very slow to redraw screens.

db2setup 1

I choose to “Install a Product” and in this case I’m installing the DB2 Workgroup Server Edition which is all that I need to host the Connections101 databases.

db2setup 2

I like to choose the custom install so I can minimise what features are added and ensure no unnecessary services are listening or available.  Having services I don’t need installed just exposes access via a route I may forget exists and so don’t secure properly.

db2setup 4

Here are the features I chose – I don’t for example install LDAP or SSH services with my DB2 server for Connections.

Bear in mind that although DB2 itself will install into /opt/ibm/db2/v10.5, the actual databases will install into the home directory of the instance owner – /home/db2inst1. If your Linux partition isn’t set up correctly to allow plenty of space in /home (which by default it often isn’t) you will hit space problems.

Similarly when installing on Windows look at the install screens carefully and note where it wants to use c:\programdata for storing DB2 information. Even if you choose to install the program files on an E drive you need to make sure you check all locations to avoid filling up your C drive by default.

db2setup 6

Now we need to set up users who will have administrative access to the DB2 services. In Windows the default user is a local account called db2admin.  In Linux it’s a local account called db2inst1.  At this point of the install neither account exists, the installer will both create them and grant them the access they need.

It’s important to note what you use on the next few screens for the account names and passwords.  You will need to switch to these accounts at various points to work with DB2

db2setup 8

DB2 can create multiple instances which run in isolation from each other with their own assigned memory and resources.  In larger environments this is useful for sharing the load of many databases across the resources of a large server.

I’m choosing to create a single DB2 instance and install all the databases into it.  In a large environment I would often have multiple instances to ensure I can manage resources effectively and that I can share the Connections databases across them.

db2setup 9

The local db2inst1 account which will be created during install is the account you use to perform all the DB2 maintenance work including creating the Connections databases using the DBWizard and running the update or maintenance scripts.  Post install you can also grant other users rights to do this work if you want.

db2setup 11

DB2 installs listening on port 5000, you can easily change this during the install if you need to.  Some people choose other ports to obfuscate security so that DB2 can’t easily be found on its known port.  If that’s all the security you have in place then you should worry 🙂

db2setup 12

 

db2setup 13

Once the install is complete you can verify it by checking you can login or switch (su)  to db2inst1 (which confirms the account is created) or , if Windows, that you can login to the server using local account db2admin.

You should also verify that the new server is listening on port 50000 or whatever you chose during install.  I like to do this after a clean restart of the OS to ensure the DB2 server does start automatically on OS launch.

Linux

netstat -tulvn | grep :50000

or on Windows

netstat -an | find /i “50000”

Finally we need to activate our DB2 install.  Download the activation file CN3Z2ML from Passport Advantage.  

skitch.25

Unzip the download and find the file db2ese_u.lic which should be in the top directory.

Login to the server as db2inst1 and run

db2licm -a <pathto db2ese_u.lic>

Verify the license has applied by typing db2licm -l

skitch.24