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.
lock_timeout	for example, 60000	Abort any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object. The time limit applies separately to each lock acquisition attempt. The limit applies both to explicit locking requests (such as LOCK TABLE, or SELECT FOR UPDATE without NOWAIT) and to implicitly-acquired locks. If this value is specified without units, it is taken as milliseconds. A value of zero (the default) disables the timeout.

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.

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 with the default tablespace, 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.
Optionally, if you want to create the Application Engine playback server database with a separate tablespace, 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 tablespace and modify location as per your requirement 
create tablespace APP_ENGINE_TABLESPACE_NAME owner APP_ENGINE_DB_USER_NAME location '/pgsqldata/baspbdb'; 
grant create on tablespace APP_ENGINE_TABLESPACE_NAME to APP_ENGINE_DB_USER_NAME;

-- create database APP_ENGINE_DB_NAME 
create database APP_ENGINE_DB_NAME owner APP_ENGINE_DB_USER_NAME tablespace APP_ENGINE_TABLESPACE_NAME encoding UTF8;

-- 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_USER_NAME 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, for example, AAEDB. It is case-sensitive.
- APP_ENGINE_TABLESPACE_NAME is the tablespace name for the Application Engine database, for example, baspbdb_tbs. It is case-sensitive.
To create the Business Automation Studio database with the default tablespace, run the following command on your local or remote database server.
Note: If you use database.current_schema in your CR, replace the schema name (basadmin) with your customized schema name before running the script.
```
-- create user basadmin/basadmin
CREATE ROLE basadmin PASSWORD 'basadmin' CREATEDB CREATEROLE INHERIT LOGIN;

-- basdb
CREATE DATABASE basdb OWNER basadmin ENCODING UTF8;
\c basdb;
CREATE SCHEMA IF NOT EXISTS basadmin AUTHORIZATION basadmin;
GRANT ALL ON SCHEMA basadmin to basadmin; 
```
where:
- basadmin is the username that Business Automation Studio uses to connect to the database.
- basadmin is the password that Business Automation Studio uses to connect to the database.
- basdb is the database name for the Business Automation Studio database. It is case sensitive.
Optionally, if you want to create the Business Automation Studio database with a separate tablespace, run the following command on your local or remote database server:
```
-- create user basadmin/basadmin
CREATE ROLE basadmin PASSWORD 'basadmin' CREATEDB CREATEROLE INHERIT LOGIN;
-- modify location as per your requirement 
CREATE TABLESPACE basdb_tbs OWNER basadmin LOCATION '/pgsqldata/basdb';
GRANT CREATE ON TABLESPACE basdb_tbs to basadmin;
-- basdb
CREATE DATABASE basdb OWNER basadmin TABLESPACE basdb_tbs ENCODING UTF8;

\c basdb;
CREATE SCHEMA IF NOT EXISTS basadmin AUTHORIZATION basadmin;
GRANT ALL ON SCHEMA basadmin to basadmin;
```
where:
- basadmin is the username that Business Automation Studio uses to connect to the database.
- basadmin is the password that Business Automation Studio uses to connect to the database.
- basdb is the database name for the Business Automation Studio database. It is case-sensitive.
- basdb_tbs is the tablespace name for the Business Automation Studio database. 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 Optional: 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.