Creating the Process Federation Server database for Microsoft SQL Server (deprecated)

Edit online

Draft comment:
This topic only applies to BAW, and is located in the BAW repository. Last updated on 2025-03-13 12:15

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.

Attention: 25.0.1.0
Storing saved search definitions that are used by Process Portal in a relational database is deprecated with this version. To help ensure compatibility and future support, use the Federated Data Repository index as the data store for the Process Federation Server.

For more information on how to configure the Process Federation Server data store, see Configuring properties for the data store.

To migrate existing saved search definitions, see Migrating saved search definitions to a Federated Data Repository index.

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

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.
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.