Setting up an external Db2 database for OpenPages

You can use an Db2 database that is outside of IBM® Software Hub.

Before you begin

Ensure that your database server meets the following requirements:

The time zone of the external database must match the time zone of the OpenPages application container. For more information, see Setting the time zone of the OpenPages containers.
The external database server is geographically located as close as possible to your IBM Software Hub environment.
For the best UI performance, ensure that the ping interval (measured by the Linux® ping and db2 ping utilities) between the database server and the application server is under 1 millisecond.
Install Db2 on the external database server. Your server must meet the system requirements for OpenPages:
- See OpenPages version 9.1 on the Supported Environments page for the supported versions of Db2.
  Note: If you're using 11.5.8, install Special Build 37208 or later before you provision OpenPages.
- Only Db2 on Linux is supported.
- For OpenPages version 9.1 on IBM Software Hub version 5.2, the Db2 Client version that is used by the OpenPages application image is fixed at 12.1.
- For more information, click the Detailed system requirements link on the Supported Environments page.
The OpenPages application requires a user with DBADM and SECADM privileges for the initial deployment and for upgrades.
Create an operating system user that is named openpage for the schema owner. The name must be openpage and the name cannot be changed.
You need access to the IBM Entitled Registry or to a private container registry that contains the OpenPages packages.
If you want to use a tethered project for OpenPages, you must create the tethered project before you provision any OpenPages instances. For more information, see Tethering projects to the IBM Cloud Pak for Data control plane.

Procedure

Complete the following tasks to set up an external database:

Preparing the database
Configuring the parameters for the database setup script
Running the database setup script
Retrieving the certificate from the database server
Setting up a vault to store the database credentials
Creating a secret in OpenShift that references the database secrets
Verifying the installation

Preparing the database

Determine the version number of the external database package you need to download based on the version of IBM Software Hub that you are using. Use the following list for guidance.
- 5.2.0 9.1.0.1-82
- 5.2.1 9.1.1.1-653
- 5.2.2 9.1.2-48
Download the external database setup files and transfer them to the database server.
For example, you can use the following commands to get the files from the IBM Entitled Registry:
The following command extracts the OpenPages database backup file from the image:
```
docker cp $(docker create --rm cp.icr.io/cp/cpd/openpages-cpd-provisioner:<db_package_version>):/app-root/backup/ backup/
```
Replace <db_package_version> with the database package version number you chose in step 1. For example, the command will be the following if you're using IBM Software Hub version 5.2.2:
```
docker cp $(docker create --rm cp.icr.io/cp/cpd/openpages-cpd-provisioner:9.1.2-48):/app-root/backup/ backup/
```
The following command extracts the setup scripts from the image:
```
docker cp $(docker create --rm cp.icr.io/cp/cpd/openpages-cpd-provisioner:<db_package_version>):/app-root/externaldb/ externaldb/
```
Note: The image name (cp.icr.io/cp/cpd/openpages-cpd-provisioner:<db_package_version>) might be different if you mirrored the images to a local container registry.
The files that you need are:
- setup-extdb.sh: The main external database installation script
- setup-extdb.env: The environment file for the setup-extdb.sh script
- create-opx-db-srv.sh: The script that creates the database.
- OPX.0.db2inst1.DBPART000.<date_time_stamp>.001: The backup of the OpenPages database to restore
Create a database instance.
For example,
```
/opt/ibm/db2/V11.5/instance/db2icrt -a server -u db2fenc1 db2inst1
```
Where db2inst1 is the database instance owner and db2fenc1 is the database fencing user.
Decide how to set up the keystore. Choose one of the following options:
- Manually create a new keystore and SSL certificate.
- Locate an existing keystore and SSL certificate.
- Use the setup-extdb.sh to create a keystore and SSL certificate.
The Db2 database can be encrypted with a master key in the keystore. Decide which of the following options that you want to use:
- Use an existing master key.
- Manually create a master key.
- Use the setup-extdb.sh to create a master key.
- Do not use Db2 encryption.
Create an operating system user that is called openpage. This user is the schema owner in the database that is restored when you run the database setup script. You cannot change the name of this user.
```
useradd openpage
```

Note:

Verify that you're using the following database configuration parameters:

auto_ai_maint OFF
auto_reorg OFF
auto_del_rec_obj OFF

Configuring the parameters for the database setup script

Modify the setup-extdb.env file to match your environment.

Note: The parameter values that are shown in the following examples are included to help you configure the setup-extdb.env file. You must use the values for your environment.

You must configure the following parameters:

# hostname of the db2 server
export DB2SERVERNAME=opdb
# name of the db2 database to create
export DB2DBNAME=extdb
# OS user to use for db2 database creation
export DB2USER=db2inst1
# path to create the database
export DB2DBDIR=/home/db2inst1
# path to store the database data
export DB2STORAGEDIR=/home/db2inst1
# DB2 SSL port to configure
export SSL_DB_PORT=50051

If you want the script to set up a local keystore and create a self-signed SSL certificate, configure the following parameters:

# the following parameters are only needed if the {keystore} option is selected
# default name for a self-signed certificate
export SSCERT=${DB2DBNAME}_selfsigned_cert
# distinguished name for the self-signed cert
export SSCERTDN="CN=${DB2SERVERNAME},O=Sample Corp,L=Anytown,C=US"
# path to directory to store db2 keys
export KEYSTORE=${DB2DBDIR}/sqllib/security/keystore
# password for the keystore
export KEY_DB_PASSWORD=<password>
# path to gskit/bin in DB2 instance for key setup
export PATH=$PATH:$DB2DBDIR/sqllib/gskit/bin
export LD_LIBRARY_PATH=/opt/ibm/db2/V11.5/lib64/gskit_db2:$LD_LIBRARY_PATH

Review the following parameters. These parameters are used to restore the OpenPages data into the database.

# path to the db2 backup to restore
export RESTOREDIR=<path>
# date of the db2 backup to restore
export RESTOREDATE=<date_time_stamp>

Replace <path> with the directory where you downloaded the setup files, for example /home/db2inst1.

Replace <date_time_stamp> with the timestamp on the OPX.0.db2inst1.DBPART000.<date_time_stamp>.001 file that you downloaded.

Running the database setup script

Verify the following requirements:
- A database instance exists.
- The setup-extdb.env file and the create-opx-db-srv.sh script are in the same directory.
- The parameters in setup-extdb.env match your environment.

Run the setup-extdb.sh script.

Run the script as the database instance owner, for example, db2inst1.

Usage: setup-extdb.sh [OPTIONS]

Table 1. Options for the setup-extdb.sh script
Option	Description
-k	Create a keystore and a self-signed SSL certificate.
-d	Install the Db2 database.
-e	Encrypt the database; auto-create a master key.
-m <label>	Encrypt the database by using an existing master key label that is already configured in the keystore.
-v	Run in debug mode.
-h	Display the help text for the script.

If you do not specify any options, the script does the following actions:

Creates an encrypted database.
If a keystore is not already configured, create a keystore and a self-signed SSL certificate.
Creates a master key to encrypt the database.
Important: Back up the master key. If the master key is lost, the database is lost.

Table 2. Examples of setup-extdb.sh usage
Example	Description
`./setup-extdb.sh`	Equivalent to `./setup-extdb.sh -kde`
`./setup-extdb.sh -kde`	Creates a keystore and an SSL certificate, installs the database, and encrypts the database by using an auto-generated master key.
`./setup-extdb.sh -k`	Creates a keystore and an SSL certificate
`./setup-extdb.sh -de`	Installs the database and encrypts the database by using an auto-generated master key. The keystore must exist.
`./setup-extdb.sh -d -m keylabel`	Installs the database and encrypts the database by using a master key that is named `keylabel` that exists in the keystore. The keystore and the master key must already exist.

Trouble: If a problem occurs with the installation, do the following steps:

Terminate all connections to the database and drop the database.
For example,
```
db2 terminate && db2 drop database <database_name>
```
Run the setup-extdb.sh script again.

Retrieving the certificate from the database server

If you are using your own certificate, get it from the database server. You need the certificate when you create the database secret.

Note: If you used the -k parameter when you ran the setup-extdb.sh, skip this task.

For example, the following command retrieves a certificate that is called opdb_cert.pem:

openssl s_client -servername <db_server> -connect <db_server>:<db_port> </dev/null 2>/dev/null | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > opdb_cert.pem

Setting up a vault to store the database credentials

The database credentials must be stored in a vault. You can use the vault that is provided in IBM Software Hub or you can use an external vault. For more information about vaults, see Managing secrets and vaults.

Within the vault, you create two secrets: one for the DBA (db2inst1) username and password and another secret for the database user openpage username and password.

The DBA and database user secrets must be created by the same IBM Software Hub user.
The DBA and database user credentials must be stored in separate secrets within the same vault.
Each secret must be of the type username and password in the vault.
The secrets must either be owned by the user who provisions OpenPages instances or shared with the owner of the OpenPages instances.
OpenPages does not support storing the SSL certificate in a vault.

Do the following steps:

If you want to use an external vault, set up the vault. For more information, see Adding integrations to external vaults.
In the vault, add two secrets: one for the DBA user credentials and another for the database user credentials. Make a note of the secret details. For example, if you're using HashiCorp, note the secret names and paths.
In IBM Software Hub, set up references to the DBA user secret and the database user secret.
- If you're using an external vault, see Adding references to secrets in external vaults
- If you're using the IBM Software Hub vault, see Adding secrets to the internal vault
For each secret, use the following settings:
1. For the information type, choose Username and password.
2. Enter the database credentials.
3. If the user who will provision OpenPages is different from the user who is creating the secret, share the secret with the user who provisions OpenPages instances.
In IBM Software Hub, click Administration > Configurations, click the Vaults and secrets tab, and then click Secrets. Make a note of the following information for the DBA user secret and the database user secret:
- Name is the name of the secret.
- Vault name
- Created by is the owner of the secret.

Creating a secret in OpenShift that references the database secrets

Create a secret in OpenShift®. The secret references your vault and the database secrets that are defined in IBM Software Hub.

If you're using a tethered project for OpenPages, create the secret in the tethered project.

OpenPages uses the secret to connect to the database. The following example shows the required format. This example creates a secret that is called openpages-external-db-info. This example references secrets in the IBM Software Hub vault.

kind: Secret
apiVersion: v1
metadata:
  name: openpages-external-db-info
  namespace: <openpages_namespace>
  labels:
    app.kubernetes.io/name: openpages
    app.kubernetes.io/version: "9.001.0"
    app.kubernetes.io/managed-by: ibm-openpages-cpd-operator
stringData:
  host: <db_server_url>
  sslport: "5051"
  alias: extdb
  vault: <vault_name>
  secretowner: <created_by>
  usercred: "<db_user_secret>"
  dbacred: "<dba_secret>"
  opdb-cert.pem: |-
    -----BEGIN CERTIFICATE-----
    <SSL cert>
    -----END CERTIFICATE-----
type: Opaque

Table 3. Parameters for the secret
Parameter	Description
`name`	The name of the OpenShift secret, for example `openpages-external-db-info`
`namespace`	Use one of the following values: If you're using a tethered project for OpenPages, use the name of the tethered project. Otherwise, use the project where IBM Software Hub is installed.
`version`	The version of OpenPages that you want to install. For IBM Software Hub 5.2.2, use 9.5.2.
`host`	The URL of the external database server.
`vault`	The name of the vault. The name is displayed in the Vault Name column on the Secrets tab in IBM Software Hub.
`secretowner`	The owner of the secrets. The username is displayed in the Created by column on the Secrets tab in IBM Software Hub.
`usercred`	The name of the secret in IBM Software Hub for the database user credentials. The secret name is displayed in the Name column on the Secrets tab.
`dbacred`	The name of the secret in IBM Software Hub for the DBA user credentials. The secret name is displayed in the Name column on the Secrets tab.
`<SSL cert>`	If you are using your own certificate, paste it after `BEGIN CERTIFICATE`.

Save your changes, for example:

oc apply -f openpages-external-db-info

The DBA and database user credentials are ready to use.

When you provision OpenPages, you need to provide the name of the Kubernetes secret. In this example, the name is openpages-external-db-info.

Note: If the database connection information changes in the future, update the secrets in the vault. The connection information in the secrets must always match the values for your environment. For example, if the password of the openpage user changes, update the <db_user_secret> secret in the vault.

Verifying the installation

Review the log file, setup-extdb.<date_time_stamp>.log for any errors.

Verify that you can connect to the database by running the following command:

db2 connect to <database_name> user openpage using <password>

Results

After you complete this task, the external database has both TCPIP and SSL communication enabled, since the application runtime uses SSL.
Archive logging is not enabled by default. For instructions on how to set up archive logging, see the Db2 documentation: Configuring database logging options.

Note: The external database setup must be complete before you provision an OpenPages instance.

If you create the tethered project after you install the OpenPages service, you must complete some steps before you provision any instances. See Setting up a tethered project after you install OpenPages.