Table of contents

Setting up a connection to Db2 Big SQL

You must set up a connection to Db2® Big SQL before users can connect to Db2 Big SQL from a client application. You can set up the connection by using either the NodePort or the Passthrough Secure Route method.

NodePort method

You can use the Db2 Big SQL NodePort service resource to connect JDBC or ODBC applications to Db2 Big SQL on IBM® Cloud Pak for Data. A NodePort-type Service resource exposes a service on a specific port on all nodes in the cluster. The port is specified in the Service resource's .spec.ports[*].nodePort field.

Note: To use this method, the cluster administrator must ensure that external IPs are routed to the nodes and local firewall rules on all nodes allow access to the open port, as well as ensuring any timeouts set in the routing are appropriate to the workload requirements. For more information, see Configuring ingress cluster traffic using a NodePort in the Red Hat® OpenShift® documentation.

After you deploy Db2 Big SQL, you can find the NodePort by running the following commands:

  1. Log in to your OpenShift cluster as a project administrator:
    oc login OpenShift_URL:port
  2. Change to the project where the Cloud Pak for Data control plane is installed:
    oc project Project
  3. Obtain the NodePort number.
    1. Identify the Db2 Big SQL instance ID.
      oc get cm -l component=db2bigsql -o custom-columns="Instance Id:{.data.instance_id},Instance Name:{.data.instance_name},Created:{.metadata.creationTimestamp}"
    2. For a non-TLS port, run the following command:
      oc get -n project services c-bigsql-<instance-id>-db2u-engn-svc -o jsonpath="{.spec.ports[?(@.name=='legacy-server')].nodePort}"
    3. For a TLS port, run the following command:
      oc get -n project services c-bigsql-<instance-id>-db2u-engn-svc -o jsonpath="{.spec.ports[?(@.name=='ssl-server')].nodePort}"

Users can now connect to Db2 Big SQL by using the NodePort from a client application. For example, the following JDBC URL shows how to connect to Db2 Big SQL on the non-TLS port:

jdbc:db2://<publicly exposed or load balancer node>:<non-TLS NodePort>/bigsql:user=<username>;password=<password>;

To connect a JDBC client using a TLS NodePort, you must copy the certificate into a truststore file and provide its details to the JDBC connection by completing the following steps:

  1. Download the SSL certificate from the Db2 Big SQL instance page in the IBM Cloud Pak for Data web interface.
  2. Copy the certificate to the client host and then import it into a keystore file.
    keytool -import -keystore /var/jdbc-client.jks -file bigsql-ssl.cert -noprompt -storepass <truststore-password>
  3. Add the following properties to the JDBC connection URL:
    sslConnection=true
    sslTrustStoreLocation=/var/jdbc-client.jks
    sslTrustStorePassword=<truststore-password>
    
    For example:
    jdbc:db2://<FQDN>:<port>/bigsql:user=<username>;password=<password>;sslConnection=true;sslTrustStoreLocation=<path-to-truststore-file>;sslTrustStorePassword=<truststore-password>

Passthrough secure route method

A secure route has the advantage over NodePort in that it can automatically take advantage of the router that is deployed by default to your cluster, and functions as the ingress point for external network traffic. The router listens on the public host network interface, unlike the application pods that listen only on private IPs. The router uses a service selector to find the service and the endpoints that back the service, and creates routes that expose services as hostnames to be used by external clients. For more information on how applications are exposed with routes, see Exposing apps with routes in the IBM Cloud documentation.

To use this method, the connection must be made by using an SSL connection, using the certificate that you download from the Db2 Big SQL instance page in the Cloud Pak for Data web interface.

Note: The passthrough secure route method is not supported when the public network interface is load balanced over multiple addresses. In this case, use the node port method instead.
  1. Log in to your OpenShift cluster as a project administrator:
    oc login OpenShift_URL:port
  2. Change to the project where the Cloud Pak for Data control plane is installed:
    oc project Project
  3. Identify the Db2 Big SQL instance ID.
    oc get cm -l component=db2bigsql -o custom-columns="Instance Id:{.data.instance_id},Instance Name:{.data.instance_name},Created:{.metadata.creationTimestamp}"
  4. Create a new service for the secure route by running the following command:
    cat << EOF | oc apply -f -
    apiVersion: v1
    kind: Service
    metadata:
      name: bigsql-<instance-id>-jdbc-tls
    spec:
      ports:
      - name: jdbc-tls
        port: 50001
        protocol: TCP
        targetPort: 50001
      selector:
        app: bigsql-<instance-id>
        name: dashmpp-head-0
      type: ClusterIP
    EOF
  5. Create the secure route by running the following command:
    oc create route passthrough bigsql-<instanceid>-jdbc-tls --service=bigsql-<instanceid>-jdbc-tls
  6. Confirm that the route was created:
    oc get route bigsql-<instanceid>-jdbc-tls

    This command should return a result such as:

    NAME              HOST/PORT                                                PATH      SERVICES          PORT       TERMINATION   WILDCARD
    
    bigsql-<instanceid>-jdbc-tls   bigsql-<instanceid>-jdbc-tls-<Project>.apps.<publicly exposed or load balancer node>             bigsql-jdbc-tls   jdbc-tls   passthrough   None

Users can now connect to Db2 Big SQL by using the secure route from a client application. For example, the following JDBC URL shows how to connect to Db2 Big SQL by using the secure route that you obtained from step 5:

jdbc:db2://bigsql-<instanceid>-jdbc-tls-<Project>.apps.<publicly exposed or load balancer node>:<port>/bigsql:user=<username>;password=<password>;sslConnection=true;sslTrustStoreLocation=<path-to-truststore>;sslTrustStorePassword=<truststore-password>
Notes:

As well as considering the router wide timeouts, the timeout on the Db2 Big SQL jdbc secure route can be customized by using a router annotation, as described in Configuring route timeouts in the Red Hat OpenShift documentation.

Although they are alternative ingress mechanisms, the NodePort and Passthrough Secure Route methods can coexist and be used interchangeably.