Partners Blogs:

Stuff:

Blog Category: connections

With the latest version Db2 (version 10.X) the native SQL client db2cc has been removed so you now need a separate SQL client for your admin work, this is a real ball ache, but something that us developers have been putting up with for ages, so I thought I would do a quick guide to install a nice SQL client for connections.

Been around for ages so well respected (even die hardened DBAs will acknowledge that your choice is not terrible)

FREE

Small footprint (just needs Java and you will already have that if you are doing connections dev/admin)

hellishly powerful

Uses IBM’s own jar files.

works on Win / Mac / Linux

There are tons of other options of course most, developers will be familiar with Eclipse SQL as that is the most common plug in for eclipse, where as some admins prefer Toad or IBM Data Studio

Once you have downloaded and run the install Jar you will only find one option worth mentioning, and that is to install the IBM DB2 option (just a tick box)

Everything else is just either next or picking the install directory

When you first open it you will get a screen like this

Click on the big “Drivers” button on the left hand side, and scroll down till you see “IBM DB2 App Driver” entry, you will see that like nearly all the other entries, it has a red X by it,

Now Double Click on the entry and in the pop up box, move to the “Extra Class Path” Tab

Next click on the “Add” button and go hunting for the driver Jar files, these are best to get of the DB2 server, they are:

db2jcc.jar

db2jcc_licence_cu.jar

and you will tend to find them in the directory

X:\Program Files\IBM\SQLLIB\java

where ever you have installed the db2 server

Once you have added them, change the “Class Name” drop down to

com.ibm.db2.jcc.DB2Driver

That’s you driver sorted, now make some connections

Click on the “Aliases” button then the “+” button to add a new Aliases

Select the driver you just setup, from the drop down field

Then in the URL field put the connection string for the database you want to get to in the format

jdbc:db2://XXX.XXX.XXX.XXX:50000/XXXX (port 50000 is the default port for db2)

for example

jdbc:db2://localdb2.ldc.com:50000/BLOGS

will get me the blogs database on my local server

for extra info here is the list of all the databases for the different connections Apps

Connections App: Files, DataBase: FILES

Connections App: Activities, DataBase: OPNACT

Connections App: Blogs, DataBase: BLOGS

Connections App: Communities, DataBase: SNCOMM

Connections App: Community Events, DataBase: SNCOMM

Connections App: BookMarks, DataBase: DOGEAR

Connections App: Forums, DataBase: FORUM

Connections App: HomePage , News, Search, DataBase: HOMEPAGE

Connections App: Metrics, DataBase: METRICS

Connections App: Mobile, DataBase: MOBILE

Connections App: Profiles, DataBase: PEOPLEDB

Connections App: Wiki, DataBase: WIKIS

Then just use enter the username and password (hint, you will NEVER get the lsuser account off your admin)

and click “OK”

You now have an alias that you can double click to connect to the specified database, you are going to have to create one for each of the Connections databases :( , but you can make things easier by right clicking on a alias and copying it so you only have to make changes rather than start again.

But it does seem a a pain to make everybody do this full setup, you can cheat even more by copying the SQLAliases23.xml file (it’s normally in a hidden directory in the users home drive but varies according to the operating system) and using it to replace the one on your target PC, all the aliases will then appear already setup.

Finally before this turns into a Squirrel sql manual, you can double click each of the aliases and not only see the database tree and gain access to the tables, but run SQL queries via the SQL tab at the top of the screen just under the normal menus.

This page is just a living reference for thoese of us using the connections databases directly who want to make sure our code works by checking the db schemas before performing actions, you can see how to get these values in a previous post

If IBM would like to document this themselves (I know they wont as they dont want us near the dbs directly and want us to go via the APIs) I would be happy to bow out and point to their site :)

NOTE: All these tips are not officaliy sanctioned by IBM as they do not release DB schema info to the public, so you use them at your own risk, Also if IBM are reading this and I either have somthing wrong or you have documented this for the public, then please tell me and I will correct :-)

Anyone who has had a dig around inside the back end SQL databases (or even compared a number of the ATOM feeds) for IBM Connections will have realised that ID’s are not a constant across the whole of Connections i.e. the userID that apparently created a file will not match the user ID that created a community even though you know that it was the same user that performed both actions, the reason for this is simple, IBM Connections was not build from the ground up as a cohesive whole, its a bunch of application and their databases that have been glued together, this makes it a bugger to link things together under the banner of a single user both for the more serious Developer and for Administrators trying restore/fix/edit records.

So I thought a little database relationship diagram would help make life a bit easier, the core of everything is the EMPLOYEE table in the PEOPLEDDB as that is where the global profile ID is generated for each person, you then have to find where that is mentioned in each of the applications individual member tables, from there you can use that to work out actually what that user has done in the system, thankfully most of the individual application database’s use the same sort of text identifier so they don’t cause a problem once you know they exist, the 2 exceptions are the FILES database which uses an IBM binary format (goodness know why, I’m guessing that it makes sense to use one for the files them selves and they were trying to be consistent within the FILES application) and the HOMEPAGE application in which the UNID is hidden inside a text string which you have to parse.

I will keep this diagram up to date, but please yell if you need more.

this diagram works equally well on both v4 and v4.5

NOTE: All these tips are not officaliy sanctioned by IBM as they do not release DB schema info to the public, so you use them at your own risk, Also if IBM are reading this and I either have somthing wrong or you have documented this for the public, then please tell me and I will correct :-)

One of the many little bundles of joy when dealing with back end SQL code on ‘living’* application such as IBM Connections, is that SQL that may have worked for one version or service pack might not work for another

To cater for this you might have to write for or at least be able to handle the diferent versions of a database, when doing this you tend not to use the actual product version ie “Connections 4.5.0.0.” but the db schema number for each database, thankfully IBM store the db schema for each database in Connections (though not with a great deal of consistency), the following is the SQL to get this for each of the databases that make up connections.

Connections App: Profiles

Connections App: Wiki

BD: WIKIS
Table: PRODUCT
Connection v4.5 Base Schema Number: 82

SQL to get Schema

SELECT SCHEMA_VERSION FROM"WIKIS"."PRODUCT";

*By living I mean ‘the feckers keep changing them’

NOTE: All these tips are not officaliy sanctioned by IBM as they do not release DB schema info to the public, so you use them at your own risk, Also if IBM are reading this and I either have somthing wrong or you have documented this for the public, then please tell me and I will correct :-)

As a connections developer you will be fiddling with you local Websphere configuration, and down that road madness lays, so before you touch anything the make the whole thing go BANG! (and quite frankly that can be anything, you need to know how to backup and restore the config.

To do this first go to the bin directory of your deployment manager, in my test server’s case that is (yours will most likely be very similar except for it being on “E”)

E:\IBM\WebSphere\AppServer\profiles\Dmgr01\bin>

and just enter “backupconfig” giving the location of a new zip file you want created for everything to be stored in and wait for the stream of little dots to finish,

backupconfig e:\preLDAPchange.zip

On a web sphere server with a ‘medium’ Connections installation on it, This will give you a Zip file of about 775Meg so make sure you have the space (on a side note go have a look inside the zip it’s fascinating, like some sort of config abattoir)

hopefully you wont want it but say your change broke everything and you need to put it all back as it was, you run the ‘restoreconfig’ comand, as follows using the Zip file you created

restoreconfig e:\preLDAPchange.zip

Again, just wait for it to be done, and things should start working again.

Of course its all fully documented on the IBM website if you know to look Here And Here

NOTE: I’m not a proper Web Sphere admin, If you want proper Web Sphere and Connections admin you want the Turtle Partnership

Now that we are all “connections developers” according to IBM, sooner or later you will need to starting dealing with Web sphere in a serious fashion, this is no simple task, as Web Sphere admin as far more complex that developing for Web sphere and the documentation makes massive assumptions, so I will be doing a series of simple and essential tips for developers so make your life a bit easier, starting off will be changing XML config on connections.

Most of the connections config resides in a file called “LotusConnections-config.xml”, now there are copies of this file all over the place, to change them you check them all out, make the change in one place then check them back in, this not only keeps the changes consistent over your connections installation but it validates your changes and keeps a backup.

To do this first go to the bin directory of your deployment manager, in my test server’s case that is (yours will most likely be very similar except for it being on “E”)

E:\IBM\WebSphere\AppServer\profiles\Dmgr01\bin>

Then enter the wasadmin console with

wsadmin -lang jython

Next run the Connections Config jpython file

execfile("connectionsConfig.py")

Once that is done (after a load of output to the screen) you need to check out the config files, you need to provide a location to check the files out to (just create a directory somewhere safe) and the name of the web sphere cell you want to get the files from (see below on how to get that)

LCConfigService.checkOutConfig("E:\working","WIN-L5H5FQ2LKP1Cell01")

If you don’t know the name of the cell you are working on (hell I never know) you can find out by typing the following.

AdminControl.getCell()

You can now alter the files in the directory to suit what ever changes you want to make, the best way though is to use the “updateConfig” command which makes sure that you don’t make any bad types and screw your config up.

Sometimes I get the feeling the powers at be are not giving the IBM developers quite the time they need to document stuff, IBM Connections is the best example of it I know, take this sterling example of how to add a member programmatically and even if they have slightly better versions such as this they are not a patch on the notes ones, so every time I hit one and end up solving it my self I thought I would do a proper guide

Note: all of my documentation assumes the worst case scenario that I can think off which in this case is to have the “emails not visible” setting on, which means the Atom feeds will NOT accept email address as parameters, if you need to get the userid for Atom is this is the case you can get it with this function

The Atom ‘add user’ and search functions detailed later CAN take an email address, but as IBM always give you best case scenarios, I’m giving you a worst case one.

Oh, shout out to Mikkel Flindt Heisterberg at http://lekkimworld.com/ who is the connections god, and pointed me in the right direction when I was having a head bashing moment on a custom names space issue for this.

Right so we want to add a user to a restricted community in IBM connections and we want to do this via the atoms feeds, the minimum XML I have found that you need to add a new user to a community is this

As a little add on from This post on getting The User Id from IBM Connections, we discovered another little wrinkle, and that is the single sign on name that you get from @username in domino is not necessary the same as the what IBM connections is wanting in the ?Name= search parameter in its profile search, as we all should know the domino full name is not always the FirstName + ” ” + Last name then we tend to expect, so I just do a little lookup before running the function in the previous blog post

IBM Connections has an evil setting that disables emails from being seen on the system, this is meant as a security setting, but it not only hides the email from the front end but from all the atom feeds, suddenly you don’t have a unique key to reference users on functions.

Well that’s not exactly true, you have a internal ID that IBM functions will accept instead e.g “A7B2C512-27E5-596C-8625-7AD4000843Z3”, the only problem is how in the heck do you GET HOLD OF IT?

If you are within a community you can get it from member lists and such but how about when you want to just want to get a name that is on the system, say to add it to a community, the only way I have found is to search for it and no you cant search for it by email as that is disabled.

You have to search for it by name which while not exactly precise does the job but even that is not an ultra easy function, here is a little function to do it for you.

private String getUserID( String adminUserName, String adminPassword, String baseURL, String ProfileSearchURL, String SearchUser)throws NotesException {
String Unid ="";try{// example values for the passed parameters.
adminUserName ="administrator";
adminPassword ="password";
baseURL ="https://www.myconnectionsSite.com";
ProfileSearchURL ="/profiles/atom/search.do?name=";//sthis is what connections 4 uses
SearchUser ="John Smith";//build our search string
String searchString = baseURL + ProfileSearchURL + URLEncoder.encode(SearchUser,"UTF-8");
Abdera abdera =new Abdera();
AbderaClient client =new AbderaClient(abdera);//ensure we can handle SSL requests
AbderaClient.registerTrustManager();//logon to connections
client.addCredentials(baseURL,null,null,new UsernamePasswordCredentials(adminUserName,adminPassword));//make the call
ClientResponse resp = client.get(searchString );
XPath xpath = abdera.getXPath();//get the xml back an navigate to the root of the document
org.apache.abdera.model.Document doc = resp.getDocument();
Feed feed =(Feed) doc.getRoot();//if there is more than 1 returned entry then the user name is none unique and we cant trust our responseif(xpath.numericValueOf("count(/a:feed/a:entry)", feed).intValue()>1){
System.out.println("Ack we have more than 1 user with that name I cant be certain I have got the right one");}else{//as there is only one entry we can navigate straight down to the contributer sectnio which contains the user id
Object contrubuternode = xpath.selectSingleNode("/a:feed/a:entry/a:contributor", feed );//because of the "snx" part we cant get the value without passing xpath our namespaces from the feed so it knows what "snx" means
String snxUserid = xpath.valueOf("snx:userid",(Base) contrubuternode, feed.getNamespaces());
Unid = snxUserid;}}catch(ClassCastException e){
System.out.println("no user found or some other garbage return");
e.printStackTrace();
Unid ="";}catch(Exception e){
e.printStackTrace();}return Unid;}