Monitoring IBM Db2

Supported versions

Currently, the supported versions for metrics and configuration data are v10.5 and above.
Monitoring of the Db2 instances with DATA_ENCRYPT authentication security mechanism is not supported.

The DB2 versions supported on Instana are listed in the following table:

Supported client-side tracing

For this technology, Instana supports client-side tracing for the following languages and runtimes:

• Java
• Node.js
• PHP
• .Net Framework
• .Net Core

General setup

To use a custom port, database, user, password, instance, or polling rate, you need to configure the following fields in the agent configuration file <agent_install_dir>/etc/instana/configuration.yaml:

com.instana.plugin.db2:
  local: # multiple local configurations supported
    - instance: 'db2inst1'
      port: '50000'
      user: 'db2inst1'
      password: 'password'
      poll_rate: 5 # seconds
      tabschema: 'schema1,schema2'
      databases:
        - 'database1'
        - 'database2'
        - 'database3'
        - 'database4'

com.instana.plugin.db2:
  remote:
    - host: 'host-1'
      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      sslTrustStorePassword: 'password'
      sslTrustStoreLocation: '/path/to/clientTrustStore.jks'
      availabilityZone: 'DB2 Remote Monitoring'
      poll_rate: 5 # seconds
      databases: # multiple databases
        - 'db'
        - 'sample' 

SSL/TLS support

For the Instana agent to securely connect to your Db2 server, it needs to be configured here <agent_install_dir>/etc/instana/configuration.yaml:

com.instana.plugin.db2:
  ...
  sslTrustStoreLocation: '/path/to/truststore.jks'
  sslTrustStorePassword: 'trustStorePassword'

com.instana.plugin.db2:
  remote:
    ...
    sslTrustStoreLocation: '/path/to/truststore.jks'
    sslTrustStorePassword: 'trustStorePassword'

Local monitoring for IBM Db2 in Red Hat OpenShift Container Platform

Instana supports local monitoring for IBM Db2 in Red Hat OpenShift Container Platform. The IBM Db2 database instances in the container environment are created as StatefulSets. They are rendered from the same pod template by using Custom Resources (CR). A sample configuration for IBM Db2 is shown in the following example:

com.instana.plugin.db2:
  local:
    - host: 'c-db2ucluster-sample-one-db2u.openshift-operators.svc'
      instance: 'db2inst1'
      port: '50000'
      environment: 'OCP'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      availabilityZone: 'DB2 Monitoring'
      poll_rate: 5 # seconds
      databases:
        - 'BLUDB'
    - host: 'c-db2ucluster-sample-two-db2u.openshift-operators.svc'
      instance: 'db2inst1'
      port: '50000'
      environment: 'OCP'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      availabilityZone: 'DB2 Monitoring'
      poll_rate: 5 # seconds
      databases: # multiple databases are supported
        - 'database1'
        - 'database2'

If multiple IBM Db2 instances exist in the same worker node, you can add them in the same configuration file.

The host URL of the IBM Db2 services is a combination of the service name and the namespace on the OpenShift Container Platform cluster in which the database instances are created. The URL is typically in the format " . .svc". For example, if "c-db2ucluster-sample-one" is the service name and "openshift-operators" is the namespace, the host URL is "c-db2ucluster-sample-one-db2u.openshift-operators.svc".

If you recreate a pod, use the same service name to preserve the database history in Instana.

Login timeout

Login timeout defines the maximum time in seconds that the sensor waits for connecting to the database. You can set a login timeout in the Instana Db2 sensor by using the loginTimeout property in the configuration.yaml file. If the connection is not obtained within the specified loginTimeout interval, then a connection error is displayed. The default value for this property is 5 seconds. The property is optional and is applicable for both the local and remote configurations. The following example shows how to customize theloginTimeout property:

      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      poll_rate: 5 # seconds
      loginTimeout: 20 #value in seconds

Custom interval for counting lock waits and long running queries

You can customize the time interval to get a count of the lock waits and long running queries in the Instana Db2 sensor by using the lockAndLongQueryInterval property in the configuration.yaml file. By default, the time taken by the sensor for classifying a query or lock wait as long running is 100 seconds. This metric can be used for alert or event configurations. The lockAndLongQueryInterval property is optional and is applicable for both the local and remote configurations. The following example shows how to customize this property:

      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      poll_rate: 5 # seconds
      lockAndLongQueryInterval: 60 # value in seconds

Custom interval for collecting runstats and reorg metrics

You can now customize the time interval for collecting runstats and reorg metrics. To customize your time interval, use the customRunStatsPollRate property in the configuration.yaml file. By default, the metrics are collected every 180 minutes. The customRunStatsPollRate property is optional and is applicable for both the local and remote configurations. The following example shows how to customize this property:

      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      poll_rate: 5 # seconds
      customRunStatsPollRate: 30 # value in minutes

Custom number of rows for table size metrics

You can now customize the number of rows that are displayed for table sizes per schema. To customize the number of rows, use the tableSizeRowsLimit property in the configuration.yaml file. By default, only the first 50 tables by size per schema are displayed. The tableSizeRowsLimit property is optional and is applicable for both the local and remote configurations. The following example shows how to customize this property:

      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      poll_rate: 5 # seconds
      tableSizeRowsLimit: 100 # number of rows to fetch

By configuring the tableSizeRowsLimit property, you can customize the number of rows displayed in the Table Sizes, Runtime Statistics, and REORG tables.

Token Authentication support

JSON Web Tokens (JWT) is a mechanism supported by Db2 for authentication, and is now supported by Db2 sensors from 1.0.27 version onwards. To make the Instana agent to support JWT connection to your Db2 server, you need to configure the <agent_install_dir>/etc/instana/configuration.yaml file as follows:

com.instana.plugin.db2:
  local: # multiple local configurations supported
    - instance: 'db2inst1'
      port: '50000'
      user: 'db2inst1'
      password: 'accessTokenHere'
      accessTokenType: 'JWT'
      sslTrustStoreLocation: '/path/to/truststore.jks'
      sslTrustStorePassword: 'trustStorePassword'
      poll_rate: 5 # seconds
      tabschema: 'schema1,schema2'
      databases: # multiple databases
        - 'db'
        - 'sample' 

com.instana.plugin.db2:
  remote:
    - host: 'host-1'
      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'accessTokenHere'
      accessTokenType: 'JWT'
      sslTrustStoreLocation: '/path/to/truststore.jks'
      sslTrustStorePassword: 'trustStorePassword'
      availabilityZone: 'DB2 Remote Monitoring'
      poll_rate: 5 # seconds
      databases: # multiple databases
        - 'db'
        - 'sample' 

Monitor multiple instances

Monitoring multiple instances is supported for both local and remote monitoring. Here is an example of how you can configure it within configuration.yaml file:

com.instana.plugin.db2:
  local: # multiple local configurations supported
    - instance: 'db2inst1'
      port: '50000'
      user: 'db2inst1'
      password: 'password'
      sslTrustStoreLocation: '/path/to/truststore.jks'
      sslTrustStorePassword: 'trustStorePassword'
      tabschema: 'schema1,schema2'
      poll_rate: 5 # seconds
      databases:
        - 'database1'
        - 'database2'
        - 'database3'
        - 'database4'
    - instance: 'db2inst2'
      port: '55000'
      user: 'db2inst2'
      password: 'password'
      sslTrustStoreLocation: '/path/to/truststore.jks'
      sslTrustStorePassword: 'trustStorePassword'
      poll_rate: 5 # seconds
      tabschema: 'schema1,schema2'
      databases:
        - 'database1'
        - 'database2'
        - 'database3'
        - 'database4'

com.instana.plugin.db2:
  remote:
    - host: 'host-1'
      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      sslTrustStorePassword: 'password'
      sslTrustStoreLocation: '/path/to/clientTrustStore.jks'
      availabilityZone: 'DB2 Remote Monitoring'
      poll_rate: 5 # seconds
      databases:
        - 'db'
      ...
    - host: 'host-n'
      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      sslTrustStorePassword: 'password'
      sslTrustStoreLocation: '/path/to/clientTrustStore.jks'
      availabilityZone: 'DB2 Remote Monitoring'
      poll_rate: 5 # seconds
      databases:
        - 'db'
        - 'sample'
      ...

IBM® Db2 pureScale

You can monitor Db2 pureScale instance both locally and remotely. The following examples show how to configure local and remote monitoring for a Db2 pureScale instance in the configuration.yaml file:

com.instana.plugin.db2:
  local: # multiple local configurations supported
    - instance: 'db2inst1'
      port: '50000'
      user: 'db2inst1'
      password: 'password'
      sslTrustStoreLocation: '/path/to/truststore.jks'
      sslTrustStorePassword: 'trustStorePassword'
      tabschema: 'schema1,schema2'
      poll_rate: 5 # seconds
      databases:
        - 'database1'

    - instance: 'db2inst2'
      port: '55000'
      user: 'db2inst2'
      password: 'password'
      sslTrustStoreLocation: '/path/to/truststore.jks'
      sslTrustStorePassword: 'trustStorePassword'
      poll_rate: 5 # seconds
      tabschema: 'schema1,schema2'
      databases:
        - 'database2'

com.instana.plugin.db2:
  remote:
    - host: 'host-1'
      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      sslTrustStorePassword: 'password'
      sslTrustStoreLocation: '/path/to/clientTrustStore.jks'
      availabilityZone: 'DB2 Remote Monitoring'
      poll_rate: 5 # seconds
      databases:
        - 'testdb'
      ...
    - host: 'host-n'
      port: '50000'
      tabschema: 'SYSTOOLS'
      user: 'db2inst1'
      password: 'db2inst1'
      sslTrustStorePassword: 'password'
      sslTrustStoreLocation: '/path/to/clientTrustStore.jks'
      availabilityZone: 'DB2 Remote Monitoring'
      poll_rate: 5 # seconds
      databases:
        - 'sample'
      ...

User permissions

You need to grant the following authorities to the Db2 user account that Instana uses:

• DATAACCESS authority
• DBADM authority
• SQLADM authority

or EXECUTE privilege on the following table functions/views or procedures:

• MON_GET_CONTAINER
• MON_GET_TRANSACTION_LOG
• MON_GET_DATABASE
• MON_GET_TABLESPACE
• MON_GET_PKG_CACHE_STMT
• MON_GET_AGENT
• MON_GET_HADR
• MON_GET_CONNECTION
• MON_GET_PKG_CACHE_STMT
• MON_GET_UTILITY
• MON_GET_WORKLOAD
• MON_GET_UNIT_OF_WORK
• MON_GET_INSTANCE
• MON_TRANSACTION_LOG_UTILIZATION
• PD_GET_DIAG_HIST
• PD_GET_LOG_MSGS
• SYSCAT.INDEXES
• SYSCAT.TABLES
• SYSIBMADM.MON_CURRENT_SQL
• SYSIBMADM.DB_HISTORY
• SYSIBMADM.DBCFG
• SYSIBMADM.MON_CONNECTION_SUMMARY
• SYSIBMADM.MON_DB_SUMMARY
• SYSIBMADM.MON_LOCKWAITS
• SYSIBMADM.SNAP_REORG
• SYSPROC.DB_GET_CFG
• SYSPROC.DBM_GET_CFG
• SYSPROC.ENV_GET_INST_INFO

Example:

db2 connect to db

db2 "grant dataaccess, dbadm, sqladm on database to user db2inst1"

db2 "grant execute on FUNCTION SYSPROC.MON_GET_DATABASE to USER db2inst1"

Custom polling

Notes:

• A polling configuration for IBM Db2 LUW sensor is enabled. With this feature, you can set a custom configuration for fetching stats at required intervals and also disable irrelevant stats.
• If you are using the custom polling configuration, this setting takes priority over the default system settings.
• The poll customization is enabled only for the stats that are outlined in the table in the Configurable stats section. The stats for which poll customization is not enabled are collected less frequently and are vital for the system. For more information about the collected stats and the default poll interval, see the Configurable stats section.
• You cannot set the interval configuration less than the default value. If you make an incorrect configuration, this entry is moved to the next higher poll-rate configuration bucket that is available.
• If you omit any stats from the configurable items (the stats is not added to any of the poll_intervals or disabled_metrics buckets), then it is automatically added to the next higher bucket available. However, if no such bucket exists, it is moved to the disabled metrics list.
• Poll intervals can be customized for both local and remote monitoring. Set all poll intervals in seconds.
• The stats names and bucket names (disabled_metrics, poll_rate_1, poll_rate_2, poll_rate_3, and poll_rate_4) are fixed. You need to name them as shown in the table in the Configuration stats section or as in the following sample configuration.

To customize polling for both local and remote monitoring, configure as follows:

      poll_rate: 5 # seconds
      custom_polling:
        disabled_metrics:
          metrics: DBM_CONFIG_STATS, AGENT_STATS #Stats that you don't want to be monitored
        poll_rate_1:
          poll_interval: 30
          metrics: TOP_QUERIES_STATS, TABLE_SPACE_STATS
        poll_rate_2:
          poll_interval: 200
          metrics: ELAPSED_TIME_STATS, WORKLOAD_STATS, TOP_TOTAL_STMT_IDS_STATS
        poll_rate_3:
          poll_interval: 900
          metrics: LOG_DISK_WAIT_STATS, DB_CONFIG_STATS, UNIT_OF_WORK_STATS
        poll_rate_4:
          poll_interval: 2000
          metrics: HADR_STATS, VMON_LOCK_STATS
      databases:
        - 'database1'

Configuration data

• Process ID
• Port
• Version
• Start time / Up time

Configurable stats

Performance metrics

Db2 pureScale performance metrics