Upgrading Lotus Connections Profiles to 1.0.2

Update: We found the original update files!

In the last week, I've been upgrading several Lotus Connections 1.0.1 installations to version 1.0.2.

As part of this process, there is a missing sql script needed to upgrade the Profiles database to include a few column changes and updates. The only script that is provided by the update is peopledb.db2, which is used to create a whole new Profiles schema using create table and related statements. This script will not work to update existing data tables since you would have to drop your tables before running peopledb.db2.

I resolved this issue by comparing the new table definitions in peopledb.db2 to the existing Lotus Connections 1.0.1 tables.

I have listed below a simple version of my script. The full upgrade102.sql also includes the index and other updates to the EMPLOYEE and EMP_DRAFT tables to make sure we get anything added in 1.0.2.

CONNECT TO PEOPLEDB;

SET INTEGRITY FOR EMPINST.EMPLOYEE OFF;

ALTER TABLE EMPINST.EMPLOYEE
ADD COLUMN PROF_UID_LOWER VARCHAR (36) GENERATED ALWAYS AS (LOWER(PROF_UID))
ALTER COLUMN PROF_DESCRIPTION SET DATA TYPE VARCHAR ( 4000 )
ALTER COLUMN PROF_EXPERIENCE SET DATA TYPE VARCHAR ( 4000 )
ADD COLUMN PROF_GW_EMAIL_LOWER VARCHAR (128) GENERATED ALWAYS AS (LOWER(PROF_GROUPWARE_EMAIL))
ADD COLUMN PROF_LOGIN VARCHAR (36)
ADD COLUMN PROF_LOGIN_LOWER VARCHAR (36) GENERATED ALWAYS AS (LOWER(PROF_LOGIN))
ADD COLUMN PROF_GIVEN_NAME VARCHAR (128) ADD COLUMN PROF_SURNAME VARCHAR (128) ;

SET INTEGRITY FOR EMPINST.EMPLOYEE IMMEDIATE CHECKED FORCE GENERATED;

ALTER TABLE EMPINST.EMP_DRAFT
ALTER COLUMN PROF_DESCRIPTION SET DATA TYPE VARCHAR ( 4000 )
ALTER COLUMN PROF_EXPERIENCE SET DATA TYPE VARCHAR ( 4000 );

COMMIT WORK;

CONNECT RESET;

TERMINATE;