SQuirrel SQL For IBM Connections

Posted on by Mark Myers Leave a comment

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.

My recommendation for this is http://squirrel-sql.sourceforge.net/

why?

  • 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 FilesIBMSQLLIBjava

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.

 

 

Have Fun!!

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

New Anime Series: Black Bullet

Posted on by Mark Myers Leave a comment
First Episode(s) Review for: Black Bullet
Summary:

After a terrible infestation / invasion from really nasty infecting monsters, mankind has retreated behind huge monoliths that keep the beasties at bay, any of them that get past are hunted down by red eyed young girls and their controllers, this is the story of one such pair.

Animation:

A lot of the early 2014 seasons seems to be filled with the slightly washed out cell style of animation, I have no complaints as it is one of the most distinctive things about anime, the only down side of this series is they seem to have done the cars in CGI which does not match quite right.

Plot Potential:

In the first 15 mins I did not think I would be going to pay more that cursory attention to the whole series as it seemed very much a stamped out of a template job, then they added a serious and deeper angle when it turns out that the very girls that are keeping humanity alive are treated like cattle and ostracised at every point (hell they even live in the sewers when they are not being used)

Music:

Opening is what other members of the household call “Aweseome”, not quite MP3 player quality as it needs a lot of context but still very rousing

Reminds me of:

Another one that is a lot like Aria the Scarlet Ammo but in a company setting a lot like Heat Guy J and monsters more like the D Gray-man

Overall:

I was initially unimpressed with this series particularly because it was using very young girls in quite an adult (violence wise) situation, but they have really stuck the knife in on the refugee and discrimination angle, this has given this series a lot more that its basic “remnants of humanity against the insect hordes” setting would indicate. a somewhat grim thumbs up.

 

 

 

Disclaimer: These are mini reviews of anime’s that are fresh out in Japan and are not licensed in the UK, buy them once they have been licensed or at the very least buy the merchandise, remember if the anime makers make a loss, THEY WILL STOP MAKING ANIME!!

Living Document: Connections Db Schema Versions

Posted on by Mark Myers Leave a comment

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 🙂

Connections App: Files

BD: FILES

Table: PRODUCT

Connections v4.0 BASE: 79

Connections v4.0 CR1: 79

Connections v4.0 CR2: 79

Connections v4.0 CR3: 79

Connections v4.0 CR4: 79

Connections v4.5 BASE: 82

Connections v4.5 CR1: 82

Connections v4.5 CR2: 82

Connections v4.5 CR3: 89

Connections v4.5 CR4: 89

Connections App: Activities

BD: OPNACT

Table: OA_SCHEMA

Connections v4.0 BASE: 53

Connections v4.0 CR1: 53

Connections v4.0 CR2: 53

Connections v4.0 CR3: 53

Connections v4.0 CR4: 53

Connections v4.5 BASE: 56

Connections v4.5 CR1: 56

Connections v4.5 CR2: 56

Connections v4.5 CR3: 56

Connections v4.5 CR4: 56

Connections App: Blogs

BD: BLOGS

Table: ROLLER_PROPERTIES

Connections v4.0 BASE: 43

Connections v4.0 CR1: 43

Connections v4.0 CR2: 43

Connections v4.0 CR3: 43

Connections v4.0 CR4: 43

Connections v4.5 BASE: 51

Connections v4.5 CR1: 51

Connections v4.5 CR2: 51

Connections v4.5 CR3: 51

Connections v4.5 CR4: 51

Connections App: Communities

BD: SNCOMM

Table: SNCOMM_SCHEMA

Connections v4.0 BASE: 76

Connections v4.0 CR1: 76

Connections v4.0 CR2: 76

Connections v4.0 CR3: 76

Connections v4.0 CR4: 76

Connections v4.5 BASE: 77

Connections v4.5 CR1: 77

Connections v4.5 CR2: 77

Connections v4.5 CR3: 77

Connections v4.5 CR4: 77

Connections App: Community Events

BD: SNCOMM

Table: CA_SCHEMA

Connections v4.0 BASE: 10

Connections v4.0 CR1: 10

Connections v4.0 CR2: 10

Connections v4.0 CR3: 10

Connections v4.0 CR4: 10

Connections v4.5 BASE: 15

Connections v4.5 CR1: 15

Connections v4.5 CR2: 15

Connections v4.5 CR3: 15

Connections v4.5 CR4: 15

Connections App: BookMarks

BD: DOGEAR

Table: DOGEAR_SCHEMA

Connections v4.0 BASE: 14

Connections v4.0 CR1: 14

Connections v4.0 CR2: 14

Connections v4.0 CR3: 14

Connections v4.0 CR4: 14

Connections v4.5 BASE: 15

Connections v4.5 CR1: 15

Connections v4.5 CR2: 15

Connections v4.5 CR3: 15

Connections v4.5 CR4: 15

Connections App: Forums

BD: FORUM

Table: DF_SCHEMA

Connections v4.0 BASE: 21

Connections v4.0 CR1: 21

Connections v4.0 CR2: 21

Connections v4.0 CR3: 21

Connections v4.0 CR4: 21

Connections v4.5 BASE: 27

Connections v4.5 CR1: 27

Connections v4.5 CR2: 27

Connections v4.5 CR3: 27

Connections v4.5 CR4: 27

Connections App: HomePage , News, Search

BD: HOMEPAGE

Table: HOMEPAGE_SCHEMA

Connections v4.0 BASE : 110

Connections v4.0 CR1 : 110

Connections v4.0 CR2 : 132

Connections v4.0 CR3 : 132

Connections v4.0 CR4 : 132

Connections v4.5 BASE: 210

Connections v4.5 CR1 : 210

Connections v4.5 CR2 : 210

Connections v4.5 CR3 : 211

Connections v4.5 CR4 : 213

Connections App: Metrics

BD: METRICS

Table: PRODUCT

Connections v4.0 CR2: 29

Connections v4.0 CR1: 29

Connections v4.0 CR2: 29

Connections v4.0 CR3: 30

Connections v4.0 CR4: 30

Connections v4.5 BASE: 34

Connections v4.5 CR1: 34

Connections v4.5 CR2: 37

Connections v4.5 CR3: 37

Connections v4.5 CR4: 37

Connections App: Mobile

BD: MOBILE

Table: ROLLER_PROPERTIES

Connections v4.0 BASE: 1

Connections v4.0 CR1: 1

Connections v4.0 CR2: 1

Connections v4.0 CR3: 1

Connections v4.0 CR4: 1

Connections v4.5 BASE: 1

Connections v4.5 CR1: 1

Connections v4.5 CR2: 1

Connections v4.5 CR3: 1

Connections v4.5 CR4: 2

Connections App: Profiles

BD: PEOPLEDB

Table: SNPROF_SCHEMA

Connections v4.0 BASE: 33

Connections v4.0 CR1: 33

Connections v4.0 CR2: 33

Connections v4.0 CR3: 33

Connections v4.0 CR4: 33

Connections v4.5 BASE: 36

Connections v4.5 CR1: 36

Connections v4.5 CR2: 36

Connections v4.5 CR3: 36

Connections v4.5 CR4: 36

Connections App: Wiki

BD: WIKIS

Table: PRODUCT

Connections v4.0 BASE: 79

Connections v4.0 CR1: 79

Connections v4.0 CR2: 79

Connections v4.0 CR3: 79

Connections v4.0 CR4: 79

Connections v4.5 BASE: 82

Connections v4.5 CR1: 89

Connections v4.5 CR2: 89

Connections v4.5 CR3: 89

Connections v4.5 CR4: 89

 

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 🙂

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

New Anime Series: Akuma no riddle

Posted on by Mark Myers Leave a comment
First Episode(s) Review for: Akuma no Riddle
Summary:

A gifted and stone cold young female assassin is sent to a new class at school filled with other killers and one as yet unknown but seemingly obvious victim, all the killers imediatly start to size each other up, but the victom takes a shine to our little stone cold killer 🙂

Animation:

Clear cell animation with a slight hazy over tone, Very good human movements, lots of nice details and subtleties including blinking at times when it not needed, which added a lot of depth to the visualisation.

Plot Potential:

Well I don’t know, on the surface this seems to be a dead simple plot with the hardened heroine falling for the Innocent girl with the hard history, but there are so many lead characters and so many plot twist hints that I have no idea whats going to happen next for example Haru’s “dusty smell”: is that a “sunny smell” as one characters thinks or is the the smells of crypts and things brought back to life

Music:

Hard Rock for both beginning and end, good tunes, the opening will I suspect be listened to about 4 times before I start to skip, the ending maybe twice.

Reminds me of:

Aria the Scarlet Ammo with characters from Deadman Wonderland

Overall:

Riveting if slightly dark watching trying to figure out where this is going as well as the shear enjoyment of them trying to pack so many primary characters in to one anime, it is a bit overwhelming in places and I had a couple of WTF! moments and had to flip back the timeline and watch again, but it has the making of a fun anime, let us hope it lives up to its promise.

 

 

 

Disclaimer: These are mini reviews of anime’s that are fresh out in Japan and are not licensed in the UK, buy them once they have been licensed or at the very least buy the merchandise, remember if the anime makers make a loss, THEY WILL STOP MAKING ANIME!!

New Anime Series: Kamigami no Asobi

Posted on by Mark Myers Leave a comment
First Episode(s) Review for: Kamigami no Asobi
Summary:

A young shrine maiden who is following the path of the sword rather than getting ready for her entrance exams, is transported by an ancient sword to some form of Hogwarts’s cross with the playboy mansion but for young girls, in this case she is alone with a wide variety of very hot young men and the odd flying horse, it turns out that they are all young gods here to be taught about humans BY humans what could go wrong

Animation:

This series is filled with the clear bright animation that makes me wish I lived in an anime world, not too shinny but just enough so that you think every day will be a bright new future, detail where it needed but not over done or seemingly trying too hard, A very nice middle ground with good use of light.

Plot Potential:

If it’s got a plot I have not yet found one, that might be because I’m rolling around on the floor at the sheer “teenage girls fantasy” nature of the environment, of particular note was the stunned “it’s so big” line when the flying stallion is standing next to the girl..

Music:

Lots of slow piano tinkles going on in the background (I honestly was expecting to hear Barry White a couple of times)

Reminds me of:

Mostly The Wallflower but with the humour and situations of El-Hazard

Overall:

Anime often makes me chuckle in that it is seen to cater only to a male hetero audience but the number of truly beautiful young males is nearly equal to the number of imposable women, and this is a fun example of the former. easy watching

 

 

 

Disclaimer: These are mini reviews of anime’s that are fresh out in Japan and are not licensed in the UK, buy them once they have been licensed or at the very least buy the merchandise, remember if the anime makers make a loss, THEY WILL STOP MAKING ANIME!!

Connections Db Schema Tip2: Finding the UserID

Posted on by Mark Myers Leave a comment

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 🙂

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

New Anime Series: Captain Earth

Posted on by Mark Myers Leave a comment
First Episode(s) Review for: Captain Earth
Summary:

The death of his astronaut farther has a detrimental effect on a teenagers life, meaning he is withdrawn despite being quite skilled and popular, when a huge floating rainbow ring appears in the sky he is drawn back to the place in his childhood where he first saw it, oh and chuck in a space princess in a giant pink mecha.

Animation:

Slightly toned down cell animation (the sort they use when try to achieve realism without putting a lot of effort into detail) totally watchable.

Plot Potential:

So what looks at first to be a simple “boy finds secret life in space” starts to look a lot deeper than you would expect, with a lot more threads already coming into play, defiantly not a background series (some people who I thought were good guys already turned into bad guys within 15 mins)

Music:

Moving but not catchy, sounds like they have plundered NASA’s dramatic music store

Reminds me of:

Hard to say really, but you know those series that have a group of children going into an old temple when they are young and it going back to bite them then they are older? That but in space, I suppose like a slow moving and serious Tenchi Muyo!

Overall:

Even though this is a standard plot line, I really enjoyed the variants in it, the hero is not a d**k which always helps and one wonderful old school bonus, the giant machine transformations are truly ridiculous, there is more “clunk clinking” than talking in the first episode, this is a series to watch (and pull the odd Dragon ball Z pose too)

 

 

 

 

Disclaimer: These are mini reviews of anime’s that are fresh out in Japan and are not licensed in the UK, buy them once they have been licensed or at the very least buy the merchandise, remember if the anime makers make a loss, THEY WILL STOP MAKING ANIME!!

Working from anywhere

Posted on by Mark Myers Leave a comment

I was taught by Mr Ben Poole that a good contractor should leave his desk empty and clean at the end of each day, and watched how Gab Davis seems to be able to work from anywhere at a moments notice so I have been trying for some time to manage both things.

As normal I have not quite got the hang of the simple part, and given the small nature of my studio flat where I do most of my from home working I have tried to pack my whole office into one bag but it does have a few advantages

  • I don’t mind working anywhere as I have no ‘homebase’ as such.
  • I can always give my all at a clients site as I will never be lacking anything
  • I don’t mind doing less than a day at a clients as I can just get on with other stuff

This all works well for me as I enjoy people’s company and do my best work in a busy environment. so I though I might share what I carry in addition to a the real basics: a Laptop (Thinkpad W510), Phone (Nexus 5) and headphones (Sennheiser 550-X)

 

 

Core Stuff

  • The silent and powerful USB fan from Thermaltake, saved me from misery in many an over hot office.
  • In my opinion the best mouse in the world the Logitech M570
  • Baby TPLink 150Mbps Wireless N Nano Router , USB powered and perfect for sites where they may not have a desk setup for you, you can just plug this in and you are up and running.
  • Bloody huge Thinkpad charger, weighs a sodding ton, but can be used as a ball and chain in those tricky meetings.
  • At last a proper retractable and collapsible UK power cable this one by ReTrak
  • Western digital Elements Portable HDD inside its Caselogic case a more reliable and essential paring I have yet to see
  • Anker portable USB 15000mAh battery + the lovely smaller version provided by Panagenda
  • Decent 2Amp USB charger any brand is fine, but this one is from Lenovo
  • Torch: present from Matt White (for some late night working) + spare batteries (including ones for my headphones)

 

 

Abroad and in the dark

  • Eagle Creek bag to store everything from all the pictures in (they also supply the small bag in the next image)
  • The awesome GoalZero Luna LED light
  • Skross World Adaptor Pro+ which is by far and away the best socket adaptor I have found, both in terms of amperage it can take and the fact it is earthed.
  • Lenovo Travel charger + its thousands of cables and adaptors
  • Various adaptors: Euro to US, kettle lead to figure of eight and vs versa

 

 

Little Bag

  • 5 USB cables of various lengths
  • 2 USB OTG adaptors so that I can plug things like keyboards and usb sticks into my phone/tablet
  • Mini USB hub
  • SIM adaptor and tool (in case of phone dieing at a critial time)
  • USB Sticks
  • Audio adaptor cables for my Sennheiser 550-X bluetooth headphones
  • Lindy retractable Ethernet cable, this may look big but its nice and long and most importantly it protects the little clips from being broken which happened to its 2 predecessors
  • Spare mouse by Swiftpoint
  • Spare headphones

 

 

One thing that people tease me about with my setup but frankly I simply could not do without is my Magma laptop Riser, this combined with a USB version of the ThinkPad Keyboard and a couple of mouse pads as arm rests mean I get to have the top third of my laptop’s screen at my sitting eye level which is how its supposed to be.

What do I carry all of this in?

After the destruction of many brands of rucksack including the Wenger backpacks that everybody else swears by, I have finally settled on the Lowepro pro runner 450aw which while rather large means I can carry everything I want and JUUUSSST get it on a plane as carry on (its right on the limit), if I’m travelling on a budget airline I use a Lowepro Fastpack 350 which always fits.

 

 

And here is how it looks when I’m all setup ( in this case at a Regus Office near a client at Bank station London )

 

 

Of course in addition to this I carry pens, pad, spork, small flask, umbrella and the usual ‘man bag’ stuff, but that was all boring