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 a connection to Db2 Big SQL by using the NodePort or Passthrough Secure Route method.
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:
- Log in to your OpenShift cluster as a project
administrator:
oc login OpenShift_URL:port
- Change to the project where the Cloud Pak for Data control plane is
installed:
oc project Project
- Obtain the NodePort number.
- Identify the Db2 Big SQL instance
ID.
./cpd-cli service-instance list --service-type bigsql --profile <profile-name>
- For a non-TLS port, run the following
command:
oc get -n project services bigsql-<instanceid>-jdbc -o jsonpath="{.spec.ports[?(@.name=='jdbc')].nodePort}"
- For a TLS port, run the following
command:
oc get -n project services bigsql-<instanceid>-jdbc -o jsonpath="{.spec.ports[?(@.name=='jdbc-tls')].nodePort}"
- Identify the Db2 Big SQL instance
ID.
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:
- Download the SSL certificate from the Db2 Big SQL instance page in the IBM Cloud Pak for Data web interface.
- 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>
- Add the following properties to the JDBC connection
URL:
For example:sslConnection=true sslTrustStoreLocation=/var/jdbc-client.jks sslTrustStorePassword=<truststore-password>
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.
- Log in to your OpenShift cluster as a project
administrator:
oc login OpenShift_URL:port
- Change to the project where the Cloud Pak for Data control plane is
installed:
oc project Project
- Identify the Db2 Big SQL instance
ID.
./cpd-cli service-instance list --service-type bigsql --profile <profile-name>
- 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
- Create the secure route by running the following
command:
oc create route passthrough bigsql-<instanceid>-jdbc-tls --service=bigsql-<instanceid>-jdbc-tls
- 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.
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.