Configuring an external database

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.
For example, to create a Db2 database instance, run the following 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.

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 choose 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

Configuring a database without SSL.
1. 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>
```
2. 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>
```
3. 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, sqlserver, or oracle>"
      database_servername: "<IP address>"
      dc_common_database_port: "<port>" 
      dc_common_database_name: "<database name>"
      dc_common_database_url: "<database url>" (Optional and only for oracle, example "jdbc:oracle:thin:@serverName:port:databaseName")
      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.

Configuring a database with SSL.

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.

Choice 1: Configure a Db2 database instance.
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=tls.crt=server.crt
```
Where server.crt is the Db2 SSL certificate public key in ASCII format.
```
-----BEGIN CERTIFICATE----- 
MIIHDzCCBfegAwIBAgIQCKZtYygfn9pg13D0uAX YzANBgkqhkiG9w0BAQsFADBg ... 3R7IrdK8aS1WUGlKulqEDiV4TJ 1XpcoUq8wtmBSw1fyV7g= 
-----END CERTIFICATE-----
```
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.

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 enter 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>"
```

Choice 2: Configure a PostgreSQL database instance.

There are two types of PostgreSQL SSL configuration:

Traditional password authentication with a driver that automatically retrieves the certificate
Certificate-based authentication

PostgreSQL supports certificate-based authentication for increased security of your connections. Certificate-based authentication is only available for SSL connections. Instead of a password, the client must provide a valid certificate.

Operational Decision Manager requires TLS version 1.2 to configure PostgreSQL on the server side. The verification level of the connection depends on the SSL mode (sslmode), as described in the following table.

Table 1. SSL modes
sslmode	Eavesdropping protection	MITM protection	Description
require	Yes	No	I want my data to be encrypted, and I accept the overhead. I trust that the network ensures I always connect to the server I want.
verify-ca	Yes	Depends on the Certificate Authority (CA) policy	I want my data to be encrypted, and I accept the overhead. I want to be sure that I connect to a server which I trust.
verify-full	Yes	Yes	I want my data to be encrypted, and I accept the overhead. I want to be sure that I connect to a server which I trust, and which is the one I specify.

For more information on the SSL modes, see https://jdbc.postgresql.org/documentation/ssl/.

To establish certificate-based authentication, you must create an SSL secret in the following way.

In require sslmode: The secret must contain at least the tls.key private key, and the tls.crt public key, in PEM format.

In verify-ca or verify-full sslmode: In addition to the two above keys, the secret must contain the ca.crt certificate of the PostgreSQL server Certificate Authority, in PEM format. Example:

kubectl create secret generic odm-db-ssl-secret --from-file=tls.crt \                                           
                                                  --from-file=tls.key \                                            
                                                  --from-file=ca.crt \
                                                  --from-literal=sslmode=[require|verify-ca|verify-full]

To define the secret for the database connection, you do not need to specify a password. The db-user key is sufficient. Example:

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

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 enter 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: "postgresql"
      database_servername: "<IP address>"
      dc_common_database_port: "5432" 
      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>"

Choice 3: Configure an Oracle database instance.
To configure Oracle SSL, you must specify PROTOCOL=TCPS in the URL parameter.
For example:
```
"jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=servername)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=servicename)))"
```
You must also provide the Oracle certificate as described in Importing the certificate of an external service.