Replicating the ibi WebFOCUS Repository Database

The Repository Database Replication utility transfers a copy of the WebFOCUS Repository from a source database to a target database. Because the utility preserves the integrity of the Repository database structure and contents during the transfer, you can use it to replicate the WebFOCUS Repository from Derby™ to Oracle®, Db2®, PostgreSQL®, MySQL AB®, or Microsoft SQL Server®.

Overview

Follow these steps to replicate the Repository database:

1. Create a target relational database.
2. Identify source and target database connection information and credentials.
3. Assign source and target database credentials and connection information to the appropriate settings in the Database Replication Settings file.
4. Run the Database Replication Utility.
5. Review the results of the replication utility run.
6. Replace the settings for the default WebFOCUS Repository database in the install.cfg file with those that identify the replicated WebFOCUS Repository database.
7. Test the connection to the replicated WebFOCUS Repository database.

For more information about the credentials and source and target database connection information you need in order to run the utility successfully, see Understanding Database Replication Settings .

As stated in the introduction, you can use this utility to replicate the Repository database from Derby to Oracle, Db2, PostgreSQL, MySQL AB, or Microsoft SQL Server. You can also use it to replicate the Repository database from these relational database management systems to Derby.

Please consult the Customer Support Team if you must replicate the Repository to any other database platform. The database replication utility cannot replicate the Repository to any non-relational database or to any database platform that does not use a JDBC driver.

Identify Source and Target Database Connection Information and Credentials

Procedure
1. Open the utiluservars file as follows:
   • Windows: Navigate to drive:\ibi\WebFOCUS82\utilities\setenv, and open the utiluservars.bat file with a text editor.
   • UNIX or Linux: Navigate to install_directory/ibi/WebFOCUS82/utilities/setenv, and open the utiluservars.sh file with a text editor.

   The value in the JDBC_PATH parameter in this file is used as the value for the SOURCE_JDBC_PATH parameter in the db_replication_settings file.

2. Open the install.cfg file as follows:
   • Windows: Navigate to drive:\ibi\WebFOCUS82\config and open the install.cfg file with a text editor.
   • UNIX or Linux: Navigate to install_directory/ibi/WebFOCUS82/config and open the install.cfg file with a text editor.

   The values in the IBI_REPOS_DB_URL, IBI_REPOS_DB_DRIVER, and IBI_REPOS_DB_USER parameters in this file are used as the values for the SOURCE_REPOS_DB_URL, SOURCE_REPOS_DB_DRIVER, and SOURCE_USER_NAME parameters in the db_replication_settings file.

3. Keep both files open until the configuration of the Database Replication Settings file is complete.
4. To identify target database connection information and credentials, contact the database administrator who created the empty target database.

   Or

   Review the target database configuration and documentation provided by the vendor.

   The location and configuration of the specific values in the target database vary by vendor and are beyond the scope of this document.

Prepare the Database Replication Settings File

Procedure
1. Open the db_replicate_settings file as follows:
   • For Windows: Navigate to drive:\ibi\WebFOCUS82\utilities\dbupdate, and open the db_replicate_settings.bat file with a text editor.
   • For UNIX or Linux: Navigate to install_directory/ibi/WebFOCUS82/utilities/dbupdate, and open the db_replicate_settings.sh file with a text editor.
   • Note: If you want to keep a backup copy of the original version of the db_replicate_settings file, create a copy of the file you are updating before making changes.
2. Scroll down to the source section of the file.
3. In the SOURCE_CLASS_PATH parameter, accept the pathname displayed, by default, without making any changes.
4. In the SOURCE_JDBC_PATH parameter, replace the sample value with the pathname to the source database JDBC driver, as identified in the JDBC_PATH parameter of the utiluservars.bat file.

   This value must be enclosed in quotation marks (").

5. In the SOURCE_REPOS_DB_URL parameter, replace the sample value with the URL from the IBI_REPOS_DB_URL parameter of the install.cfg file.
6. In the SOURCE_REPOS_DB_DRIVER parameter, replace the sample value with the JDBC driver class name from the IBI_REPOS_DB_DRIVER parameter of the install.cfg file.
7. In the SOURCE_USER_NAME parameter, replace the user ID from the IBI_REPOS_DB_USER parameter of the install.cfg file.

   Make sure that the characters and format of the user ID are an exact match to the user ID as it appears in the source database and that there are no excess spaces before or after the user ID.

   If you do not assign a valid user ID to this parameter, the database replication utility prompts you for it at run time.

8. In the db_replicate_settings file SOURCE_PASSWORD parameter, type the password assigned to the Repository user name during the product installation.

   This value is encrypted in the IBI_REPOS_DB_PASSWORD parameter in the install.cfg file, so you cannot paste it from the install.cfg file.

   Make sure that the characters and format of the password are an exact match to the password as it appears in the source database and that there are no excess spaces before or after the password.

   If you do not assign a valid password to this parameter, the database replication utility prompts you for it at run time.

9. Scroll down to the target section of the file.
10. In the TARGET_JDBC_PATH parameter, type over the sample value with the pathname to the JDBC driver of the target database.

    If your JDBC driver consists of more than one jar file, separate multiple jar file paths with a semi-colon.

11. In the TARGET_REPOS_DB_URL parameter, type over the sample value with the URL of the target database connection.
12. In the TARGET_REPOS_DB_DRIVER parameter, type over the sample value with the class name of the target database JDBC driver.
13. In the Å_NAME parameter, type the user ID assigned to the new database when it was created.

    Make sure that the characters and format of the user ID are an exact match to the user ID as it appears in the target database and that there are no excess spaces before or after the user ID.

    If you do not assign a value user ID to this parameter, the database replication utility prompts you for it at run time.

14. In the TARGET_PASSWORD parameter, type the password assigned to the new database user ID.

    Make sure that the characters and format of the password are an exact match to the password as it appears in the target database and that there are no excess spaces before or after the password.

    If you do not assign a valid password to this parameter, the database replication utility prompts you for it at run time.

15. Save and close the db_replicate_settings file with your changes.

Run the Database Replication Utility

Procedure
1. Start the database replication utility as follows:
   • For Windows: Navigate to drive:/ibi/WebFOCUS82/utilities/dbupdate, and double-click db_replicate.bat.
   • For UNIX or Linux: Navigate to install_directory/ibi/WebFOCUS82/utilities/dbupdate, and double-click db_replicate.sh.
2. If you receive one of the following prompts, respond as directed in the following substeps, and press the Enter key after each response:
   1. Enter Source Database Repository Username: Type the user ID assigned to the WebFOCUS Repository created during the product installation.
   2. Enter Source Database Repository password: Type the password assigned to the Repository user name.
      Note: When you type this value, the cursor remains in place and does not show any typed characters.
   3. Enter Target Database Repository Username: Type the user ID assigned to the new database when it was created.
   4. Enter Target Repository Database Password: Type the password assigned to the new database user ID.
      Note: When you type this value, the cursor remains in place and does not show any typed characters.
3. Monitor the Command Prompt window while it displays a list of the parameters and values provided for this run of the utility.
4. When you receive a message stating that the DB update process is starting, wait while the utility completes the database replication process. This could take several seconds.
5. If you receive an error message that stops the utility, such as jdbc driver not found, credentials invalid, or credentials don’t have ability to access source or target:
   1. Close the Command Prompt window.
   2. Revise the values assigned to the db_replicate_settings file, as described in Prepare the Database Replication Settings File.

   You can also review these errors in the db_replicate_install log file created for this run of the utility. For more information on how to open and review the log file, see Review the Results of the Replication.

6. When you receive a message stating that the database update was successful, press any key to close the Command Prompt window.

Review the Results of the Replication

Procedure
1. Sign in as an administrator, and open the Administration Console.
2. Click the Diagnostic tab, and then click Application Log Files.
3. On the Application Log Files page, click db_replicate_install_2_YYYY-MM-DD_HH-MM-SS.txt, where YYYY-MM-DD_HH-MM-SS represents the date and time the database replication utility log file was created.
4. Review records in the log file page to ensure that the replication is complete, and that no error messages preventing it from completing successfully are present.

   If the final entry reads, Update process SUCCEEDED, the replication completed successfully.

5. Close the log file page, and sign out.

Redirect the Repository Database From the Old Source Database to the New Target Database

Procedure
1. Stop the Application Server, if it is running.
2. Navigate to the config directory.
   • On Windows: drive:\ibi\WebFOCUS82\config
   • On Unix or Linux: install_directory/ibi/WebFOCUS82/config
3. Create and save a backup copy of the install.cfg file.
4. Open the install.cfg file in a text editor.
5. In the IBI_REPOS_DB_USER parameter, type over the existing value from the source database with the user name assigned to the target database.

   Make sure that the characters and format of the user ID are an exact match to the user ID as it appears in the source database and that there are no excess spaces before or after the user ID.

6. In the IBI_REPOS_DB_PASSWORD parameter, type over the encrypted password from the source database with the new plain text password assigned to the database user in the target database.

   Make sure that the characters and format of the password are an exact match to the password as it appears in the source database and that there are no excess spaces before or after the password.

   The plain text password is encrypted automatically when you restart the Application Server.

7. In the IBI_REPOS_DB_DRIVER parameter, type over the class name of the database driver of the source database with the class name of the database driver of the target database.
8. In the IBI_REPOS_DB_URL parameter, type over the URL for the source database connection with the URL for the target database connection.
9. Confirm that the new values are correct, and then save and close the install.cfg file.
10. Re-start the Application Server to encrypt the plain text Repository Database User password in the install.cfg file.
    Note: During the restart, the Application Server will attempt to utilize the new RDBMS.

Test the New Repository Database Connection

Procedure
1. If the Application Server was not stopped and restarted after updating the install.cfg file, start or restart it before you begin.
2. Sign in as an administrator and open the Administration Console.
3. Open the Database Replication Utility Log File, as described in Review the Results of the Replication.
4. Review the log file for error messages.
5. Address any errors that occurred when the Application Server attempted to connect to the new RDBMS.
6. When you have addressed errors in the Database Replication Utility log file, sign in again as an administrator.
7. If you encounter errors during sign in, review the values assigned to the IBI_REPOS_DB settings in the install.cfg file to ensure they contain the appropriate connection values for the new target database.
8. If you are able to run the report with no errors, you have successfully established a connection to the target database.

Understanding Database Replication Settings

Values for the settings required by the database replication utility are stored in the db_replicate_settings file. This file contains four main sections: Prompt if Needed, Samples, Source, and Target.

The Prompt if Needed section enables you to activate or deactivate the interactive mode for the Database Replication Utility. This mode is activated, by default, ensuring that you are prompted to add missing database connection information whenever you run the database replication utility.

The Samples section contains sample Database Driver paths, Database Driver URLs, and sample JDBC Paths for various RDBMS providers, including MS SQL Server®, PostgresSQL, MySQL™, Db2®, Oracle®, and Derby™. Consult the latest version of the db_replicate_settings file for full details.

The Source section contains the following settings that define connection information for the Repository database created during the product installation. This database serves as the source of the replication.

With the exception of the SOURCE_CLASS_PATH value, you must replace the sample values in the Source section with those that conform to the values used in your local installation of WebFOCUS if your local configuration does not match the sample values assigned to the settings in the SOURCE section. You can clear these values if you prefer to have the Database Replication Utility prompt you for this information at run time.

• SOURCE_CLASS_PATH. Defines the location of the entity classes matching the database that is to be replicated. The CLASSPATH is the location of the IBFSCommands.jar file for the WebFOCUS Repository. The value in this setting must not be updated.

  The database replication utility does not define a classpath for the target database.

  In the db_replicate_settings file, the value assigned to this setting contains the variable %WFROOT% for the Windows version or ${WFROOT} for the UNIX version. This variable captures the root directory for your installation of WebFOCUS. Within that root directory, the location of the IBFSCommands.jar file remains the same:

  • For Windows: \utilities\lib\
  • For UNIX or Linux: /utilities/lib/
• SOURCE_JDBC_PATH. Defines the full path to the location of the jar files that make up the JDBC driver for the source database. If the JDBC driver consists of more than one jar file, multiple jar file paths are separated with a semicolon (;).

  You must replace the sample value assigned to this field with the pathname used by your organization before running the database replication utility.

  You can identify the location of the source database JDBC driver in use in your installation of WebFOCUS in the JDBC_PATH parameter of the utiluservars.bat file, which is located in drive:\ ibi\WebFOCUS82\utilities\setenv for Windows, or install_directory/ibi/WebFOCUS82/utilities/setenv for UNIX or Linux.

  • In most Windows-based installations of WebFOCUS, the JDBC driver jar files are stored in the location, drive:\ibi\derby\lib\derbyclient.jar.
  • In most Unix or Linux-based installations of WebFOCUS, the JDBC driver jar files are stored in the location, install_directory/ibi/Drivers/derbyclient-10.8.1.2.jar.
• SOURCE_REPOS_DB_URL. Defines the URL that identifies the connection string to the source database. This URL takes the form:

  jdbc:subprotocol:node/databaseName

  where:

  jdbc

  Is the java database class protocol.

  subprotocol

  Is the protocol for the RDBMS that hosts the database. For example, derby//localhost.

  Note: If the database resides on the same computer node as the java program, the hostname part and the corresponding double slashes of the jdbc can be skipped. For example, jdbc:odbc:wham.

  node

  Is the number of the port on the machine that hosts the database. For example, 8080, if the database is on the same machine.

  databaseName

  Is the name of the Repository database or its copy. For example, WebFOCUS82.

  You must replace the sample value assigned to this field with the database connection string used by your organization before running the database replication utility.

  You can identify the source database URL in use in your installation of WebFOCUS in the IBI_REPOS_DB_URL parameter of the install.cfg file, which is located in drive:\ibi\WebFOCUS82\config for Windows, or install_directory/ibi/WebFOCUS82/config for UNIX or Linux.

  In most Windows-based and UNIX or Linux-based installations of WebFOCUS, the database connection string is:

  jdbc:derby://localhost:1527/WebFOCUS82;

• SOURCE_REPOS_DB_DRIVER. Defines the location of the jar file that contains the Java Database Connectivity (JDBC) driver for the Repository, which is the source database. The JDBC driver class provides network connectivity to the Network Server for this database.

  You can identify the URL in use in your product installation in the IBI_REPOS_DB_DRIVER parameter of the install.cfg file, which is located in drive:\ibi\WebFOCUS82\config for Windows, or install_directory/ibi/WebFOCUS82/config for UNIX or Linux.

  In most product installations, the location of the jar file that contains the Java Database Connectivity (JDBC) driver is:

  org.apache.derby.jdbc.ClientDriver

• SOURCE_USER_NAME. Defines the ID for the source database user. When you run the utility, this setting should contain the user name assigned to the Repository during product installation.

  You can identify the user ID of the source database in use in your product installation in the IBI_REPOS_DB_USER parameter of the install.cfg, which is located in drive:\ibi\WebFOCUS82\config, for Windows or install_directory/ibi/WebFOCUS82/config for UNIX or Linux.

  This setting contains the sample value username, by default. If you run the Database Replication Utility without specifying the source database user ID in this field, you are prompted to type the source database user name.

  A database user ID grants access to information and resources in the database to the users and applications to which it is assigned. The user ID for the source database must have the right to read database objects.

• SOURCE_PASSWORD. Defines the password assigned to the source database user. When you run the utility, this setting should contain the password assigned to the Repository during product installation.

  An encrypted version of the password assigned to the source database user in use in your product installation appears in the IBI_REPOS_DB_PASSWORD parameter of the install.cfg file, which is located in drive:\ibi\WebFOCUS82\config for Windows, or install_directory/ibi/ WebFOCUS82/config for UNIX or Linux.

  This setting contains the sample value password, by default. If you run the Database Replication Utility without specifying the source database password in this field, you are prompted to type the password for the source database user.

  This password for the source database authenticates the identity of the source database User ID.

The TARGET section of the Database Replication Settings file contains the following settings that define the connection for the database targeted by the replication.

You must always replace the sample values in the TARGET section with those that conform to the target database you wish to create, or clear those values if you prefer to have the Database Replication Utility prompt you for this information at run time.

• TARGET_JDBC_PATH. Identifies the location of the jar file that contains the Java Database Connectivity (JDBC) driver for the target database. The JDBC driver class provides network connectivity to the Network Server for the target database.

  You must replace the sample value with the target database JDBC Path before running the database replication utility.

• TARGET_REPOS_DB_URL. The URL that identifies the connection string to the target database. This URL takes the form:

  jdbc:subprotocol:node/databaseName

  where:

  jdbc

  Is the java database class protocol.

  subprotocol

  Is the protocol for the RDBMS that hosts the database. For example, derby//localhost.

  Note: If the database resides on the same computer node as the java program, the hostname part and the corresponding double slashes of the jdbc can be skipped. For example, jdbc:odbc:wham.

  node

  Is the number of the port on the machine that hosts the database. For example, 8080, if the database is on the same machine.

  databaseName

  Is the name of the target database.

  You must replace the sample value with the URL of the target database before running the Database Replication Utility.

• TARGET_REPOS_DB_DRIVER. Defines the location of the jar file that contains the Java Database Connectivity (JDBC) driver for the target database. The JDBC driver class provides network connectivity to the Network Server for the target database.

  You must replace the sample value with the location of the JDBC driver for the target database before running the Database Replication Utility.

• TARGET_USER_NAME. Defines the user ID for the target database. When you run the utility, this setting should contain the user name assigned to the target database when it was created.

  However, it contains the sample value, username, by default. If you run the Database Replication Utility without specifying the target database user ID in this field, you will be prompted to type the target database user name.

  A database User ID grants access to information and resources in the source database to the applications and the users to which it is assigned. The User ID for the target database must have the right to create and modify database objects.

• TARGET_PASSWORD. Defines the password assigned to the target database user. When you run the utility, this setting should contain the password assigned to the target database user name during the creation of the target database.

  However, it contains the sample value, password, by default. If you run the Database Replication Utility without specifying the target database password in this field, you are prompted to type the password for the target database user.

  This password authenticates the identity of the Database User ID for the target database.