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.

Failure creating STPROXY HADR database

As part of my plans to upgrade a customer’s environment for Sametime 8.5.1 to 8.5.2.1 I wanted to get ahead of myself by creating the database used for iOS devices. When trying to create the database using the createProxyDb.sh I got a failure.

“SQL1363W warning: One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, all applications must disconnect from this database before the changes become effective.”

I thought that maybe a stop and start of DB2 was required but before failing over the two HADR members I Googled and found Failure creating Sametime Proxy database on DB2 HADR system which describes what is (not) happening and a resolution….. for Windows

I’m not overly hot with writing shell scripts but I managed to get there in the end on a RHEL server and have pasted the amended shell script for those who come across this.

PS – I ran the shell script with the following c0mmand so that I could see all the output.

bash -xv ./createProxyDbHADR.sh

#
# US Government Users Restricted Rights – Use, duplication or
# disclosure restricted by GSA ADP Schedule Contract with
# IBM Corp.
#
# *****************************************************************

# This script will create the database and run the schema creation script

# exec 1>/dev/null

PATH=/usr/bin:/usr/ucb:$PATH; export PATH
+ PATH=/usr/bin:/usr/ucb:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/opt/IBM
/db2/V9.5/db2inst1/sqllib/bin:/opt/IBM/db2/V9.5/db2inst1/sqllib/adm:/opt/IBM/db2
/V9.5/db2inst1/sqllib/misc:/opt/IBM/db2/V9.5/db2inst1/sqllib/db2tss/bin:/opt/IBM
/db2/V9.5/db2inst1/bin
+ export PATH
PRG=`basename $0`
basename $0
++ basename ./createProxyDbHADR.sh
+ PRG=createProxyDbHADR.sh

STDDL=proxyServer.ddl
+ STDDL=proxyServer.ddl

if [ “$2” = “” ]; then
        echo “Insufficient arguments”
        echo
        echo “Usage: $PRG DatabaseName DbUserName”
        echo ”  DatabaseName is the name of the database this script will”
        echo ”  create.  DbUserName is the name of the database user that”
        echo ”  will be used to connect to the DB2 server from the”
        echo ”  Sametime Proxy Server.”
        exit 1
fi
+ ‘[‘ db2inst1 = ” ‘]’

if [ ! -f “${STDDL}” ]; then
        echo “${STDDL} is missing from the current directory”
        echo “Aborting database creation”
        exit 2
fi
+ ‘[‘ ‘!’ -f proxyServer.ddl ‘]’

echo “Processing…”
+ echo Processing…
Processing…
echo
+ echo

db2 CREATE DATABASE $1 USING CODESET UTF-8 TERRITORY US COLLATE USING UCA400_NO
+ db2 CREATE DATABASE STPROXY USING CODESET UTF-8 TERRITORY US COLLATE USING UCA
400_NO
DB20000I  The CREATE DATABASE command completed successfully.
#       AUTOCONFIGURE USING MEM_PERCENT 50 TPM 1500 \
#       ADMIN_PRIORITY PERFORMANCE ISOLATION UR APPLY DB AND DBM
command_return_code=$?
+ command_return_code=0
if [ ${command_return_code} -ne 0 ]; then
        echo
        echo “Database creation failed”
        exit ${command_return_code}
fi
+ ‘[‘ 0 -ne 0 ‘]’

# adding deactivate as needed for HADR installation
db2 deactivate database $1 user db2inst1 using ********
+ db2 deactivate database STPROXY user db2inst1 using ********
SQL1496W  Deactivate database is successful, but the database was not
activated.

db2 update db cfg for $1 using LOGSECOND 20
+ db2 update db cfg for STPROXY using LOGSECOND 20
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
#command_return_code=$?
#if [ ${command_return_code} -ne 0 ]; then
#       echo
#       echo “Database creation failed”
#       exit ${command_return_code}
#fi

db2 update db cfg for $1 using LOGFILSIZ 10000
+ db2 update db cfg for STPROXY using LOGFILSIZ 10000
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.
#command_return_code=$?
#if [ ${command_return_code} -ne 0 ]; then
#       echo
#       echo “Database creation failed”
#       exit ${command_return_code}
#fi

# adding activate as needed for HADR installation
db2 activate database $1 user db2inst1 using ********
+ db2 activate database STPROXY user db2inst1 using ********
DB20000I  The ACTIVATE DATABASE command completed successfully.

db2 CONNECT to $1
+ db2 CONNECT to STPROXY

   Database Connection Information

 Database server        = DB2/LINUX 9.5.1
 SQL authorization ID   = DB2INST1
 Local database alias   = STPROXY

command_return_code=$?
+ command_return_code=0
if [ ${command_return_code} -ne 0 ]; then
        echo
        echo “Database creation failed”
        exit ${command_return_code}
fi
+ ‘[‘ 0 -ne 0 ‘]’

if [ “$2” = `whoami` ]; then
        echo “*** $PRG:  skipping granting privileges to self”
        echo
else
        db2 GRANT DBADM ON DATABASE TO USER $2
        command_return_code=$?
        if [ ${command_return_code} -ne 0 ]; then
                echo
                echo “Database creation failed”
        exit ${command_return_code}
        fi
fi
whoami
++ whoami
+ ‘[‘ db2inst1 = db2inst1 ‘]’
+ echo ‘*** createProxyDbHADR.sh:  skipping granting privileges to self’
*** createProxyDbHADR.sh:  skipping granting privileges to self
+ echo

db2 CREATE BUFFERPOOL STPBP32K ALL NODES \
        SIZE 750 AUTOMATIC PAGESIZE 32768
+ db2 CREATE BUFFERPOOL STPBP32K ALL NODES SIZE 750 AUTOMATIC PAGESIZE 32768
DB20000I  The SQL command completed successfully.
command_return_code=$?
+ command_return_code=0
if [ ${command_return_code} -ne 0 ]; then
        echo
        echo “Database creation failed”
        exit ${command_return_code}
fi
+ ‘[‘ 0 -ne 0 ‘]’

db2 CREATE LARGE TABLESPACE STP32K \
        PAGESIZE 32768 \
        MANAGED BY DATABASE \
        USING “( FILE ‘STPDATA32K_1’ 200M, FILE ‘STPDATA32K_2’ 200M)” \
        AUTORESIZE YES \
        INCREASESIZE 500M \
        MAXSIZE NONE \
        BUFFERPOOL STPBP32K
+ db2 CREATE LARGE TABLESPACE STP32K PAGESIZE 32768 MANAGED BY DATABASE USING ‘(
 FILE ‘\”STPDATA32K_1’\” 200M, FILE ‘\”STPDATA32K_2’\” 200M)’ AUTORESIZE YES
 INCREASESIZE 500M MAXSIZE NONE BUFFERPOOL STPBP32K
DB20000I  The SQL command completed successfully.
command_return_code=$?
+ command_return_code=0
if [ ${command_return_code} -ne 0 ]; then
        echo
        echo “Database creation failed”
        exit ${command_return_code}
fi
+ ‘[‘ 0 -ne 0 ‘]’

db2 CREATE TEMPORARY TABLESPACE STPTEMP32K \
        PAGESIZE 32768 \
        MANAGED BY DATABASE \
        USING “( FILE ‘STPTEMPDATA32K_1’ 10M, FILE ‘STPTEMPDATA32K_2’ 10M)” \
        AUTORESIZE YES \
        INCREASESIZE 5M \
        MAXSIZE NONE \
        BUFFERPOOL STPBP32K
+ db2 CREATE TEMPORARY TABLESPACE STPTEMP32K PAGESIZE 32768 MANAGED BY DATABASE
USING ‘( FILE ‘\”STPTEMPDATA32K_1’\” 10M, FILE ‘\”STPTEMPDATA32K_2’\” 10M)’
AUTORESIZE YES INCREASESIZE 5M MAXSIZE NONE BUFFERPOOL STPBP32K
DB20000I  The SQL command completed successfully.
command_return_code=$?
+ command_return_code=0
if [ ${command_return_code} -ne 0 ]; then
        echo
        echo “Database creation failed”
        exit ${command_return_code}
fi
+ ‘[‘ 0 -ne 0 ‘]’

db2 -tvf ${STDDL}
+ db2 -tvf proxyServer.ddl
CREATE TABLE STPROXY.STP_NWAYINV  ( NWAY_USERID VARCHAR(128) NOT NULL, NWAY_SEND
ERID VARCHAR(128) NOT NULL, NWAY_SENDERNAME VARCHAR(128) NOT NULL, NWAY_TIMESTAM
P TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP, NWAY_TOPIC VARCHAR(256) NOT
 NULL, NWAY_MESSAGE VARCHAR(1024) NOT NULL, NWAY_PLACEID VARCHAR(256) NOT NULL,
CONSTRAINT NWAY_PK PRIMARY KEY (NWAY_USERID, NWAY_PLACEID, NWAY_TIMESTAMP) ) IN
STP32K
DB20000I  The SQL command completed successfully.

CREATE TABLE STPROXY.STP_IMMSG  ( IM_USERID VARCHAR(128) NOT NULL, IM_PARTNERID
VARCHAR(128) NOT NULL, IM_PARTNERUID VARCHAR(128) NOT NULL, IM_PARTNERNAME VARCH
AR(256) NOT NULL, IM_TEXT CLOB (1M) COMPACT NOT NULL, IM_PLACEID VARCHAR(256) WI
TH DEFAULT NULL, IM_TIMESTAMP TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP,
 CONSTRAINT IM_PK PRIMARY KEY (IM_USERID, IM_PARTNERID, IM_TIMESTAMP) ) IN STP32
K
DB20000I  The SQL command completed successfully.

CREATE TABLE STPROXY.STP_ANNC  ( ANNC_USERID VARCHAR(128) NOT NULL, ANNC_SENDERI
D VARCHAR(128) NOT NULL, ANNC_MESSAGE CLOB (1M) COMPACT NOT NULL, ANNC_RESPONSE_
ALLOWED SMALLINT NOT NULL WITH DEFAULT 0, ANNC_SENDERNAME VARCHAR(128) NOT NULL,
 ANNC_TIMESTAMP TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP, CONSTRAINT AN
NC_PK PRIMARY KEY (ANNC_USERID, ANNC_SENDERNAME, ANNC_TIMESTAMP) ) IN STP32K
DB20000I  The SQL command completed successfully.

CREATE TABLE STPROXY.STP_DEVICETOKENS  ( DT_DEVICETOKEN VARCHAR(128) NOT NULL, C
ONSTRAINT DEVICETOKEN_PK PRIMARY KEY (DT_DEVICETOKEN) ) IN STP32K
DB20000I  The SQL command completed successfully.

command_return_code=$?
+ command_return_code=0
if [ ${command_return_code} -ne 0 ]; then
        echo
        echo “Database creation failed”
        exit ${command_return_code}
fi
+ ‘[‘ 0 -ne 0 ‘]’

db2 disconnect $1
+ db2 disconnect STPROXY
DB20000I  The SQL DISCONNECT command completed successfully.
command_return_code=$?
+ command_return_code=0
if [ ${command_return_code} -ne 0 ]; then
        echo
        echo “Database creation failed”
        exit ${command_return_code}
fi
+ ‘[‘ 0 -ne 0 ‘]’