Upgrading Lotus Connections Profiles to 1.0.2, take 2

I posted previously on how to update the Lotus Connections database schema from version 1.0.1 to 1.0.2.

Over on the Lotus Connections Forums, Henry Walther posted that he found the original files in the TDI directories. For others, here is the location of the files:

C:\Program Files\IBM\TDI\V6.1\tdisol\TDI\database\db2

I did a comparison between my version and this one and the EMPLOYEE columns are the same. Interestingly, the Lotus version does not include expanding the description and experience columns in the EMP_DRAFT table:

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

There are a couple of other useful files in the \TDI\database\db2 directory. Look for the runstats.bat batch file, which will help DB2 run faster on the Profiles tables.

Good find Henry!

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;