Cloud Pak for Data

 View Only

IBM Cloud Pak for Data: Exposing the Db2 warehouse SSL JDBC port to external users

By BHARATH DEVARAJU posted Sun November 20, 2022 01:01 AM


 IBM Db2 Warehouse is an analytics data warehouse that features in-memory data processing and in-database analytics. It is client-managed and optimized for fast and flexible deployment, with automated scaling that supports analytics workloads.

Db2 warehouse on Cloud Pak for data exposes the JDBC ports for external users using OpenShift NodePort service.

In a highly available OpenShift cluster all the end user connections are going through a load balancer, by default only standard OpenShift ports are permitted by the load balancers.

 In the following article we’ll discuss steps for configuring external load balancer to forward the client JDBC connections to DB2 warehouse JDBC port listening on OpenShift NodePort.

  1. Identify the JDBC service ports for Db2 Warehouse, the command differs for SSL and non-SSL ports as shown following
oc get svc -n <project> <db2_service_name> -o jsonpath='{.spec.ports[?("ssl-server")].nodePort}

oc get svc -n <project> <db2_service_name> -o jsonpath='{.spec.ports[?("legacy-server")].nodePort}

- Replace <project> with the project name where CPD is installed, for example, zen

- Replace <db2_service_name> with the service name corresponding to db2wh, for example, c-db2wh-1668405592559722-db2u-engn-svc.
if you are unaware of the service name, you can query for OpenShift db2wh svc as following–

oc get svc | grep db2wh

If the command is successful, the output will show the corresponding port number as shown in the following output

Non-SSL Port
oc get svc -n cp4d c-db2wh-1668405592559722-db2u-engn-svc -o jsonpath='{.spec.ports[?("legacy-server")].nodePort}'

oc get svc -n cp4d c-db2wh-1668405592559722-db2u-engn-svc -o jsonpath='{.spec.ports[?("ssl-server")].nodePort}'


  1. Configure the external load balancer to forward the JDBC traffic to the Db2 NodePort, in the following example we are using haproxy as our load balancer,
  1. Update the haproxy config file (/etc/haproxy/haproxy.cfg), and append the following configurations, replace the port number (31692)  and IP address of OpenShift master nodes as applicable
frontend db2
        bind *:31692
        default_backend db2u
        mode tcp
        option tcplog

backend db2u
        balance source
        mode tcp
        server master0 <Openshift masternode1 IP>:31692 check
        server master1 <Openshift masternode2 IP>:31692 check
        server master2 <Openshift masternode3 IP>:31692 check


              b.  Restart haproxy service

systemctl restart haproxy

 c .Verify whether haproxy can connect to db2 Nodeports: Open the haproxy stats page, the db2u rows should appear as green as shown in the following image, if the rows appear as red then review the db2 nodeport number and firewall settings as applicable,



d. We can perform a connectivity test to the Db2 ssl port, following example uses openssl s_client to verify the identity of db2 server.


  openssl s_client <load balancer ip>:31692
  Can't use SSL_get_servername
  depth=1 CN = zen-ca-certificate
  verify error:num=19:self signed certificate in certificate chain
  verify return:1
  depth=1 CN = zen-ca-certificate
  verify return:1
  depth=0 CN = db2wh-internal-tls-certificate
  verify return:1
  Certificate chain
  0 s:CN = db2wh-internal-tls-certificate
    i:CN = zen-ca-certificate
  1 s:CN = zen-ca-certificate
    i:CN = zen-ca-certificate


  1. Steps for preparing the external JDBC URL to connect to the Db2 Warehouse server SSL port , 
    1. Login to CPD UI as an admin user, Goto Data -> Databases page , Select the Details menu for Db2 warehouse instance as shown following
   b. Using the details page shown following, download the SSL certificate and also copy the JDBC SSL URL

c. Replace the <CLUSTER_ACCESSIBLE_IP> , user and <password> parameters as applicable, for example here is the finished URL as per our scenario
d. The certificate downloaded is in pem format, since JDBC expects the certificate stored in jks format, using the following command to add the certificate to a java keystore, make a note of storepass which will be supplied in the JDBC URL,
keytool -import -alias db2u -file db2-ssl.cert -keystore test.jks -storepass password    
e. Append the keystore location and keystore password to your JDBC url as shown following

             f. If all the above steps were done correctly, external users can now connect using the JDBC URL and execute their queries using Db2 Warehouse service.



We have successfully exposed the Db2 JDBC SSL port to external clients.


  1. HAProxy -
  2. Openshift NodePort -