Connecting to Db2 ports and services

You can use the Db2 NodePort service or an external-facing Ingress Controller to connect applications to Db2 on Cloud Pak for Data.

Retrieving the Db2 port number

You can use the Db2 NodePort service to connect JDBC or ODBC applications to Db2 on Cloud Pak for Data.

You can find the NodePort by using the web console or commands:

Web console

After you deploy the database, the console displays the NodePort that applications should use for client connections in the JDBC Connection URL field of the Access information section of the database details page for both SSL and non-SSL connections.

Use the JDBC URLs on the database Details page to connect JDBC or ODBC applications.

Replace the <CLUSTER_ACCESSIBLE_IP> variable with either the infra IP address of the cluster that is retrieved with hostname -i, or the IP address of the SVC cluster IP where Db2 is installed. The <user> variable, if it is not automatically retrieved and filled in, must be replaced with the username, and the <password> variable must be replaced with the user's password.

Commands
The command differs for SSL and non-SSL ports:
SSL port
oc get svc -n ${PROJECT_CPD_INST_OPERATORS} <db2_service_name> -o jsonpath='{.spec.ports[?(@.name=="ssl-server")].nodePort}'
Non-SSL port
oc get svc -n ${PROJECT_CPD_INST_OPERATORS} <db2_service_name> -o jsonpath='{.spec.ports[?(@.name=="legacy-server")].nodePort}'

Where:

  • service_name is the unique identifier that is assigned to each Db2 deployment. The service name always starts with c-db2oltp, for example c-db2oltp-1605022957148004-db2u-engn-svc.
For more information, see Configuring ingress cluster traffic using a NodePort in the OpenShift® documentation:

Configuring the Db2 NodePort with an HA Proxy

If you use an external infrastructure node to route external Db2 traffic into the Red Hat® OpenShift cluster, the cluster might be in a private zone and you need to configure an external-facing HA Proxy to route the traffic to the OpenShift nodes.

About this task

Because Db2 is externally exposed through a NodePort, the HA Proxy also needs to expose the NodePort in order to allow traffic into the cluster.

The configuration below is only applicable with an HA Proxy. For more detail about configuring networking, see Understanding networking in the OpenShift documentation:

If you don't have an HA proxy, you can use the OpenShift TLS route. For more information, see Connecting to Db2 with an OpenShift TLS route.

Procedure

  1. On the infrastructure node, open the HA Proxy configuration file located at /etc/haproxy/haproxy.cfg.
  2. Run the following command to obtain the Db2 NodePort value: 
    oc get svc service-identifier -o jsonpath='{.spec.ports[*]}{"\n"}'
{"name":"legacy-server","nodePort":31505,"port":50000,"protocol":"TCP","targetPort":50000} {"name":"ssl-server","nodePort":30414,"port":50001,"protocol":"TCP","targetPort":50001}

    Where service-identifier is the Cloud Pak for Data identifier for the Db2 service, for example c-db2wh-1651177484587666-db2u-engn-svc.

  3. Modify the haproxy.cfg file to include the OpenShift NodePort: 
    frontend db2
        bind *:Db2 NodePort
        default_backend db2u
        mode tcp
        option tcplog
backend db2u
        balance source
        mode tcp
        server master0 Master0-privateIP:Db2 NodePort check
        server master1 Master1-privateIP:Db2 NodePort check
        server master2 Master3-privateIP:Db2 NodePort check
  4. Reload HA Proxy: 
    systemctl reload haproxy

Removing non-SSL ports in Db2

You can edit the Db2 configuration to remove non-SSL ports from your deployment to prevent non-SSL connections to the service and guarantee the highest security.

Procedure

  1. Run the following command to get the database instance identifier: 
    kubectl get pods --namespace=${PROJECT_CPD_INST_OPERANDS} | grep db2oltp

    The command returns a string that contains an instance identifier number, similar to the following example:

    c-db2oltp-1605722434029496-db2u-0

    In this example, the instance identifier of the database is 1605722434029496.

  2. Run the following patch to disable non-SSL port: 
    kubectl patch --namespace=${PROJECT_CPD_INST_OPERANDS} db2ucluster db2oltp-INSTANCE_ID --type=merge --patch '{"spec":{"environment":{"database":{"ssl":{"allowSslOnly": true}}}}}'
  3. Run the following command to ensure the corresponding db2uconfig ConfigMap has changed the value of DB2_ALLOW_SSL_ONLY to true: 
    kubectl get cm --namespace=${PROJECT_CPD_INST_OPERANDS} c-db2oltp-INSTANCE_ID-db2uconfig -o yaml | grep DB2_ALLOW_SSL_ONLY
  4. Apply the change to the Db2 pod by running the following command: 
    kubectl exec -it --namespace=${PROJECT_CPD_INST_OPERANDS} c-db2oltp-INSTANCE_ID-db2u-0 -- bash -lc "sudo rm -f /db2u/tmp/os_envar_configmap && apply-db2cfg-settings --setting regvar"
  5. Ensure that the Db2 instance has only enabled SSL by inspecting the db2 registry variable DB2COMM: 
    kubectl exec -it --namespace=${PROJECT_CPD_INST_OPERANDS} c-db2oltp-INSTANCE_ID-db2u-0 -- sh -c "su - db2inst1 -c 'db2set DB2COMM'"

    The output should be similar to the following example:

    Defaulted container "db2u" out of: db2u, init-labels (init), init-kernel (init)

SSL

Connecting to Db2 with an OpenShift TLS route

You can set up an OpenShift TLS route to connect to the Db2 service on Cloud Pak for Data from a Db2 client.

About this task

In order to externally access the Db2 database with a supported Db2 client (such as a JDBC client), you can use the NodePort. But in cases where the NodePort is not desired, you can edit the service that exposes the NodePort to transform the NodePort back into a ClusterIP. In place, you can define an OpenShift route over the Db2 TLS port.

Procedure

The following command creates an OpenShift TLS route with the following settings:
  • Passthrough route
  • insecureEdgeTerminationPolicy set to None
  • Use service c-${DB2UCLUSTER_CR_NAME}-db2u-engn-svc, for example:
    c-db2ucluster-sample-db2u-engn-svc
  • Target port 50001 or label associated with the port ssl-server

Substitute the hostname of the node where the Db2 service is running for <hostname> and where <route_name> is any Kubernetes supported object name.

cat << EOF | oc apply -f -
apiVersion: route.openshift.io/v1
kind: Route
metadata:
 name: <route_name>
 labels:
   formation_id: db2ucluster-sample 
spec:
 host: <hostname>
 port:
   targetPort: 50001
 tls:
   insecureEdgeTerminationPolicy: None
   termination: passthrough
 to:
   kind: Service
   name: c-db2ucluster-sample-db2u-engn-svc
   weight: 100
 wildcardPolicy: None
EOF