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 ‘]’

 

Advertisements

DB2 HADR – clustered Meeting servers

I needed to create a cluster of Sametime Meeting servers to ensure high availability. With WebSphere (like Domino) this is relatively easy but what about the database? DB2 HADR (High Availability and Disaster Recovery) ensures that two databases are kept in sync with each other in case one of the databases fails.

HADR does this by creating a primary and standby database. The primary is always in use whilst the standby is kept in sync using internal processes to ship database log buffers to it. A process at the standby server then replays the log records directly to the standby database. If the primary fails the standby database can be promoted to primary to take over responsibilities.

  • This is not a seamless fail over, it requires manual interaction to promote the standby database.
  • To achieve seamless fail over other tools such as TSA or HACMP must be used.
  • The standby cannot be accessed, only the primary can be read or written to.
  • In DB2 v9.7 the standby can be used for reads but if you cannot write to it I am unsure of the effect this might have on Sametime.

Instead of installing the limited use (CZLF7ML) version of DB2 for Linux I opted to use the full version (C1X0UEN) which gave me greater control over user accounts and install directory. I decided to use the GUI to set up HADR, good thing about it is that at almost every step you can see the command that the GUI is compiling should you wish to script it next time round.

Screen shots detailing HADR set up.

The deployment plans for the Meeting servers both have to point to the same DB2 fqhn as defined in the “Connect to DB2 databases” in the SSC. If the primary DB2 server should fail WebSphere will not be able to read/write to it even if the standby has been promoted to the primary. To fix this Automatic Client Reroute can be used within WebSphere.

You need to make each DB2 server aware of the other so that when a client connects it knows how to reroute it should it fail. You need to run the following command on each server referencing the other in the command line.

./db2 update alternate server for database STMS using hostname db2.acme.com port 50000

Log in to the SSC and go to Resources – JDBC – Data sources. You will see a number of JDBC data sources, normally two for each Meeting node and two for the cluster.

Click on each (checking at the bottom of the page that it references STMS and not STSC) and then select “WebSphere Application Server data source properties” on the right hand side.

Under “DB2 automatic client reroute options” you have options of adding alternate servers and the ports they listen on. You configure your second database here. Make sure the changes synchronise with your nodes and that the applications servers are restarted afterwards.

If your primary database fails WebSphere will then connect to the alternate DB2 server as long as the alternate server has been made the primary.