IBM BPM, V8.0.1, All platforms > Install IBM BPM > IBM BPM Advanced > Install IBM BPM Advanced > On AIX > Network deployment environment > Create databases and database design files > For SQL Server

Create SQL Server databases

Create required databases before creating profiles.

The default database names are BPMDB for the Process Server database, PDWDB for the Performance Data Warehouse database, and CMNDB for the Common database.

The Process Server and Performance Data Warehouse require their own separate databases and cannot be configured on the same database as the other BPM components.


Procedure

  1. Install Microsoft SQL Server.

  2. Use the following commands to create the databases for SQL Server:

    1. To create the Process Server (BPMDB) and Performance Data Warehouse (PDWDB) databases: 
      osql -b -S  hostname -U  db_user_account -P  db_user_password 
      -Q "CREATE DATABASE  database_name COLLATE SQL_Latin1_General_CP1_CI_AS"
      where hostname is the host name of the system that hosts SQL Server, db_user_account and db_user_password are the user name and password used to log in to create the database, and database_name is the name of the database that you are creating. COLLATE SQL_Latin1_General_CP1_CI_AS is included because these databases must be case-insensitive.

    2. To create the Common database: 
      osql -b -S  hostname -U  db_user_account -P  db_user_password 
      -Q "CREATE DATABASE  database_name COLLATE SQL_Latin1_General_CP1_CS_AS"
      The difference is the COLLATE clause. CMNDB requires case-sensitive collation.

    Ensure that the databases you create for Process Server and Performance Data Warehouse are case insensitive. The letters CI in the COLLATE attribute value specifies this. Ensure this variable looks like this: SQL_Latin1_General_CP1_CI_AS (not SQL_Latin1_General_CP1_CS_AS). Failure to do so can result in an error like the following: 

    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'message.routingCache' 
defined in class path resource [registry.xml]: Instantiation of bean failed; nested exception is 
org.springframework.beans.BeanInstantiationException: Could not instantiate bean class 
[com.lombardisoftware.bpd.runtime.engine.message.DefaultMessageRoutingCache]: Constructor threw exception; 
nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 
bad SQL grammar [select "value" from lsw_system where "key"=?]; nested exception is 
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'lsw_system'.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'lsw_system'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
[...]

    When using Microsoft SQL Server as the database for the Performance Data Warehouse, the following error message might be displayed during report generation using optimizer or portal for an existing process application. This prevents the report from being displayed. 

    com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'slathresholdtraversals'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteCursored(SQLServerStatement.java:1824)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:760)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:685)

    In Microsoft SQL server, the default schema name associated with a user must be the same as the user name.

    For example, if the Performance Data Warehouse database user name is perfDB then the default schema name associated with the user perfDB must also be named perfDB. You must create an ordinary database user and assign the required rights to the user instead of using a super user, such as sa. This is because the default schema for the super user is dbo and this cannot be changed.

    You can complete the following steps if existing tables are not associated with a schema that is the same as the user name.

    1. In SQL Server Management Studio Object Explorer, right-click the table name and then click Design.

    2. From the Design view, press F4 to view the Properties window.

    3. From the Properties window, update the schema name.
    4. Right-click the tab and select Close to close the Design view.

    5. Click OK when prompted to save. The selected table is transferred to the schema.
    6. Repeat the previous steps for all the tables in the Performance Data Warehouse database.


What to do next

When you create your database schemas, you must have a user ID with enough authority to create your tables. After the tables are created, the applications must have enough authority to select, insert, update, and delete information in the tables.

The following table shows the database privileges that are required to access the data store.

Database management system Minimum privilege required to use the data store tables Additional privilege required to create the data store tables
Microsoft SQL Server Configure the SQL Server for SQL Server so that authentication can be based on an SQL server login ID and password. The user ID can own the tables or be a member of a group that has sufficient authority to issue TRUNCATE TABLE statements. The user ID requires the CREATE TABLE statement privilege.
Isolation level specifies the transaction locking behavior.

You should set the isolation level to READ_COMMITTED_SNAPSHOT. You can check the isolation level of the Process Server database, the Performance Data Warehouse and the Common database with the following SQL command: SELECT  name, is_read_committed_snapshot_on FROM  sys.database. You can set the isolation level with the following SQL command: ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON.

Create databases and database design files for SQL Server