Monitoring Db2 Warehouse On IBM Cloud with Instana

Over the last few months I’ve been increasing my knowledge and skills around Instana and recently I had the opportunity to explore its Db2 monitoring capabilities.

The situation I was facing was one of sporadic performance issues with heavy queries running against a Db2 Warehouse database. The database contains several column oriented tables which contain between 20 and 90million rows of data per table. The issue was presenting its self as a random failure in bactch jobs due to heap and temporary file system space being exhausted. Given the random nature I needed a way to monitor the key resources and performance aspects of the Db2 environment and hence I turned to Instana.

Instana has support for Db2 monitoring and the details on this can be found here. I am not going to cover the monitoring output in this blog but focus on how I went about setting the Instana agent to monitor my Db2 environment.

Firstly I need to point out that the approach I took was to extend the configuration of an Instana agent that I already had in place monitoring an OpenShift cluster (also running in the IBM Cloud) so the steps I took are focused on this environment. Also the Db2 Warehouse Instance that I need to monitor only allows SSL connectivity and hence I needed to configure the Instana agent with the necessary details to support this.

As the connection would be using SSL I needed to download the SSL certificate from the Db2 instance I wanted to monitor. To do this I accessed the Db2 Console for my instance.

Open Console

In the console I selected Settings (the spanner icon) and clicked on the Connections tab. From here I could download the SSL certificate.

Download SSL Certificate

With the certificate downloaded I needed to generate a JKS trust store as this is what the Instana agent needed to initiate the SSL connection. To generate the required trust store I used keytool on my Mac and executed the following command in a Terminal session:

keytool -import -file <Db2.crt> -keystore instana-db2.jks -storepass <store password> -alias instana-db2

Where:

1. <Db2.crt> is the path and file name to the SSL certificate downloaded from Db2
2. instana-db2.jks is the name of the JKS to be created
3. <store password> is the password to use for the JSK
4. The alias for the JKS is set to instana-db2

After the JKS had been created I needed to Base64 encode it ready for use in OpenShift. Again in my Mac Terminal session I executed the following command.

cat instana-db2.jks | base64

This commands displays the output in the Terminal window and I left it there so I could cut and paste it into my OpenShift configuration in the next step.

With the Base64 encoded JKS ready I next logged into my OpenShift Clusters console and using the “+” icon…

Add YAML

I added the following YAML to create a new secret in the instana-agent project. Of course “cutting and pasting” the Base64 encoded string created in the previous step over the top of the place holder <base64 encoded jks> shown below.

kind: Secret
apiVersion: v1
metadata:
   name: db2-jks
   namespace: instana-agent
data:
   keystore.jks: >-
   <base64 encoded jks>
type: Opaque

Once I had created the required secret I needed to update the Instana Agent daemonset to mount the secret as path in the agent pods. The updates I made are shown below (I’ve removed a lot of the standard details to high-light the additions)

kind: DaemonSet
apiVersion: apps/v1
...
namespace: instana-agent
labels:
   ...
spec:
   selector:
      ...
   template:
      metadata:
         ...
      spec:
         ...
         containers:
            ...
            env:
               ...
            securityContext:
               privileged: true
            ports:
               - hostPort: 42699
                 containerPort: 42699
                 protocol: TCP
            imagePullPolicy: Always
            volumeMounts:
               ...
               - name: db2truststore      <————<<
                 mountPath: /truststore
            terminationMessagePolicy: File
            image: 'instana/agent:latest'
         ...
     serviceAccount: instana-agent
     volumes:
        ...
        - name: db2truststore     <————-<<
           secret:
              secretName: db2-jks
              defaultMode: 420
     ...

These changes allowed me to mount the created secret under the /truststore mount point within the pods created by the daemonset. With the secret now available to the the pods I could update the Instana Agent configmap to use the truststore. To do this I edited the configmap to add the following.

com.instana.plugin.db2:
  remote:
    - host: '<my db2 host name>'
      port: '50001'
      tabschema: 'SYSTOOLS'
      user: '<my user>'
      password: '<my user password>'
      sslTrustStorePassword: 'Ins4n4d82'
      sslTrustStoreLocation: '/truststore/keystore.jks'
      availabilityZone: 'CDL Db2 Monitoring'
      poll_rate: 50 # seconds
      databases: # multiple databases
        - 'BLUDB'

I’ve blanked out the sensitive data in the above re host, user and password but these matched whats set in my Db2.

With the configmap updated I restarted my Instana agent pods and my Db2 server appeared in the infrastructure view within the “Db2 Remote Monitoring” availability zone.

Db2 in Instana

Double clicking on the DB2 box allows me to open the “Dashboard”.

Open dashboard

Allowing the monitoring information for Db2 to be accessed.

Db2 moniotoring information

Using the information that Instana collected I’ve been able to work with a colleague who is far more Db2 literate than I am to get a clearer view as to whats happening and this is allowing us to address the issue.

So the steps to set up the monitoring were pretty simple :-)