Creating PostgreSQL databases

Create PostgreSQL databases 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 user for Business Automation Studio and any other component, such as Application Engine playback server or IBM Business Automation Workflow. You can use the same database user for Business Automation Studio if the schema of all the databases created are the same. You can use a shared database but you must use a different database user for each component.

Tip: If you need to know which database versions are supported for your installation, use this IBM Support page to generate a software compatibility report.

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 playback server database, for example, APPDB. 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 BASDB. It is case sensitive.

Optional: To use your own JDBC driver, complete the following steps.
1. Package your JDBC files into a compressed file and use the sc_drivers_url configuration parameter to download them from an accessible web server. Follow the steps in Preparing customized versions of JDBC drivers and ICCSAP libraries.
2. Add the customized JDBC driver information to the configuration parameters.
  - Set bastudio_configuration.database.use_custom_jdbc_drivers to true and insert your own JDBC driver files into bastudio_configuration.database.jdbc_driver_files in the configuration parameters. See Business Automation Studio configuration parameters.
  - Set application_engine_configuration.use_custom_jdbc_drivers to true in the configuration parameters. See Application Engine configuration parameters.

What to do next

To protect the configuration data you're going to enter, see Creating secrets to protect sensitive configuration data.