Update 4.0 and 4.5 SQL Server databases manually

Manually update IBM Connections 4.0 or 4.5 databases to v5.0 in an existing IBM WebSphere Application Server and Microsoft SQL Server database environment.

Before applying updates, back up Connections.

Install supporting software for v5.0.

Ensure the name of the Home page database instance is not null. If it is null, rename it to HOMEPAGE. Ensure that Named Pipes is enabled in the SQL Server Network Configuration for all instances. For more information, refer to the SQL Server documentation.

Use this procedure if you want an alternative to using the database wizard to update the databases.

• Use the JRE under the Wizards directory. Update the PATH variable to point to this JRE. For example...
  Wizards/jvm/linux/jre

• IBM Connections does not support GNU Java.

• Use a database administrator ID to run the Java migration utilities.

• After running each command, review output closely for error messages. Resolve errors before proceeding.

Update databases manually

1. Log on to the WAS console on the dmgr and go to...
   Applications > Application types > WebSphere Enterprise Applications

2. Stop all Connections applications.

3. Log in as the database administrator and change to the directory containing the scripts. The relative path is shown in the step for each application.

4. For each application, run the appropriate scripts by entering the commands shown in the following list. In these commands, dbPassword is the password for the SQL Server user named sa. If the database server has multiple SQL Server instances installed, add the following text as the first parameter to each command:

   -S sqlserver_server_name\sqlserver_server_instance_name

   where

   • sqlserver_server_name is the name of the SQL Server database server

   • sqlserver_server_instance_name is the name of the current instance

   To capture the output of each command to a log file, append the following parameter to each command:

   >> \file_path\db_application.log

   where file_path is the full path to the log file and application is the name of the log file.

   For example:

   sqlcmd >> \home\admin_user\lc_logs\db_activities.log

   where sqlcmd is a command with parameters and admin_user is the logged-in user. Ensure you have write permissions for the directories and log files.

5. Perform the following steps for the Activities application:

   1. cd WP_PROFILEhe Activities SQL scripts directory Wizards/connections.sql/activities/sqlserver.

   2. Enter sqlcmd -U dbUser -P dbPassword . before each upgrade command.

      where

      • dbUser is the database user ID

      • dbPassword is the administrator password

      This script generates a message that states Changing any part of an object name could break scripts and stored procedures. We can safely ignore the message.

   3. Check DB schema version :
      -i connect to OPNACT
      -iselect DBSCHEMAVER from ACTIVITIES.OA_SCHEMA

      • For version 53, run:
        -i upgrade-40-45.sql
        -iupgrade-45-50.sql
        -iappGrants.sql
        -irunstats.sql

      • For version 56, run:
        -i upgrade-45-50.sql
        -iappGrants.sql
        -irunstats.sql

      • For version 69, run:
        -i appGrants.sql
        -irunstats.sql

6. Perform the following steps for the Blogs application:

   1. cd WP_PROFILEhe Blogs SQL scripts directory Wizards\connections.sql\blogs\sqlserver.

   2. Enter sqlcmd -U dbUser -P dbPassword before running each upgrade command.

   3. Check the database schema version :
      -i connect to BLOGS
      -iselect value from BLOGS.roller_properties where name= 'database.schema.version'

      • For version 43, run:
        -i upgrade-40-45.sql
        -iupgrade-45-45CR4.sql
        -iupgrade-45CR4-50.sql
        -iappGrants.sql
        -irunstats.sql

      • For version 51, run:
        -i upgrade-45-45CR4.sql
        -iupgrade-45CR4-50.sql
        -iappGrants.sql
        -irunstats.sql

      • For version 52, run:
        -i upgrade-45CR4-50.sql
        -iappGrants.sql
        -irunstats.sql

      • For version 69, run:
        -i appGrants.sql
        -irunstats.sql

7. Perform the following steps for the Bookmarks application:

   1. cd WP_PROFILEhe Bookmarks SQL scripts directory Wizards\connections.sql\dogear\sqlserver.

   2. Enter sqlcmd -U dbUser -P dbPassword before each upgrade command.

   3. Check DB schema version :
      -i connect to DOGEAR
      -iselect DBSCHEMAVER from DOGEAR.DOGEAR_SCHEMA

      • For version 14, run:
        -i upgrade-40-45.sql
        -iupgrade-45-50.sql
        -iappGrants.sql
        -irunstats.sql

      • For version 15, then enter the following command:
        -i upgrade-45-50.sql
        -iappGrants.sql
        -irunstats.sql

      • For version 22, run
        -i appGrants.sql
        -irunstats.sql

8. Perform the following steps for the Communities application:

   1. cd WP_PROFILEhe Communities SQL scripts directory Wizards\connections.sql\communities\sqlserver.

   2. Run sqlcmd -U dbUser -P dbPassword before each upgrade command.

   3. Check the database schema version :
      -i connect to SNCOMM
      -iselect DBSCHEMAVER from SNCOMM.SNCOMM_SCHEMA

      • For version 76, run:
        -i upgrade-40-45.sql
        -iupgrade-45-50.sql
        -iappGrants.sql --i runstats.sql

      • For version 77, run:
        -i upgrade-45-50.sql
        -iappGrants.sql
        -irunstats.sql

      • For version 92, run:
        -i appGrants.sql
        -irunstats.sql

9. Perform the following steps for the Calendar application:

   1. cd WP_PROFILEhe Calendar SQL scripts directory Wizards/connections.sql/calendar/sqlserver.

   2. Run sqlcmd -U dbUser -P dbPassword before each upgrade command.

   3. Check the database schema version :
      -i connect to SNCOMM
      -iselect DBSCHEMAVER from CALENDAR.CA_SCHEMA

      • For version 10, run:
        -i calendar-upgrade-40-45.sql
        -icalendar-upgrade-45-50.sql
        -icalendar-appGrants.sql
        -icalendar-runstats.sql

      • For version 15, run:
        -i calendar-upgrade-45-50.sql
        -icalendar-appGrants.sql
        -icalendar-runstats.sql

      • For version 29, run:
        -i calendar-appGrants.sql
        -icalendar-runstats.sql

10. Perform the following steps for the Files application:

    1. cd WP_PROFILEhe Files SQL scripts directory Wizards\connections.sql\files\sqlserver.

    2. Enter sqlcmd -U dbUser -P dbPassword before each upgrade command.

    3. Check the database schema version :
       -i connect to FILES
       -iselect SCHEMA_VERSION from "FILES"."PRODUCT"

       • For version 79, run:
         -i upgrade-40-45.sql
         -iupgrade-45-45CR3.sql
         -iupgrade-45CR3-50.sql
         -iappGrants.sql

       • For version 82, run:
         -i upgrade-45-45CR3.sql
         -iupgrade-45CR3-50.sql
         -iappGrants.sql

       • For version 89, run:
         -i upgrade-45CR3-50.sql
         -iappGrants.sql

       • For version 106, run:
         -i appGrants.sql

11. Perform the following steps for the Forums application:

    1. cd WP_PROFILEhe Forums SQL scripts directory Wizards\connections.sql\forum\sqlserver.

    2. Enter sqlcmd -U dbUser -P dbPassword before each upgrade command.

    3. Check the database schema version :
       -i connect to FORUM
       -iselect DBSCHEMAVER from FORUM.DF_SCHEMA

       • For version 21, run:
         -i -vf upgrade-40-45.sql
         -iupgrade-45-50.sql
         -iappGrants.sql
         -irunstats.sql

       • For version 27, run:
         -i upgrade-45-50.sql
         -iappGrants.sql
         -irunstats.sql

       • For version 35, run:
         -i appGrants.sql
         -irunstats.sql

12. Perform the following steps for the Home page application:

    1. cd WP_PROFILEhe Home page SQL scripts directory Wizards\connections.sql\homepage\sqlserver.

    2. Run sqlcmd -U dbUser -P dbPassword before each upgrade command.

    3. Check the database schema version :
       -i connect to HOMEPAGE
       -iselect DBSCHEMAVER from HOMEPAGE.HOMEPAGE_SCHEMA

       • For version 110, run:
         -i upgrade-40-40CR2.sql
         -iupgrade-40CR2-45.sql

         Also, run a java migration from 4.0 to 4.5.

         Then, resume executing the upgrade commands.

         -i upgrade-45-45CR3.sql
         -iupgrade-45CR4-50.sql

         Next, run a java migration from 4.5 to 5.0.

         Then, resume executing the upgrade commands

         -i appGrants.sql

       • For version 132, then enter the following command:
         -i upgrade-40CR2-45.sql

         Also, run a java migration from 4.0 to 4.5.

         Then, resume executing the upgrade commands.

         -i upgrade-45-45CR3.sql
         -iupgrade-45CR4-50.sql

         Next, run a java migration from 4.5 to 5.0.

         Then, resume executing the upgrade commands

         -i appGrants.sql

       • For version 210, run:
         -i upgrade-45-45CR3.sql
         -iupgrade-45CR4-50.sql

         Next, run a java migration from 4.5 to 5.0.

         Then, resume executing the upgrade commands

         -i appGrants.sql

       • For version 211, run:
         -i upgrade-45CR4-50.sql

         Next, run a java migration from 4.5 to 5.0.

         Then, resume executing the upgrade commands:

         -i appGrants.sql

       • For version 213, run:
         -i upgrade-45CR-50.sql

         Next, run a java migration from 4.5 to 5.0.

         Then, resume executing the upgrade commands:

         -i appGrants.sql

       • For version 475, run:
         -i appGrants.sql

13. Perform the following steps for the Metrics application:

    1. cd WP_PROFILEhe Metrics SQL scripts directory Wizards\connections.sql\metrics\sqlserver.

    2. Run sqlcmd -U dbUser -P dbPassword before each upgrade command.

    3. Check the database schema :
       -i connect to METRICS
       -iselect SCHEMA_VERSION from "METRICS"."PRODUCT"

       • For version 29, run:
         -i upgrade-40-40CR3.sql
         -iupgrade-40CR3-45.sql
         -iupgrade-45-45CR2.sql
         -ifixup-LO77552.sql
         -iupgrade-45CR2-45CR3.sql
         -iupgrade-45CR4-50.sql
         -iappGrants.sql

       • For version 30, run:
         -i upgrade-40CR3-45.sql
         -iupgrade-45-45CR2.sql
         -ifixup-LO77552.sql
         -iupgrade-45CR2-45CR3.sql
         -iupgrade-45CR4-50.sql
         -iappGrants.sql

       • For version 34, run:
         -i upgrade-45-45CR2.sql
         -ifixup-LO77552.sql
         -iupgrade-45CR2-45CR3.sql
         -iupgrade-45CR4-50.sql
         -iappGrants.sql

       • For version 37, run:
         -i fixup-LO77552.sql
         -iupgrade-45CR2-45CR3.sql
         -iupgrade-45CR4-50.sql
         -iappGrants.sql

       • For version 38, run:
         -i appGrants.sql

14. Perform the following steps for the Mobile application:

    1. cd WP_PROFILEhe Mobile SQL scripts directory Wizards/connections.sql/mobile/sqlserver.

    2. Enter sqlcmd -U dbUser -P dbPassword before each upgrade command.

    3. Check the database schema version :
       -i connect to MOBILE
       -iselect VALUE from MOBILE.ROLLER_PROPERTIES where NAME='database.schema.version'

       • For version 4.0.0.0.0, run:
         -i upgrade-40-45.sql
         -iupgrade-45-45CR4.sql
         -iupgrade-45CR4-50.sql
         -iappGrants.sql
         -irunstats.sql

       • For version 4.5.0.0.0, run:
         -i upgrade-45-45CR4.sql
         -iupgrade-45CR4-50.sql
         -iappGrants.sql
         -irunstats.sql

       • For version 2, run:
         -i upgrade-45CR4-50.sql
         -iappGrants.sql
         -irunstats.sql

       • For version 7, run:
         -i appGrants.sql
         -irunstats.sql

15. Perform the following steps for the Profiles application:

    1. cd WP_PROFILEhe Profiles SQL scripts directory Wizards\connections.sql\profiles\sqlserver

    2. Enter sqlcmd -U dbUser -P dbPassword before each upgrade command.

       This script generates a message that states Changing any part of an object name could break scripts and stored procedures. We can safely ignore the message.

    3. Check the database schema version :
       -i connect to PEOPLEDB
       -iDBSCHEMAVER from EMPINST.SNPROF_SCHEMA

       • For version 33, run:
         -i upgrade-40-45.sql
         -iupgrade-45-50.sql
         -iappGrants.sql
         -iclearScheduler.sql
         -irunstats.sql

       • For version 36, run:
         -i upgrade-45-50.sql
         -iappGrants.sql
         -iclearScheduler.sql
         -irunstats.sql

       • For version 45, run:
         -i appGrants.sql
         -iclearScheduler.sql
         -irunstats.sql

16. Perform the following steps for the Wikis application:

    1. cd WP_PROFILEhe Wikis SQL scripts directory Wizards\connections.sql\wikis\sqlserver.

    2. Enter sqlcmd -U dbUser -P dbPassword before each upgrade command.

    3. Check the database schema version :
       -i connect to WIKIS
       -iselect SCHEMA_VERSION from "WIKIS"."PRODUCT"

       • For version 79, run:
         -i upgrade-40-45.sql
         -iupgrade-45-45CR3.sql
         -iupgrade-45CR3-50.sql
         -iappGrants.sql
         -irunstats.sql

       • For version 82, run:
         -i upgrade-45-45CR3.sql
         -iupgrade-45CR3-50.sql
         -iappGrants.sql
         -irunstats.sql

       • For version 89, run:
         -i upgrade-45CR3-50.sql
         -iappGrants.sql
         -irunstats.sql

       • For version 102, run:
         -i appGrants.sql
         -irunstats.sql

17. cd Wizards\connections.sql\libraries.gcd\sqlserver

    1. sqlcmd -U dbUser -P dbPassword -i "createDb.sql" -v filepath="path_to_db" password="password_for_FNGCDUSER"

    2. sqlcmd -U dbUser -P dbPassword appGrants.sql

    Wizards\connections.sql\libraries.os\sqlserver

    1. sqlcmd -U dbUser -P dbPassword -i "createDb.sql" -v filepath="path_to_db" password="password_for_FNOSUSER"

    2. sqlcmd -U dbUser -P dbPassword appGrants.sql

What to do next

Check that all the databases are working correctly.

Parent topic:
Update 4.0 and 4.5 databases manually

Related:

Access the News configuration file

Back up IBM Connections DBS and file systems