Creating the Process Federation Server database for Microsoft SQL Server

Edit online
Process Federation Server includes scripts for creating an SQL Server database for storing saved searches. Customize and then run the scripts.

Before you begin

  • Ensure that SQL Server was installed with mixed mode (Windows Authentication or SQL Server Authentication) as the authentication mode.
  • Ensure that XA transactions are configured for SQL Server.
    Important: SQL Server is not pre-configured for XA transactions. XA support is delivered as part of the Microsoft JDBC driver distribution but not enabled by default. To enable XA support, you must change the configuration in the Microsoft Windows Distributed Transaction Coordinator (MS DTC) service.

About this task

The scripts for creating the Process Federation Server database for DB2® are in the pfs_install_root/ibmProcessFederationServer/wlp-ext/dbscripts/sqlserver.
  • createDatabase_PFS.sql
  • createTable_PFS.sql

Procedure

  1. Replace the variables in the script files with the values for your federated environment:
    @DB_NAME@
    The name of the Process Federation Server database, for example, PFSDB.
    @SCHEMA@
    The name of the Process Federation Server schema, for example, PFSDB.
    @DB_USER@
    The user name that you want to use to create the database. During database creation, this user is granted db_owner privileges. To secure the database, you can remove the db_owner privileges in the createDatabase_PFS.sql script and enable them in the createTables_PFS.sql script. The database scripts contain more information about these changes.
    @DB_PASSWORD@
    The password for the database user. You can create the database user and password before you create the database, or you can edit the script to create them for you. If the script is to create the user and password, replace this variable with the password.
  2. From a DB2 command prompt, run the scripts to create the database, table spaces, and tables.
    The following variables are used in the script calls:
    dbadmin
    The SQL Server user with administrative authority.
    1. Create the database. 
      sqlcmd -U dbadmin -P password -e -i createDatabase_PFS.sql
      You can manage the locale settings for the database by using the COLLATE option with the create database script: COLLATE SQL_Latin1_General_CP1_CS_AS. For example, to change the locale settings to French, include COLLATE French_100_CS_AS with the script call.

      To change the default language, add the DEFAULT_LANGUAGE option to the createDatabase_PFS.sql file. For example, to change the default language to French, set the option to DEFAULT_LANGUAGE=French.

    2. Create the tables. 
      sqlcmd -U dbadmin -P password -e -i createTable_PFS.sql
    The SQL Server database is created.

What to do next

Create a process federation server. See Creating a process federation server.