Setting up a connection to Db2 Big SQL

Before you begin

To manage Db2 Big SQL instances (by running the cpd-cli service-instance command), you must create a cpd-cli profile. The profile must be set up with the identity of a user that was granted access to the Db2 Big SQL instance. For more information about creating a cpd-cli profile, see Creating a profile to use the cpd-cli management commands.

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.

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.

      ./cpd-cli service-instance list --service-type bigsql --profile <profile-name>

   2. For a non-TLS port, run the following command:

      oc get -n project services bigsql-<instanceid>-jdbc -o jsonpath="{.spec.ports[?(@.name=='jdbc')].nodePort}"

   3. For a TLS port, run the following command:

      oc get -n project services bigsql-<instanceid>-jdbc -o jsonpath="{.spec.ports[?(@.name=='jdbc-tls')].nodePort}"

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

jsqsh -A --jdbc-url jdbc:db2://<publicly exposed or load balancer node>:<non-TLS NodePort>/bigsql -U datascientist1

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:

   jsqsh 1> \connect -U datascientist1 -S <FQDN> -p 32690 -d db2 -D bigsql -O sslConnection=true -O sslTrustStoreLocation=/var/jdbc-client.jks -O sslTrustStorePassword=<SSLtruststorepasswd>

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.

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.

   ./cpd-cli service-instance list --service-type bigsql --profile <profile-name>

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-<instanceid>-jdbc-tls
   spec:
     ports:
     - name: jdbc-tls
       port: 32052
       protocol: TCP
       targetPort: 32052
     selector:
       app: db2-bigsql
       bigsql-node-role: head
       instance: "<instanceid>"
     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 command shows how to connect to Db2 Big SQL with Jsqsh, by using the secure route that you obtained from step 5:

jsqsh -A --jdbc-url jdbc:db2://bigsql-<instanceid>-jdbc-tls-<Project>.apps.<publicly exposed or load balancer node>:<port>/bigsql -O sslConnection=true -O sslTrustStoreLocation=/var/jdbc-client.jks -O sslTrustStorePassword=changeit -U datascientist1

where <port> is the port that all traffic is routed over the secure route, typically 443.