Summary
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.
- Identify the JDBC service ports for Db2 Warehouse, the command differs for SSL and non-SSL ports as shown following
SSL PORT
oc get svc -n <project> <db2_service_name> -o jsonpath='{.spec.ports[?(@.name=="ssl-server")].nodePort}
NON SSL PORT
oc get svc -n <project> <db2_service_name> -o jsonpath='{.spec.ports[?(@.name=="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[?(@.name=="legacy-server")].nodePort}'
32357
SSL PORT
oc get svc -n cp4d c-db2wh-1668405592559722-db2u-engn-svc -o jsonpath='{.spec.ports[?(@.name=="ssl-server")].nodePort}'
31692
- 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,
- 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
CONNECTED(00000003)
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
- Steps for preparing the external JDBC URL to connect to the Db2 Warehouse server SSL port ,
- 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
jdbc:db2://9.46.197.71:31692/BLUDB:user=admin;password=passw0rd;securityMechanism=9;sslConnection=true;encryptionAlgorithm=2"
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
jdbc:db2://9.46.197.71:31692/BLUDB:user=admin;password=passw0rd;securityMechanism=9;sslConnection=true;slTrustStoreLocation=/tmp/test.jks;sslTrustStorePassword=password;encryptionAlgorithm=2
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.
Conclusion
We have successfully exposed the Db2 JDBC SSL port to external clients.
ReferenceS
- HAProxy - http://www.haproxy.org/
- Openshift NodePort - https://docs.openshift.com/container-platform/4.8/networking/configuring_ingress_cluster_traffic/configuring-ingress-cluster-traffic-nodeport.html
#CloudPakforDataGroup#Highlights#Highlights-home