HCL Connections 6.5 Installation – Community Highlights Database Missing

When creating Databases for HCL Connections 6.5 using the Database Wizard, you will find that the database “XCC” for the “Community Highlights” Application is missing. If you continue without it and you want to install all Connections Applications, you will get an error during the installation process stating that the Database for “Community Highlights” Application is missing.

To remedy this problem you will have to create the database manually, to do that, just extract the Package containing the Connections 6.5 Wizards, and navigate to the folder with the “icec” database scripts, for example:

“C:\temp\Connections_6.5_Wizards\Wizards\connections.sql\icec\db2”

In case you are using DB2, start the DB2 CMD as the DB2 Administrator and run the following commands:

db2 -tvf createDb.sql

db2 -tvf appGrants.sql

Please note that the script “appGrants.sql” allows the default Connections User, “LCUSER” to access the database. If you want to use this user to access the database, you are good to go.

IBM Connections – How to change Personal Notification Preferences for all Users

You may find yourself in a situation where you need to change the personal IBM Connections E-Mail Notification Settings for all Users. IBM Connections Users have the following options to choose from:

There is a set of Default Settings which is applied to every user, as soon as the TDI-Sync runs, and the user is created in a database. So, the first thing to do, is to change these default settings. The official documentation does a pretty good job explaining the possibilities you have here, like Locking the User Notification Preferences, and the steps needed to apply the changes, so I won´t go in greater detail on those here. It is important to keep in mind, except if you are not “locking” the preferences, the preferences on Email notifications will only be set automatically for users which are created in Connections, as a result of the TDI Assembly Line, after you make those changes. In order to test the modifications made, you could use the “Restore Defaults” option on the Notification Preferences page.

But changing the default settings will not have any impact on the existing users, to change the notification options for them, you need to take a different approach.

I wanted to change the notification settings for all Users to “No Email”, but still make sure that the users can change those settings if they wish to, so simply locking those settings was not an option. I tried locking the Notification settings for all users to “No Email”, but after removing the lock, the setting would just be reverted to the setting done earlier. It turns out that there is no other possibility than to change the records in the database directly. A word of warning, you should contact the IBM Support before proceeding, because changing the records in the Connections Databases directly is not supported, I also recommend a database backup. 🙂

I am using for IBM Data Studio Software, but any other Database Software which allows you to connect to your type of database will suffice.

The Database Tables in which we need to change the records are “HOMEPAGE.EMD_RESOURCE_PREF” and “HOMEPAGE.EMD_EMAIL_PREFS” (in the HOMEPAGE database), a big thumbs up to Martin Schmidt for saving me countless hours searching for the correct table. Both of these tables have “PERSON_ID” column, so I searched in the “HOMEPAGE.PERSON” table for my test accounts in order to find out the “PERSON_ID” values of these accounts, so I could reproduce the desired state of one account and make sure that the changes made are valid and as I wanted them set. I’ve done that with the following SQL statement:

select *

from empinst.employee

where prof_display_name = ‘Milan Matejic’;

After getting the right “PERSON_ID”, I could check the Records in “HOMEPAGE.EMD_RESOURCE_PREF” and “HOMEPAGE.EMD_EMAIL_PREFS” tables.

— Email Notifications

select *

from homepage.emd_resource_pref

where person_id = ’13a96f01-37d8-4674-ae51-f6d2d19ee8e9′;

— Direct Emails

select *

from homepage.emd_email_prefs

where person_id = ’13a96f01-37d8-4674-ae51-f6d2d19ee8e9′;

The columns we need to change are “SEND_DIRECTED” in “HOMEPAGE.EMD_RESOURCE_PREF” table and “RESOURCE_TYPE” in “HOMEPAGE.EMD_EMAIL_PREFS” table. Setting “SEND_DIRECTED” to ‘0’ will result in deactivating “Receive notifications from other people by email”. For “RESOURCE_TYPE” we have the following options:

‘4’ –> Weekly

‘3’ –> Daily

‘2’ –> Individual

‘1’ –> Deactivated

In my case I wanted to set the notifications to “No Email” for all users, so I went with setting ‘1’ in all rows in the table.

Note: Before making changes for all users, you should make the change just for one test user, to make sure that there are no problems and that your statement is working properly.

I accomplished the task with the following update statements:

—– Update Statement HOMEPAGE.EMD_RESOURCE_PREF

update homepage.emd_resource_pref

set frequency_type = ‘1’

where frequency_type != ‘1’;

—– Update Statement HOMEPAGE.EMD_RESOURCE_TYPE

update homepage.emd_email_prefs

set send_directed = ‘0’

where send_directed != ‘0’;

Before and after running the update statements, you can verify the changes by looking on the number of rows with certain settings:

—- Search for Frequency Type

select *

from homepage.emd_resource_pref

where frequency_type = ‘1’;

—- Search for Send Direct

select *

from homepage.emd_email_prefs

where send_directed = ‘0’;

After running the update statements, just refresh your browser, there is no need to restart any applications or components.

As a bonus, I created an Enhancement Request, so hopefully in future we don’t need to change the database records for this, so please vote for it by accessing the IBM Connections Product Ideas Lab.

DB2 Commands Cheat Sheet

I am frequently using DB2 for Traveler HA Pools, Connections and Sametime databases and I am very happy with it. The performance and stability is great. In a small environment, there is not very much you have to do after you install DB2 and create databases you need.

But like in any system there are some basic tasks you need to do on a regular basis to ensure that everything runs smoothly. To help me achieve this I have a list of basic commands which I use frequently, I think that everybody who uses DB2 in their environment will know of them:

• DB2ICRT <NewInstanceName> –> Create new Instance
• DB2IDROP <InstanceName> –> Deletes the Instance
• DB2 LIST DB DIRECTORY –> Shows the directory of an instance
• DB2 LIST DB DIRECTORY ON D:\ –> Shows the directory of an instance only on D: Drive
• DB2 CREATE DB <dbName> –> creates Database
• DB2 CREATE DB <dbName> ON E:\ –> Creates Database on E: Drive
• DB2 DROP DB <dbName> –> Deletes a Database

• db2set -all –> Lists important paths and configuration parameters
• db2 get dbm cfg –> Shows the database manager configuration
• db2 get db cfg –> Shows a database specific configuration

• db2 deactivate database <dbname> –> deactivate a database
• db2 activate database <dbname> –> activate a database
• backup database “traveler” online to “C:\Backup” compress include logs without prompting  –> Backup (Online, including logs, with compression)

Commands for HADR mode

• db2pd -db <dbname> -hadr –> Shows the HADR database state
• db2 takeover hadr on db <dbname> –> Takeover

Status Check

• db2 get connection state –> Shows if a database is “connectable”
• db2 list active databases –> Lists all active databases
• db2 list applications –> Lists all Applications which are currently connected to the databases

• db2 prune history yyyymmdd and delete –> Deletes achive logs
• db2 list history backup all for db “db_name”
  –> Lists database backup history and the locations of the backup files

Version check

• db2licm -v
  
• db2licm -l
  
• db2level
  

Sure, you can do all this by using the IBM Data Studio, which is free to use, but sometimes you can be a lot faster by using the command line.

If you are missing some important commands which you are using frequently, please feel free to mention them in comments, I will then include them in the list.

DB2 HADR Configuration for STSC Database

At this point you should already have a Sametime System Console and two DB2 Servers installed. One of the DB2 Servers will be used as a DB2 Primary Instance and the other one as the Secondary. I strongly encourage you to read the official IBM documentation before implementing the following configuration.

In the environment where I have done this I had the following components installed:

• IBM DB2 10.1 (without any Fix Packs)
  
• WebSphere Network Deployment 8.5.5.0
  
• Sametime System Console 9 Hot Fix 1
  
• Sametime Community Server 9 Hot Fix 1
  

I also used the IBM Data Studio. You can install the newest version on your client, just make sure that you connect to the “STSC” database as a user which has the instance owner role.

The steps to configure HADR for STSC database are as following:

1. Stop Sametime System Console.
2. Back up all Databases which are running on your DB2 Server including STSC Database.
3. You should also have a backup of the whole server where DB2 is running or a configuration backup.
4. Log in as “db2admin” or another User which has the instance owner role.
5. The first thing which we need to do is to configure logging due to the fact that DB2 HADR relies on it to keep the secondary database in sync with primary.

1. Manually create a folder in the file system where you would like to store the logging data.

1. Select “Archive” and then browse to the location of the folder you created.

1. You can keep the default values.

1. I have kept the default value for the destination of active logs.

1. The wizard creates a DB backup before applying the configuration changes, here you can select where you want to store the Backup.

Note: You can copy all commands and create a script of them to run it in another environment.

Commands:

CONNECT TO STSC;

QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;

UNQUIESCE DATABASE;

CONNECT RESET;

CONNECT TO STSC;

UPDATE DATABASE CONFIGURATION USING logarchmeth1 “DISK:C:\db2archlog_stsc” newlogpath “C:\STSC\DB2\NODE0000\SQL00002\LOGSTREAM0000” logprimary 13 logsecond 12 logfilsiz 1024 LOGINDEXBUILD OFF ;

CONNECT RESET;

DEACTIVATE DATABASE STSC;

BACKUP DATABASE STSC TO “C:\STSC\Backup” EXCLUDE LOGS WITHOUT PROMPTING;

1. At the end select “Run”.

After the process is finished you may get a warning which states that you need to deactivate and activate the database in order for changes to take effect.

You can do this by issuing the following commands in DB2 CMD.

1. Configure HADR

Now we can actually configure HADR.

1. Connect to the STSC database.

We can now use “Online Backup” because we have configured archive logging, that is why it is quite handy to have archive logging configured even if you don´t want to configure HADR.

After going any further you should check the port numbers which are used by other databases. You need to make sure that you do not define ports for the new STSC database HADR configuration which are already used by other HADR configurations on the DB2 instance.

1. Open db2 admin cmd.
2. Connect to the specific database by issuing “db2 connect to <database_name>”.
3. Then enter the “db2 get db cfg” command.

Note: Make sure you do not use the same ports as defined in the output.

Note: Mostly I use “Synchronous” HADR synchronization mode due to the fact that all my servers are in the same LAN. Depending on your network configuration you can choose which one works best for you.

1. In the “Client Reroute Options” enter the Hostname of the secondary server as well as the port number on which the secondary database should be accessible by the Sametime System Console in a case of a failover.

2. Create a connection to the standby instance.

3. Create a new Connections Profile to the standby server and select the most recent backup of the STSC database.

4. Select a directory on the standby system where the backup file will be copied. If the specific folder does not exist, create it manually, otherwise you will get an error. J

5. Select a port for the HADR service on the standby system.

6. You will need to specify the “Client Reroute Options” again, this time you will need the to specify the primary server.

7. After you are happy with the configuration, click run.

Note: you can ignore the following warnings.

These Commands were shown in the Data Studio as a result from our configuration:

RESTORE DATABASE STSC FROM “C:\STSC\Backup” TAKEN AT 20150827095005 INTO STSC REPLACE HISTORY FILE WITHOUT PROMPTING;

–Primary Database Configuration Commands;

— Configure database for client reroute – <Primary_Host>(DB2) – STSC;

UPDATE ALTERNATE SERVER FOR DATABASE STSC USING HOSTNAME <Secondary_Host> PORT 50000;

— Update HADR configuration parameters on database – <Primary_Host>(DB2) – STSC;

UPDATE DB CFG FOR STSC USING HADR_LOCAL_HOST <Primary_Host>;

UPDATE DB CFG FOR STSC USING HADR_LOCAL_SVC 56001;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_HOST <Secondary_Host>;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_SVC 56002;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_INST DB2;

UPDATE DB CFG FOR STSC USING HADR_TIMEOUT 120;

UPDATE DB CFG FOR STSC USING HADR_TARGET_LIST <Secondary_Host>:56002;

UPDATE DB CFG FOR STSC USING HADR_SYNCMODE SYNC;

UPDATE DB CFG FOR STSC USING HADR_PEER_WINDOW 0;

UPDATE DB CFG FOR STSC USING HADR_SPOOL_LIMIT 0;

UPDATE DB CFG FOR STSC USING HADR_REPLAY_DELAY 0;

UPDATE DB CFG FOR STSC USING BLOCKNONLOGGED NO;

STOP HADR ON DATABASE STSC;

–Standby Database Configuration Commands for <Secondary_Host>-DB2;

— Configure database for client reroute – <Secondary_Host>(DB2) – STSC;

UPDATE ALTERNATE SERVER FOR DATABASE STSC USING HOSTNAME <Primary_Host> PORT 50000;

— Update HADR configuration parameters on database – <Secondary_Host>(DB2) – STSC;

UPDATE DB CFG FOR STSC USING HADR_LOCAL_HOST <Secondary_Host>;

UPDATE DB CFG FOR STSC USING HADR_LOCAL_SVC 56002;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_HOST <Primary_Host>;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_SVC 56001;

UPDATE DB CFG FOR STSC USING HADR_REMOTE_INST DB2;

UPDATE DB CFG FOR STSC USING HADR_TIMEOUT 120;

UPDATE DB CFG FOR STSC USING HADR_TARGET_LIST <Primary_Host>:56001;

UPDATE DB CFG FOR STSC USING HADR_SYNCMODE SYNC;

UPDATE DB CFG FOR STSC USING HADR_PEER_WINDOW 0;

UPDATE DB CFG FOR STSC USING HADR_SPOOL_LIMIT 0;

UPDATE DB CFG FOR STSC USING HADR_REPLAY_DELAY 0;

UPDATE DB CFG FOR STSC USING BLOCKNONLOGGED NO;

— Start HADR on standby database – <Secondary_Host>(DB2) – STSC;

DEACTIVATE DATABASE STSC;

START HADR ON DATABASE STSC AS STANDBY;

— Start HADR on primary database – <Primary_Host>(DB2) – STSC;

START HADR ON DATABASE STSC AS PRIMARY;

7. Post Configuration and Verification

Verify that the HADR is working.

Primary server:

Standby server:

Check if you can connect to the database.

You can restart the system just to make sure that everything is ok after the restart.

Primary:

Stand by:

Primary:

Standby:

8. Test a Failover

Standby:

Primary:

9. WebSphere & SSC Configuration

Alternate Server Name: <Secondary_Host>

Alternate Port Name: 50000

Do this for every DB2 Datasource/Database configured as HADR.

Restart the Application Server. à In our Case, the SSC.

1. Check and verify

Start the application, SSC.

Primary:

Our Application Server has the IP of 192.168.13.29

Failover to Standby and check if the Application can connect to the Standby Server.

If you get an error message, which refers to db2 database, in SSC just refresh or click on the same menu once again.

Some useful information regarding DB2 HADR: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/DB2HADR/page/HADR+commands

We have our DB2 Servers running in the Configuration described above for quite some time and we didn’t have any issues with it.