Cannot edit Media Manager policies due to incomplete xml data in DB2

I had a few problems with a customer’s deployment of Sametime 9 which probably come down to deployment plans and the order of the servers being installed.

During installation I had problems detailed in “System version is null” on new IBM Sametime Video Manager installation which forced me to uninstall the VMGR and install again with a new deployment plan. The outcome of this was that I could not administer the default policies nor create new Media Manager policies in the SSC, I saw the following error, “AIDSC#####: Could not connect to Sametime Video Manager. Either VMgr is not installed or server is not up. Please retry after installing VMgr or starting it.”

1

I saw in the deployment manager SystemOut.log “[17/10/14 17:02:04:101 BST] 00000220 SametimeVmgrU E   Forbidden” but nothing much else to write home about.

I raised a PMR with IBM and gathered some trace and sent it off. The PMR ended up with Ankit Vij in L3 who worked as a developer on the propagation of policies from the SSC to the VMGR.

After some to’ing and fro’ing it was identified that there were missing credentials in the DEPLOYMENT table of the SSC database. In the DEPCONF  column of the Conference Manager deployment plan lies XML data. In the data are two fields VMGRUSER and VMGRPASSWORD. In the customer’s data these values were empty, this is why the SSC couldn’t access the VMGR’s policies.

There are few ways in which to edit the data, Data Studio is nice and easy and can export the table, edit it and then import it again in no time at all but as I was accessing their environment using Citrix this wasn’t an option because I couldn’t install any software. Using the CLI was the only way to do it.

My first attempts of using the DB2 EXPORT command failed because the tables have LOBs which are truncated when you export the data to a csv file. The way around it is to export to a csv file but also export all the data to LOB files. This can be achieved using the following command.

C:\Windows\system32>db2 “export to d:\export\deployment.csv of del lobs to d:\export\lobs\ modified by lobsinsepfiles select * from ssc.deployment”
SQL3104N  The Export utility is beginning to export data to file
“d:\export\deployment.csv”.

This produces a csv. Where there was LOB data a .lob file is produced and the csv details which number .lob file holds the information for that particular entry.

Once I had found the .lob file referenced for the Conference Manager deployment plan in the DEPCONF  column I had to copy the contents of the .lob to a new text file.

The VMGRUSER and VMGRPASSWORD values were empty so I then updated them with wasadmin (could be admin/admin) and the password associated with it.

Next I had to add to the beginning of the xml data UPDATE SSC.DEPLOYMENT SET DEPCONF=’ and to the end ‘ WHERE DEPID=’14908a6aa1d-00000000000a-MediaDep’

The DEPID is easy to come about and is listed DEPID column for the Conference Manager deployment plan.

The end result is a single line containing 18000+ characters looking something like this.

UPDATE SSC.DEPLOYMENT SET DEPCONF='<?xml version=”1.0″ encoding=”UTF-8″?>………………………….</parameter></parameters></Config>’ WHERE DEPID=’14908a6aa1d-00000000000a-MediaDep’

As the command was too large to paste into the CLI I saved it to a .sql file.

I stopped STConsoleServer, the node agent and the deployment manager.

Before changing the database I needed to back it up.

C:\Windows\system32>db2 backup database stsc
SQL1035N  The database is currently in use.  SQLSTATE=57019

I then needed to force the application connections from the database.

C:\Windows\system32>db2 list applications

Auth Id  Application    Appl.      Application Id                                                 DB       # of
Name           Handle                                                                    Name    Agents
——– ————– ———- ————————————————————– ——– —–
DB2ADMIN db2jcc_applica 41961      192.168.x.x.49442.141124093130                              STSC     1
DB2ADMIN db2jcc_applica 45374      192.168.x.x.61230.141125142939                              STMS     1
DB2ADMIN db2jcc_applica 45483      192.168.x.x.61666.141125152718                              STMS     1
DB2ADMIN db2jcc_applica 41949      192.168.x.x.49385.141124093116                              STMS     1

C:\Windows\system32>db2 force application(41961)
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

After all applications are disconnected I could run the backup.

 C:\Windows\system32>db2 backup database stsc

Backup successful. The timestamp for this backup image is : 20141125154621

C:\Windows\system32>db2 connect to stsc

   Database Connection Information

 Database server        = DB2/NT64 10.1.0
 SQL authorization ID   = DB2ADMIN
 Local database alias   = STSC

At this point I am going to run the UPDATE command using the .sql file I created.

C:\Windows\system32>db2 -vf C:\DB2\ssc.sql

DB20000I  The SQL command completed successfully.

Normally I would run db2 -tvf but that didn’t work, I think because I didn’t use semicolons for delimiters in the .sql file. Anyway, it worked.

I started the deployment manager, node agent and STConsoleServer and I could now edit the Media Manager policies.

Many thanks to Imran and Ankit at IBM for helping me through this frustrating but interesting problem.

Sametime and the mystery surrounding Managed Settings

I have been working with a customer introducing Connections 4.0, Sametime Proxy and moving their current two Community servers away from native Domino to AD LDAP.

We are now at the point where as we are looking at migrating the users from their current Community server to their new one. Normally DNS could be used to do this but since the authentication method is changing too then some further steps are required. To do this two approaches are required 1) the Sametime client needs to always pull the buddy list from the server and 2) the client needs to be redirected to the new server.

This could be done with desktop policies in the address book but these are flaky and do not work as they should often enough. Personally I like the way that an xml file can be used and a Sametime policy created to reference it for a group or list of people. It keeps the actions in Sametime and separates it from the customers desktop policies which all to often don’t work.

The first step is well documented and is performed using managed-settings.xml. There are dozens of blogs from people detailing how to do this. Here is the contents of the customer’s managed-settings.xml:

<ManagedSettings>
<settingGroup name=”com.ibm.collaboration.realtime.imhub” lastModDate=”20130821T115100Z”>
<setting name=”buddyListConflictPref” value=”replaceLocal” isLocked=”true”/>
<setting name=”showBuddyListConflictDialog” value=”false” isLocked=”true”/>
<setting name=”showExternalModificationDialog” value=”false” isLocked=”false”/>
</settingGroup>
</ManagedSettings>

This changes the following setting in the client. This is important so that their local buddy list is always replaced with the server version which will have users listed in a different format.

2013-08-30_114418

The customer at present is not converting the user’s buddy list. My experience of using the name conversion tool is bad and is inconsistent although there have been a number of new fixes in the recent past so may be the conversion is better? They could also use a 3rd party such as Instant Technologies to convert the buddy list for them though there is a cost associated but there is a guarantee to the conversion accuracy.

The redirection of the client is where the documentation is confusing.

Below is the output that worked for me. I had to raise a PMR because my various attempts would redirect my client but would not blank out the user name. This would confuse users as they would attempt to log in using their Domino credentials rather than their AD ones.

The first managed-community-configs.xml below is what I created based on the documentation available and the end result was that the client would redirect but the user name was still populated which should not happen when using “createNewConfig.”

In my case there are two addresses that could be used by users to log in “oldserveralias1.domain.com” and “oldserveralias2.domain.com.” Listing both of them ensures that whatever DNS alias is used will be redirected.

<?xml version=”1.0″ encoding=”UTF-8″?>
<managed-communities>
<managed-community id=”oldserveralias1.domain.com” host=”oldserveralias1.domain.com” newHost=”newserver.domain.com”/>
<managed-community id=”oldserveralias2.domain.com” host=”oldserveralias2.domain.com” newHost=”newserver.domain.com”/>
<managed-community-action type=”update” createNewConfig=”true” managed-community-id=”oldserveralias1.domain.com”/>
<managed-community-action type=”update” createNewConfig=”true” managed-community-id=”oldserveralias2.domain.com”/>
</managed-communities>

With a little help from Cormac O’Leary and L3 the proper syntax was provided which is below.

<?xml version=”1.0″ encoding=”UTF-8″?>
<managed-communities>
<managed-community id=”oldserveralias1.domain.com” host=”newserver.domain.com”/>
<managed-community id=”oldserveralias2.domain.com” host=”newserver.domain.com”/>
<managed-community-action type=”reset” createNewConfig=”true” managed-community-id=”oldserveralias1.domain.com”/>
<managed-community-action type=”reset” createNewConfig=”true” managed-community-id=”oldserveralias2.domain.com”/>
</managed-communities>

Sametime policies

Thankfully I knew that I was going to come across one of these problems. When upgrading the policies need to also be upgraded and the wiki describes the process well. Prior to starting the upgrade I tested the approach of editing the LDAP document in the SSC but got the following error

??? nl.ErrorResourceMessages|CWWIM5044E The ******* repository cannot be deleted because it has at least one base entry that is referenced by a realm. [en] ???

To enable SSO to work across Portal, Connections, Domino and Sametime I had to configure a realm which was in line with the other environments as well as make some modifications to my wimconfig.xml. The error above is telling me that it doesn’t like the fact that the realm is different to defaultWIMFileBasedRealm, (more information about how to do this can be found in this Technote).

This had me concerned that this could jeopardize the upgrade so I read around and found the following IBM documents which are worth bearing in mind, LO70452 and LO56702. The former suggested that I change the realm back to defaultWIMFileBasedRealm as it breaks the LDAP guided activity which is what I need to step through. By changing the realm name I saw synchronisation errors with the nodes but that was quickly rectified after the policies were upgraded and the realm reverted to what it was. I ensured that I shut down all the nodes and their node agents and ensured synchronisation was working.

The other problem I came across was just after the clustered Meeting servers were upgraded to 8.5.2 is detailed in After upgrading to 8.5.2, users are no longer able to enter Sametime Meeting Rooms. The instruction in the Technote suggest that you run some DB2 commands to purge the contents of the POLICY.TEMPLATE and POLICY.ASSIGNMENT tables in STSC database. That is all good and well but you will lose ALL your policies so be very careful about doing that.

You can of course take a back up of the database as well as manually making a not of all your policies and their settings.
Below are a couple of commands that may make it easier for you.

db2 “select count(*) as rows from POLICY.ASSIGNMENT” and db2 “select count(*) as rows from POLICY.TEMPLATE”
The above will allow you to check that the number of rows and when run afterwards it checks that it has worked.

This allows you to dump the data out to a text file. This doesn’t give you the configuration of the policy just those who are assigned to them
db2 “select * from POLICY.TEMPLATE” > /tmp/policy.template.original.txt
db2 “select * from POLICY.ASSIGNMENT” > /tmp/policy.assignment.original.txt

This is the command to purge the contents
db2 “delete from POLICY.TEMPLATE”
db2 “delete from POLICY.ASSIGNMENT”

It worked for me BUT I had to recreate the policies.