Creating a PostgreSQL database

Create a PostgreSQL database before installing IBM Business Automation Studio

Before you begin

Use the latest PostgreSQL JDBC driver and make sure the PostgreSQL database is configured correctly for the customer workload.

Adjust the following parameters in the postgresql.conf file of the database server:

Table 1. PostgreSQL parameters
Parameter	Setting	Description
shared_buffers	minimum 1024 MB	The normal PostgreSQL performance tuning recommendation is to use about 25% of the memory for the shared buffer. Adjustments to the Linux® kernel configuration might also be required; check the PostgreSQL tuning guides.
work_mem	minimum 20 MB	This parameter applies to each session, and a large number of user sessions can cause large memory usage. This memory is critical because it is used for sort operations. The running time can increase significantly (over an hour for toolkit deployments, for example) if the value is set too low.
max_prepared_transactions	for example, 200	This value should be at least as large as the max_connections setting.
max_wal_size	for example, 6 GB	For larger workloads, the default value must be increased. You can find advice in the PostgreSQL server log files if an increase is required.
log_min_duration_statement	for example, 5000	You can optionally set this parameter. It allows additional logging of statements that exceed the specified running time in milliseconds (which corresponds to 5 seconds in this example) to identify bottlenecks and tuning areas.

About this task

You must create a database for Application Engine playback server as well as for IBM Business Automation Studio before you can run Business Automation Studio.

Restriction: You cannot use the same database and database user for Business Automation Studio and any other component, such as Application Engine playback server or IBM Business Automation Workflow. You can use a shared database but you must use a different database user for each component.

Procedure

To create the Application Engine playback server database, run the following command on your local or remote database server:
```
-- create a new user
create user APP_ENGINE_DB_USER_NAME with password 'APP_ENGINE_DB_PASSWORD';

-- create database APP_ENGINE_DB_NAME
create database APP_ENGINE_DB_NAME owner APP_ENGINE_DB_USER_NAME;

-- The following grant is used for databases
grant all privileges on database APP_ENGINE_DB_NAME to APP_ENGINE_DB_USER_NAME;
```
where:
- APP_ENGINE_DB_USERNAME is the username that the Application Engine uses to connect to the database.
- APP_ENGINE_DB_PASSWORD is the password that the Application Engine uses to connect to the database.
- APP_ENGINE_DB_NAME is the database name for the Application Engine database, it is case sensitive.

To create the Business Automation Studio database, run the following command on your local or remote database server:

-- create the user
CREATE ROLE STUDIO_USER_NAME PASSWORD 'STUDIO_USER_PASSW0RD' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

-- create the database:
CREATE DATABASE STUDIO_DB_NAME WITH OWNER STUDIO_USER_NAME ENCODING 'UTF8';
GRANT ALL ON DATABASE STUDIO_DB_NAME to STUDIO_USER_NAME;

-- Connect to your database and create schema
\c STUDIO_DB_NAME;
SET ROLE STUDIO_USER_NAME;
CREATE SCHEMA IF NOT EXISTS STUDIO_USER_NAME AUTHORIZATION STUDIO_USER_NAME;

where:

STUDIO_USER_NAME is the username that Business Automation Studio uses to connect to the database.
STUDIO_USER_PASSWORD is the password that Business Automation Studio uses to connect to the database.
STUDIO_DB_NAME is the database name for the Business Automation Studio database, for example BPMDB. It is case sensitive.