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.