Stan's Corner

 View Only

IBM DB2 LUW Monitoring and Performance Management with Instana

By SANDHYA R posted Mon August 02, 2021 08:08 AM

  
Co-Authors

Padmini K
Vijesh Solomon
Vipin Menon

 

What is IBM Observability by Instana?

One of the advantages of AIOps is to find and troubleshoot problematic requests and services. IBM Observability by Instana automatically detects changes, issues and incidents to help you understand the quality of services and investigate service issues of your applications. Instana provides different monitoring capabilities for IBM Infrastructure and middleware components (e.g., IBM Db2, IBM J9, IBM MQ).

 

Instana collects data from monitored systems by using a single agent on each host. The agent runs on your host, collecting and aggregating data from various sensors, before sending it to the Instana server.

 

In the case of IBM Db2 LUW, the monitoring is done remotely. This means the agent resides on a remote machine and it connects to the Db2 Server with the credentials passed into the agent configuration file. This configuration helps the agent to collect the data from any host remotely without running on the respective monitoring system.


816eb157-bebc-4a18-a9e4-6062777eceed
Figure 1


Installation

The details of installation of Db2 on Linux and Unix can be found here.

Configuration

As shown in Fig: 2, the fields containing the port, database name, user and password need to be configured in the agent configuration file located at <agent_install_dir>/etc/instana/configuration.yaml . The configured remote Db2 instance will then be shown as a separate block on the Instana GUI in the specified availability zone.

Figure 2

Metrics (Db2 LUW Performance Metrics )

 

For IBM Db2 LUW monitoring , Instana collects the following metrics:

 

  •  Top/Long Running Queries: Returns the longest running SQL statements in the currently- connected database, such as Application Handle, Application Name, Client Application Name, Activity State, Activity Type, Elapsed Time, etc.

 

  • DB Configurations: Returns the values of individual entries in a specific database configuration file such as Configuration parameter name, Deferred value, value flags, etc.

 

  • DBM Configurations: Returns the values of individual entries in the database manager configuration file such as Configuration parameter name, Deferred value, value flags, etc.

 

  • Top Total CPU (MON_GET_PKG_CACHE): This allows the user to examine the aggregated metrics for a particular SQL statement, such as Executables ID, Percentage of total rows read and total CPU time, statement execution time, etc. With these metrics, you can quickly determine the reasons for poor query performance.

 

  • Runtime Stats: Runstats is a utility in Db2 that is used to collect statistics about the data in Db2 tables and indexes such as Object Name, object Name qualifier, Start time, end time, duration, etc.

 

  • DB Utility: Retrieves information of all active utilities on all members using the MON_GET_UTILITY table such as Application Name, Application Handle, Utility Operation Type, Object Type, Utility start time, utility type, utility detail, etc.

 

Fig:3  below shows a sample screen shot of Db2 metrics monitored by Instana.

Figure 3

Problem scenarios and Solutions

 

Analysing lock wait mechanism by using Instana

Problem Statement: A locking mechanism is a way to analyse the failure in the applications to complete tasks, or a slowdown in the performance of queries due to locks. However, the ideal objective is to have no lock timeouts, lock wait or deadlocks on a database system, which result in applications failing to complete their tasks. Excessive lock wait periods have a threat of turning into lock timeouts, which prevent an application from completing its tasks.

Solution: It is important to monitor the lock timeout, lock wait and dead locking events at all times. Instana captures a lot of information about activities happening in the application. The lock wait, deadlock and lock timeouts in Db2 are reported by Instana Observability, which is  the real-time monitoring, and helps to manage concurrency control.

To manage the Quality of Service of any applications, Instana helps to detect different events like Incidents, Issues and Changes. Fig: 4 shows an example of how to register an incident.

Figure 4

Instana reports these issues by providing the start time, end time, severity and the instance where the DB2 server is running. After going to the Events view of Instana GUI, you can choose between "Incidents", "Issues", "Changes" and "All" tabs to see corresponding event types. By clicking on one or selecting multiple bars in the events bar chart at the top, events table will list only the events, which are included in the selected bars. This allows detailed inspection of events without changing the current time interval. These monitored and reported issues will help you take necessary actions. Fig: 5, Fig: 6 and Fig: 7  show the triggered events and the details of the events, respectively.

Figure 5

Figure 6

Figure 7

Identify which application transaction was consuming transaction log space


Problem Statement:
One important aspect of all databases is the transaction log. If a transaction is too large, it will consume lots of space in the transaction log. It might be possible that the log file grows to maximum size and causes the SQL error "The transaction log for database is full".

Solution: Instana helps to get the root cause quickly and resolve the performance problem of the transaction activity. The Fig: 8 shows the screenshot from Instana for the Db2 transaction log that is monitored. This will help us to visualise the used and available log space.

Figure 8

IBM Observability by Instana comes with hundreds of out-of-the-box curated capabilities that are evaluated continuously against the incoming metrics and are used to raise events or incidents depending on the user impact. While Built-in events are the predefined health signatures based on integration algorithms, which help you to understand the health of your monitored system in real-time, Custom events trigger issues based on the thresholds of an individual metric of any given entity. With IBM Observability by Instana, you have a real-time view of components that impacts Db2 LUW and understand all factors to resolve issues faster. It gives your team the benefits of observability and application performance management, there by helping to achieve the main goal of simplifying Db2 LUW service quality monitoring.

 

References

https://www.instana.com/supported-technologies/db2-monitoring/

https://www.instana.com/docs/self_hosted_instana/

https://www.instana.com/docs/setup_and_manage/host_agent/on/linux/


#Instana
#installation
#configuration
#observability
#how-to

Permalink

Comments

Tue April 25, 2023 06:27 AM

Hi Sandhya,
I think everything running well :
root@p1377-pvm1:/opt/server_dec # cat /etc/services | grep db2c_db2inst1
db2c_db2inst1   25010/tcp
db2c_db2inst1   50000/tcp
root@p1377-pvm1:/opt/server_dec # db2 update database manager configuration using svcename db2c_db2inst1
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
root@p1377-pvm1:/opt/server_dec # db2set DB2COMM=tcpip
root@p1377-pvm1:/opt/server_dec # db2stop
04/25/2023 06:16:58     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
root@p1377-pvm1:/opt/server_dec # db2start
04/25/2023 06:17:12     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

But the problem still happen:
Cannot connect to jdbc:db2://129.40.98.129:50000/test using user db2inst1.

Do you have any insights for this?
And for firewall on AIX, i think already disable from techzone.

Tue April 25, 2023 03:41 AM

Hi Ahmad,

The problem might be TCP/IP is not properly enabled on your DB2 database server.
suggestions:
check for the port in
cat /etc/services | grep
db2c_db2inst1
try the below:
db2 update database manager configuration using svcename db2c_db2inst1
db2set DB2COMM=tcpip 
db2stop 
db2start

Mon April 24, 2023 10:51 PM

Hi, please need advice, I already following the configuration.yaml like above but in Instana dashboard appear alert like this:
Cannot connect to jdbc:db2://129.40.94.129:50000/test using user db2inst1
And I attach my log from agent.log :
 
2023-04-24T06:16:18.242-04:00 | INFO  | 5ed-000c-4fe6-8603-39f4c0bd03fa) | DB2              | com.instana.sensor-db2 - 1.0.35 | Activating DB2 Sensor
2023-04-24T06:16:18.248-04:00 | ERROR | 5ed-000c-4fe6-8603-39f4c0bd03fa) | DB2              | com.instana.sensor-db2 - 1.0.35 | Cannot connect to jdbc:db2://127.0.0.1:50000/test using user db2inst1. [jcc][t4][2043][11550][4.28.11] Exception java.net.ConnectException: Error opening socket to server /127.0.0.1 on port 50,000 with message: Connection refused (connect failed). ERRORCODE=-4499, SQLSTATE=08001
2023-04-24T06:26:11.495-04:00 | ERROR | instana-scheduler-thread-2-3     | DB2              | com.instana.sensor-db2 - 1.0.35 | DB2 Connection is not active, Removing sensor
2023-04-24T06:26:11.500-04:00 | INFO  | 5ed-000c-4fe6-8603-39f4c0bd03fa) | DB2              | com.instana.sensor-db2 - 1.0.35 | Deactivated Sensor for db2inst1:50000/test, connection URL:jdbc:db2://127.0.0.1:50000/test
2023-04-24T06:26:13.500-04:00 | ERROR | instana-scheduler-thread-2-3     | DB2              | com.instana.sensor-db2 - 1.0.35 | DB2 Connection is not active, Removing sensor
 
Btw, I already configure DB2 comunication, with db2set DB2COMM=TCPIP
But jdbc still cannot connect.
 
So, what should i do to solve this?
Thanks in advance

Mon August 29, 2022 01:44 AM

Hi Jun Liu,

Please find answer for your queries.

  1. The support for number of instances depends on the customer based on the setup like number of CPUs, memory, container limits, etc. 
  2. As for retention, 

          Retention is defined per granularity.         

        * 1 and 5 second granularity: Kept for 24 hours – different granularity       used depending on query period duration

        * 1 minute granularity: Kept for 1 month

        * 5 minute granularity: Kept for 3 months

        * 1 hour granularity: Kept for at least 13 months

Fri August 19, 2022 09:09 AM

How many Db2 instances can be supported in this architecture? Customer may have hundreds or even thousands instances. 
And how long historical data can be retained? Customer may need to keep months of data or even years of data for trend prediction?

Tue August 31, 2021 03:28 AM

The only bits that are missing in the docs are how to add the necessary JKS to an Openshift environment.  I did this by creating a secret and then updating the daemonset to mount that secret so I can reference it in the config.

Tue August 31, 2021 03:20 AM

Thanks for the update Tony. https://www.instana.com/docs/ecosystem/db2/#ssltls-support

Fri August 20, 2021 04:56 PM

Any views on how to configure Instana to monitor a Db2 instance which requires an SSL connection?  I have a instance running in the IBM Cloud I want to monitor but its requires SSL so my connection string needs to reference port 50001 and also pass sslConnection=true.  I can easily set the port number but I can't find a way to specify the sslConnection flag.

<UPDATE> Fixed issue, needed to pull down the cert from the Db2 Instance and create a JKS which was then added to Instana