Updating 4.0 and 4.5 SQL Server databases manually

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

Before you begin

Before applying updates, back up your databases. For more information, see Backing up IBM Connections.

Ensure that you have installed and configured all supporting software for version 5.0.

Ensure that 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 your SQL Server documentation.

About this task

This topic describes how to update IBM Connections version 4.0 or 4.5 databases to version 5.0 manually. Use this procedure if you want an alternative to using the database wizard to update your databases.

Notes:
  • This topic applies to all releases of version 4.0 and 4.5
  • Use the Java Runtime Environment (JRE) under the Wizards directory in the installation media. Update your PATH variable to point to this JRE, using the instructions for your operating system. For example, the relative path to the JRE on the Microsoft Windows operating system might be Wizards\jvm\win\jre. For the AIX® or Linux operating systems, the relative path might be Wizards/jvm/aix/jre and Wizards/jvm/linux/jre.
  • IBM Connections does not support GNU Java.
  • You need to use a database administrator ID to run the Java migration utilities described in this task.
  • After running each command, examine the output of the command for error messages. If you find errors, resolve them before continuing with the update process.
  • To improve readability, some commands and file paths in this topic are displayed on separate lines. Ignore these formatting conventions when entering the commands.

To update databases manually, complete the following steps:

Procedure

  1. Log in to the WebSphere Application Server Integrated Solutions Console on your Deployment Manager.
  2. Go to Applications > Application types > WebSphere enterprise Applications.
  3. Stop all IBM Connections applications.
  4. Log in as the database administrator and change to the directory that contains the scripts. The relative path is shown in the step for each application.
  5. For each application, run the appropriate scripts by entering the commands that are shown in the following list. In these commands, dbPassword is the password for the SQL Server user named sa. If your 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 your SQL Server database server
    • sqlserver_server_instance_name is the name of your current instance
    Note: 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 that you have write permissions for the directories and log files.

    Note: If your database version of IBM Connections components are already at a given schema version, then start running scripts from that schema version level. For example, if the Activities database is schema version 56, enter the commands for schema version 56 and version 69.
  6. Perform the following steps for the Activities application:
    1. Change to the 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
      Note: This script generates a message that states Changing any part of an object name could break scripts and stored procedures. You can safely ignore the message.
    3. Check for the database schema version as follows: 
      -i connect to OPNACT

-i select DBSCHEMAVER from ACTIVITIES.OA_SCHEMA
      • If the schema version is 53, then enter the following commands:
        -i upgrade-40-45.sql
-i upgrade-45-50.sql
-i appGrants.sql
      • If schema version is 56, then enter the following commands:
        -i upgrade-45-50.sql 
-i appGrants.sql
      • If schema version is 69, then enter the following commands:
        -i appGrants.sql
  7. Perform the following steps for the Blogs application:
    1. Change to the Blogs SQL scripts directory Wizards\connections.sql\blogs\sqlserver.
    2. Enter sqlcmd -U dbUser -P dbPassword before you run each upgrade command.
    3. Check the database schema version as follows: 
      -i connect to BLOGS

-i select value from BLOGS.roller_properties where name=
'database.schema.version'
      • If the schema version is 43, then enter the following commands:
        -i upgrade-40-45.sql
-i upgrade-45-45CR4.sql
-i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 51, then enter the following commands:
        -i upgrade-45-45CR4.sql
-i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 52, then enter the following commands:
        -i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 69, then enter the following commands:
        -i appGrants.sql
  8. Perform the following steps for the Bookmarks application:
    1. Change to the Bookmarks SQL scripts directory Wizards\connections.sql\dogear\sqlserver.
    2. Enter sqlcmd -U dbUser -P dbPassword before each upgrade command.
    3. Check for the database schema version as follows: 
      -i connect to DOGEAR

-i select DBSCHEMAVER from DOGEAR.DOGEAR_SCHEMA
      • If the schema version is 14, then enter the following commands:
        -i upgrade-40-45.sql
-i upgrade-45-50.sql
-i appGrants.sql
      • If schema version is 15, then enter the following command:
        -i upgrade-45-50.sql
-i appGrants.sql
      • If schema version is 22, then enter the following commands
        -i appGrants.sql
  9. Perform the following steps for the Communities application:
    1. Change to the 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 as follows: 
      -i connect to SNCOMM

-i select DBSCHEMAVER from SNCOMM.SNCOMM_SCHEMA
      • If the schema version is 76, then enter the following commands:
        -i upgrade-40-45.sql
-i upgrade-45-50.sql
-i appGrants.sql
      • If schema version is 77, then enter the following commands:
        -i upgrade-45-50.sql
-i appGrants.sql
      • If schema version is 92, then enter the following commands:
        -i appGrants.sql
  10. Perform the following steps for the Calendar application:
    1. Change to the Calendar 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 as follows: 
      -i connect to SNCOMM

-i select DBSCHEMAVER from CALENDAR.CA_SCHEMA
      • If schema version is 10, then enter the following commands:
        -i calendar-upgrade-40-45.sql
-i calendar-upgrade-45-50.sql 
-i calendar-appGrants.sql
      • If schema version is 15, then enter the following commands:
        -i calendar-upgrade-45-50.sql
-i calendar-appGrants.sql
      • If schema version is 29, then enter the following commands:
        -i calendar-appGrants.sql
  11. Perform the following steps for the Files application:
    1. Change to the 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 as follows: 
      -i connect to FILES

-i select SCHEMA_VERSION from "FILES"."PRODUCT"
      • If the schema version is 79, then enter the following commands:
        -i upgrade-40-45.sql
-i upgrade-45-45CR3.sql
-i upgrade-45CR3-50.sql
-i appGrants.sql
      • If schema version is 82, then enter the following commands:
        -i upgrade-45-45CR3.sql
-i upgrade-45CR3-50.sql
-i appGrants.sql
      • If schema version is 89, then enter the following commands:
        -i upgrade-45CR3-50.sql
-i appGrants.sql
      • If schema version is 107, then enter the following commands:
        -i appGrants.sql
      In addition, create the tables for Push notification by running the following commands:
      Example for SQL Server Account Mode:
      sqlcmd -S sql_server_name\sql_server_instance_name -U sql_server_account -P sql_server_account_password -i "pns-createDb.sql" -v filepath="sql_server_data_path" password="password_for_application_user
      Followed by: 
      sqlcmd -S sql_server_name\sql_server_instance_name -U sql_server_account -P sql_server_account_password -i "pns-appGrants.sql" -v filepath="sql_server_data_path" password="password_for_application_user"
      For example, for Local Account Mode:
      sqlcmd -S sql_server_name\sql_server_instance_name -U servername \local_account -P local_account_password -i "pns-createDb.sql" -v filepath="sql_server_data_path" password="password_for_application_user"
      Followed by:
      sqlcmd -S sql_server_name\sql_server_instance_name -U servername \local_account -P local_account_password -i "pns-appGrants.sql" -v filepath="sql_server_data_path" password="password_for_application_user"
      Where:
      • sql_server_account and sql_server_account_password are the credentials for SQL Server. These credentials do not apply for Windows Local Account or Windows Domain Account.
      • servername \local_account is the credential for the user ID.
      • sql_server_data_path is the directory where the created database is stored.
  12. Perform the following steps for the Forums application:
    1. Change to the 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 as follows: 
      -i connect to FORUM

-i select DBSCHEMAVER from FORUM.DF_SCHEMA
      • If the schema version is 21, then enter the following commands:
        -i -vf upgrade-40-45.sql
-i upgrade-45-50.sql
-i appGrants.sql
      • If schema version is 27, then enter the following commands:
        -i upgrade-45-50.sql
-i appGrants.sql
      • If schema version is 35, then enter the following commands:
        -i appGrants.sql
  13. Perform the following steps for the Home page application:
    1. Change to the 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 as follows: 
      -i connect to HOMEPAGE

-i select DBSCHEMAVER from HOMEPAGE.HOMEPAGE_SCHEMA
      • If the schema version is 110, then enter the following commands:
        -i upgrade-40-40CR2.sql
-i upgrade-40CR2-45.sql –v password="<password for HOMEPAGEUSER>"

        Also, you need to run a java migration from 4.0 to 4.5.

        Then, resume executing the upgrade commands.

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

        Next, you need to run a java migration from 4.5 to 5.0.

        Then, resume executing the upgrade commands

        -i appGrants.sql
      • If schema version is 132, then enter the following command:
        -i upgrade-40CR2-45.sql –v password="<password for HOMEPAGEUSER>"
-i upgrade-45CR3-45CR4.sql

        Also, you need to run a java migration from 4.0 to 4.5.

        Then, resume executing the upgrade commands.

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

        Next, you need to run a java migration from 4.5 to 5.0.

        Then, resume executing the upgrade commands

        -i appGrants.sql
      • If schema version is 210, then enter the following commands:
        -i upgrade-45-45CR3.sql
-i upgrade-45CR3-45CR4.sql
-i upgrade-45CR4-50.sql

        Next, you need to run a java migration from 4.5 to 5.0.

        Then, resume executing the upgrade commands

        -i appGrants.sql
      • If schema version is 211, then enter the following commands:
        -i upgrade-45CR4-50.sql

        Next, you need to run a java migration from 4.5 to 5.0.

        Then, resume executing the upgrade commands:

        -i appGrants.sql
      • If schema version is 213, then enter the following commands:
        -i upgrade-454-50.sql

        Next, you need to run a java migration from 4.5 to 5.0.

        Then, resume executing the upgrade commands:

        -i appGrants.sql
      • If schema version is 475, then enter the following commands:
        -i appGrants.sql
  14. Perform the following steps for the Metrics application:
    1. Change to the 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 as follows: 
      -i connect to METRICS

-i select SCHEMA_VERSION from "METRICS"."PRODUCT"
      • If the schema version is 29, then enter the following commands:
        -i upgrade-40-40CR3.sql
-i upgrade-40CR3-45.sql
-i upgrade-45-45CR2.sql
-i fixup-LO77552.sql
-i upgrade-45CR2-45CR3.sql
-i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 30, then enter the following commands:
        -i upgrade-40CR3-45.sql
-i upgrade-45-45CR2.sql
-i fixup-LO77552.sql
-i upgrade-45CR2-45CR3.sql
-i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 34, then enter the following commands:
        -i upgrade-45-45CR2.sql
-i fixup-LO77552.sql
-i upgrade-45CR2-45CR3.sql
-i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 37, then enter the following commands:
        -i fixup-LO77552.sql
-i upgrade-45CR2-45CR3.sql
-i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 38, then enter the following commands:
        -i appGrants.sql
  15. Perform the following steps for the Mobile application:
    1. Change to the 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 as follows: 
      -i connect to MOBILE

-i select VALUE from MOBILE.ROLLER_PROPERTIES where NAME='database.schema.version'
      • If the schema version is 4.0.0.0.0, then enter the following commands:
        -i upgrade-40-45.sql
-i upgrade-45-45CR4.sql
-i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 4.5.0.0.0, then enter the following commands:
        -i upgrade-45-45CR4.sql
-i upgrade-45CR4-50.sql
-i appGrants.sql
      • If schema version is 2 or 3, then enter the following commands:
        -i upgrade-45CR4-50.sql
-i appGrants.sql

        Disregard any warnings about duplicate foreign keys.

      • If schema version is 7, then enter the following commands:
        -i appGrants.sql
  16. Perform the following steps for the Profiles application:
    1. Change to the Profiles SQL scripts directory Wizards\connections.sql\profiles\sqlserver
    2. Enter sqlcmd -U dbUser -P dbPassword before each upgrade command.
      Note: This script generates a message that states Changing any part of an object name could break scripts and stored procedures. You can safely ignore the message.
    3. Check the database schema version as follows: 
      -i connect to PEOPLEDB

-i DBSCHEMAVER from EMPINST.SNPROF_SCHEMA
      • If schema version is 33, then enter the following commands:
        -i upgrade-40-45.sql
-i upgrade-45-50.sql
-i appGrants.sql
-i clearScheduler.sql
      • If schema version is 36, then enter the following commands:
        -i upgrade-45-50.sql
-i appGrants.sql
-i clearScheduler.sql
      • If schema version is 45, then enter the following commands:
        -i appGrants.sql
-i clearScheduler.sql
  17. Perform the following steps for the Wikis application:
    1. Change to the 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 as follows: 
      -i connect to WIKIS

-i select SCHEMA_VERSION from "WIKIS"."PRODUCT"
      • If schema version is 79, then enter the following commands:
        -i upgrade-30-301.sql 
-i upgrade-301-40.sql
-i upgrade-40-45.sql
-i upgrade-45-45CR3.sql
-i upgrade-45CR3-50.sql
-i appGrants.sql
      • If schema version is 82, then enter the following commands:
        -i upgrade-45-45CR3.sql
-i upgrade-45CR3-50.sql
-i appGrants.sql
      • If schema version is 89, then enter the following commands:
        -i upgrade-45CR3-50.sql
-i appGrants.sql
      • If schema version is 102, then enter the following commands:
        -i appGrants.sql
  18. IBM Connections Content Manager: 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.