Configuring an external database

Edit online

ODM provides Db2®, PostgreSQL driver, Microsoft SQL Server, and Oracle drivers. You are responsible for the backup and recovery process of these databases.

Before you begin

Db2, PostgreSQL, Microsoft SQL Server, and Oracle are supported external databases. You can connect to an existing database or create and configure a new database.

Create an external database and give it a name <database name> for the Operational Decision Manager instance.
If you want to use a Db2 database, you can create an instance by running the db2 command on an external server.
```
db2 create database <database name>
```
Create one or more failover servers for the ODM database. To cover the possibility that the primary server is unavailable during the initial connection attempt, you can configure a list of failover servers, as described in Configuring client reroute for applications that use Db2 databases.

If you want to create a database for Decision Warehouse, you must define a buffer pool with a page size of 32K. BP32K is expected in SYSCAT.BUFFERPOOLS. Use the following command to query SYSCAT.BUFFERPOOLS for the existing buffer pool.
```
Select * from SYSCAT.BUFFERPOOLS
```
Otherwise, use the following commands to create a buffer pool named BP32K.
```
CREATE BUFFERPOOL BP32K SIZE 2000 PAGESIZE 32K;
CREATE TABLESPACE RESDWTS PAGESIZE 32K BUFFERPOOL BP32K;
CREATE SYSTEM TEMPORARY TABLESPACE RESDWTMPTS PAGESIZE 32K BUFFERPOOL BP32K;
```
Take note of the <IP address> of the instance so that you can add it to the custom resource file, or enter it in the user interface if you use the OpenShift console. If you are using a container-based database, use the service name (such as postgre-svc.postgre.svc.cluster.local) for the database server or database host property in the custom resource.

About this task

You can configure Db2, PostreSQL, SQL Server, or Oracle databases with or without enabling a secure sockets layer (SSL).

Procedure

Log in to your cluster with an administrator user to create a secret for the database connection. For example, on OpenShift use the oc login command.
```
oc login https://CLUSTERIP:8443 --token=<hidden>
```

Run the following kubectl command to create a secret to keep the login credentials.

kubectl create secret generic <odm-db-secret> --from-literal=db-user=<user_name> --from-literal=db-password=<user_password>

Note the value for the external database secret in the odm_configuration section of your custom resource file, or enter it in the user interface if you use the OpenShift console.
Make a note of the data source secret name, so you can add it to the datasource_configuration section of the custom resource file (dc_odm_datasource.dc_common_database_instance_secret).
The following datasource_configuration section must be included in the final custom resource.
```
datasource_configuration:
   dc_odm_datasource:
      dc_database_type: "<db2, postgresql, or sqlserver>"
      database_servername: "<IP address>"
      dc_common_database_port: "<port>" 
      dc_common_database_name: "<database name>"
      dc_common_database_instance_secret: "<odm-db-secret>"
```
<IP address> can be the service name (such as postgre-svc.postgre.svc.cluster.local) if container-based.
The following port numbers are the default values for the supported databases:
- db2: 50000
- postgresql: 5432
- sqlserver: 1433
- oracle: 1521
Note:
You can provide your own driver to use your own external database by setting the sc_drivers_url parameter. For more information, see Preparing customized versions of JDBC drivers and CCSAP libraries.

For more information, see Shared configuration parameters.
The SSL/TLS protocol is enabled by default to secure the database connection, so make sure the dc_common_ssl_enabled parameter is set to true in your custom resource and specify the name of the SSL secret for Db2. Create a secret that contains the SSL certificate for Db2 by running the following command.
```
oc create secret generic odm-db-ssl-secret --from-file=db2-server-certificate=server.crt
```
Where server.crt is the Db2 SSL certificate public key in ASCII format.
```
-----BEGIN CERTIFICATE----- 
MIIHDzCCBfegAwIBAgIQCKZtYygfn9pg13D0uAX YzANBgkqhkiG9w0BAQsFADBg ... 3R7IrdK8aS1WUGlKulqEDiV4TJ 1XpcoUq8wtmBSw1fyV7g= 
-----END CERTIFICATE-----
```
For more information about generating the SSL certificate, see Self-signing digital certificates.

Make a note of the SSL secret name, so you can add it to the datasource_configuration section of the custom resource file (dc_odm_datasource.dc_ssl_secret_name), or entire it in the user interface if you use the OpenShift console.

The following odm_configuration section must include the two SSL parameters in the final custom resource.
```
datasource_configuration:
  dc_odm_datasource:
      dc_database_type: "db2"
      database_servername: "<IP address>"
      dc_common_database_port: "50000" 
      dc_common_database_name: "<database name>"
      dc_common_database_instance_secret: "<odm-db-secret>"
      dc_common_ssl_enabled: true
      dc_ssl_secret_name: "<odm-db-ssl-secret>"
```
Note: If your database is postgreql or sqlserver, the datasource and the driver automatically retrieve the certificate. In these cases, you only need to set the parameter dc_common_ssl_enabled to true.