Creating databases manually

IBM® Business Process Manager Standard requires a Process Server database and Performance Data Warehouse database.. You must make sure the databases are available before you install IBM Business Process Manager Standard using a typical installation.

About this task

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.

These instructions are for DB2 and SQL Server only. For Oracle, you can use a single instance of Oracle for configuring BPM. The Oracle instance must exist and be available for access. Consult the Oracle documentation to create an Oracle instance. If you use a single Oracle instance, make sure that you use different user IDs for the different BPM databases.

Procedure

Use the following command to create the databases (replace the @ placeholder strings with the appropriate values):

For DB2:

create database @DB_NAME@ automatic storage yes  using codeset UTF-8 territory US pagesize 32768;
connect to @DB_NAME@;
-- A user temporary tablespace is required to support stored procedures in BPM
create user temporary tablespace usrtmpspc1;
grant dbadm on database to user @DB_USER@;
UPDATE DB CFG FOR @DB_NAME@ USING LOGFILSIZ 4096 DEFERRED;
UPDATE DB CFG FOR @DB_NAME@ USING LOGSECOND 64 DEFERRED;
connect reset;

For SQL Server:

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

You must configure XA transactions after the Microsoft SQL Server database is installed and before you start the server.

Note: For SQL Server, 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.

In SQL Server Management Studio Object Explorer, right-click the table name and then click Design.
From the Design view, press F4 to view the Properties window.
From the Properties window, update the schema name.
Right-click the tab and select Close to close the Design view.
Click OK when prompted to save. The selected table is transferred to the schema.
Repeat the previous steps for all the tables in the Performance Data Warehouse database.