Cloud Pak for Business Automation

Deploy and configure an external database alongside your BAW

Premise

In my role as technical sales during the preparation of Po(X) I often needed to use a database for the persistence of application data.
It is therefore a solution aimed only at development and/or PoX environments that do not require security, scalability, reliability, etc...
I've now found the time to share a series of indications and examples that can be useful to the community and customers who are approaching the world of containerization with CP4BA.

Used environment

For this deployment example I used an OpenShift ROKS cluster on IBM Cloud, however the exercise can be repeated on any other cloud provider certified for CP4BA. I installed Cloud Pak for Business Automation 22.x and deployed a Starter profile of type Authoring.

For how to install a CP4BA Starter Profile refer to the link https://www.ibm.com/docs/en/cloud-paks/cp-biz-automation/22.0.1?topic=openshift-installing-starter-deployments

Choice of containerized database

I chose to use PostgreSQL because it is well known in the developer community but also because it is already used by BAW in the authoring environment with a Starter-type deployment profile.
This example is easily adaptable to other databases such as MySql, DB2 and others.

Steps for deployment and configuration of PostgreSQL container

For the PostgreSQL deployment I used the 'postgresql-persistent' template already present in the OpenShift cluster.
I then saved the template to my workstation to do all the necessary preparations for deployment.
In addition to preparing the values to be used for the DeploymentConfig starting from the template, I also modified the deployment strategy in 'Rolling' mode so as to be able to add the management of a 'post-hook' event.
Through this 'post-hook' I defined the launch of a pod dedicated to the creation of a database and a schema (parameterizable values).

Looking at post-hook pod logs you can see the following lines

Creating database mydb...
CREATE DATABASE
Creating schema myschema...
CREATE SCHEMA
done !

Now you have a new PostgreSQL server pod '' using a PVC named 'my-postgresql' and reachable through service 'my-postgresql' at 'my-postgresql.test-postgres.svc.cluster.local'.


oc get pvc -n ${TNS} my-postgresql
oc get svc -n ${TNS} my-postgresql


Detailed steps can be found at link https://github.com/marcoantonioni/postgresql-openshift-deploy

Steps for configuration of BAStudio

Now let's move on to configuring 'CP4BA deployment' for the 'icp4adeploy' CR (in a Starter deployment profile).

Let's add the 'custom_xml' subsection in the 'bastudio_configuration' section with an xml snippet that defines the driver and datasource for accessing our db server.
Pay attention to the internal values if in your deployment you have changed the names of namespaces, services, etc... also change the references of this xml snippet.
Add the 'custom_xml' section like in the example here:

bastudio_configuration:

custom_xml: |-
<?xml version="1.0" encoding="UTF-8"?>
<server>
  <library id="PostgreSQLLibForMyDb">
    <fileset dir="/shared/resources/jdbc/postgresql" includes="* postgresql-42.3.3.jar postgresql.jar"/>
  </library>

  <dataSource commitOrRollbackOnCleanup="commit" id="jdbc/mydb" isolationLevel="TRANSACTION_READ_COMMITTED" jndiName="jdbc/mydb" type="javax.sql.XADataSource">
    <jdbcDriver libraryRef="PostgreSQLLibForMyDb"/>
    <connectionManager maxPoolSize="50" minPoolSize="2"/>
    <properties.postgresql URL="jdbc:postgresql://my-postgresql.test-postgres.svc.cluster.local:5432/mydb?currentSchema=myschema" user="postgres" password="post01gres"/>
  </dataSource>

  <transaction recoverOnStartup="true" waitForRecovery="false" heuristicRetryInterval="10" transactionLogDBTableSuffix="mydb" recoveryGroup="recover-group-bastudio" recoveryIdentity="_RIForMyDb">
    <dataSource transactional="false">
      <jdbcDriver libraryRef="PostgreSQLLibForMyDb"/>
      <connectionManager maxPoolSize="50" minPoolSize="2"/>
      <properties.postgresql URL="jdbc:postgresql://my-postgresql.test-postgres.svc.cluster.local:5432/mydb?currentSchema=myschema" user="postgres" password="post01gres"/>
    </dataSource>
  </transaction>
</server>

Save the CR 'icp4adeploy' and wait for the CP4BA operator to update the content of the config map 'icp4adeploy-bastudio-overrides-configmap'; when the value with key 'custom.xml' will appear

Data

custom.xml
<?xml version="1.0" encoding="UTF-8"?>
<server>
<library id="PostgreS...

you can now force restart the pod 'icp4adeploy-bastudio-deployment-0'.

Once the new pod has been restarted, check for the presence of the xml snippet in the folder

oc exec -n cp4ba icp4adeploy-bastudio-deployment-0 -- ls -al /opt/ibm/wlp/usr/servers/defaultServer/configDropins/overrides

oc exec -n cp4ba icp4adeploy-bastudio-deployment-0 -- cat /opt/ibm/wlp/usr/servers/defaultServer/configDropins/overrides/custom.xml

Now you may may deploy your apps using the jdbc datasource named "jdbc/mydb".

Database access test with simple application

You can test your configuration using the test app found in the 'apps' folder of the repo https://github.com/marcoantonioni/postgresql-openshift-deploy


Bonus track

Access to a containerized database is not a simple and immediate thing due to issues of network connectivity and access security.
As we have seen, the database for the persistence of application data has been deployed and made accessible to the BAStudio pod via a Service.
For the management of db administration operations (schemas, tables, etc..) things become less user friendly and so I looked for a possible solution.
With a few searches on the internet I found various open source applications for PostgreSQL database administration and I chose one for its simplicity of use and also and above all for the ease of being configured within a docker format image with complete compatibility with OpenShift and above all without the need for any security configuration.
You can find the deployment instructions at the link https://github.com/marcoantonioni/teampostgresql


I want to thank my colleagues Francesco Petrone and Chiara Venneri with whom I have gathered experiences on these issues and they have helped me to solve various problems.


References

Cloud Pak for Business Automation
https://www.ibm.com/docs/en/cloud-paks/cp-biz-automation/22.0.1

MySql in Openshift
https://docs.openshift.com/online/pro/using_images/db_images/mysql.html

DB2
https://www.ibm.com/docs/en/db2/11.5?topic=deployments-db2-red-hat-openshift
https://www.ibm.com/docs/en/db2/11.5?topic=installing-db2-openshift-cluster
http://heidloff.net/article/deploying-ibms-db2-on-openshift/