On December 2, 2008, IBM announced DB2 Performance Expert for Linux, UNIX, and Windows 3.2 and DB2 Performance Expert Extended Insight Feature 3.2. DB2 Performance Expert is a prerequisite for the new feature.
Distributed applications, although key to your business, often create serious challenges for DBAs who get panicked calls when response times go bad. Although a system administrator might be able to tell that there is a problem with a database application, any SQL request issued from that application travels a long way before it gets to the database and back to the user. Performance bottlenecks can occur anywhere in that stack—from the application and the application server to the network, the database, and the operating system.
A typical Java application environment in many shops includes a combination of an application server (such as WebSphere) with Java code accessing a database. This database is typically stored on yet another server connected via TCP/IP. DBAs working in this environment know how difficult and time consuming it can be to monitor and pinpoint problems in WebSphere applications accessing a remote database server.
Ideally, DBAs should have access to a complete view of database activities from the moment they are issued by the application through the application server, network, database, operating systems and back to the return of the result set to the application, so that they can quickly pinpoint where performance problems originate. In other words, rather than focusing on just the database server, DBAs need a solution that provides a holistic view across the entire database stack.
**The IBM vision for comprehensive end-to-end database monitoring includes providing relevant and consistent monitoring data to the various roles in an organization. This article focuses on a new extension to IBM DB2 Performance Expert, called IBM DB2 Performance Expert Extended Insight Feature, which helps DBAs find and pinpoint problems in a complex database and Java environment. These insights will help DBAs provide relevant data to their colleagues, improving collaboration and reducing the overall people cost for problem determination Over time, more and more task-related performance-related information will be available, for example, to Developers that will enable them to create enterprise-ready applications.
Figure 1 shows how difficult it is for the DBA to get a clear view of the entire process. DB2 Performance Expert 3.1 helps a DBA analyze what is going on inside the database server, but doesn't look beyond this. A product such as IBM Tivoli Composite Application Monitor for WebSphere (ITCAM for WAS) can help you identifying response time problems on the application server side, but its focus is on the application logic running inside the application server. ITCAM for WAS can tell you that some of your SQL statements inside your application are responding slowly, but it doesn't show why and where your whole application is spending its time between the application server and the database server.
Figure 1. Distributed applications
From a monitoring perspective, application response time is very important and will inform you when further attention is required. DBAs, however, are disconnected from the user experience because DBAs only monitor the part of the application they are responsible for: the database transaction. Unfortunately, today's monitoring is mostly done on a statement or connection level, which does not let you make any assumptions about the real user experience. It can only be used to take corrective actions when a user starts screaming, or in a proactive way to try to avoid problems by optimizing the resource usage.
A user is experiencing response time problems with a WebSphere application and informs technical support, which then informs the WebSphere system administrator. The WebSphere administrator investigates the problem and can't detect anything unusual on the application server side. However, she sees that the application is accessing a database and asks the DBA to assist.
The DBA's first challenge is to identify the database connections that are involved. Unfortunately, all he can use to do this is the IP address of the application server, and information about when the problem occurred. Other helpful information, such as who was using it and which specific WebSphere application was used, is not available to the DBA. The same WebSphere server may host several applications and be used by hundreds of concurrent users. Therefore, the application server might have opened dozens of connections to this database.
The DBA has information about database activity but doesn't have a way to correlate that activity with information in the network, the database driver, the application server, or the application.
As a result, the reported information from the database and from WebSphere may not uncover exactly where the problem lies. Meanwhile, the unhappy user continues to experience unacceptable response time while the DBA, the network administrator, and system administrator investigate by collecting additional information that can help them pinpoint the problem area. This situation can take hours or days to resolve and involve many skilled IT staff.
Let's assume that the problem is in the DB2 database server. To find and fix a performance problem in the database server often requires high-level expertise and may mean a phone call to the one person in the organization who has that expertise. Today, monitoring and analyzing a DB2 database application requires comprehensive knowledge about how the database works internally and how your application uses its data.
Although most databases give you information, such as diagnostic logs, buffer pool and cache hit ratios or lock wait time, which you can use to analyze the activity in the database, you need to know which values are acceptable for these metrics. A buffer pool hit ratio of 80 percent might be very good for one buffer pool, but it could be a serious problem for another buffer pool. It depends on how the application accesses the data in the tables that are cached by the buffer pools.
DB2 Performance Expert 3.2 offers a new, optional feature, with breakthrough technology to help solve the problems faced by many IT staff today. The Extended Insight Feature, which is separately priced, provides end-to-end database monitoring, which connects the dots between the application, the network, and the database server and tackles the problem of measuring, understanding, and making visible the complete database request and response. The initial release focuses on WebSphere and other Java applications accessing DB2 data in Linux, Unix, or Windows.
The Extended Insight Feature makes problem determination easier and faster by showing the DBA where the application is spending its time between the start and end of the database transaction. It tracks and reports the time spent in each layer—from the Java application or WebSphere server, through the JCC driver, over the network, to the data server, and back.
Let's look more closely at how this new intuitive way of monitoring works. First, we'll look at how you can use the Extended Insight Feature to monitor predefined service agreements, and then we'll demonstrate how you can use the feature’s end-to-end database monitoring capability to find out exactly where time is being spent to hone in more quickly on problem areas in the application stack.
We mentioned earlier how DBAs are sometimes disconnected from response time issues in an application until it's time to move quickly to solve a problem. DBAs typically don't have the time to continuously monitor and observe the status of the database and the connected applications. Ideally, they want to be informed about slow database response times before the business is affected.
But what is an acceptable database response time? Usually, the IT department that owns the database for a given application is told how short the response time for an application should be and how many violations regarding the response times are acceptable. This agreement between the department owning the database and the department owning the application is called the service level agreement ( SLA).
With end-to-end database monitoring capability in the Extended Insight Feature, it is possible to measure the real database transaction response times the application experiences. This response-time metric includes sending the SQL statements to DB2, processing them on the DB2 side, sending the data back to the application, and processing the data on the application side. You set the threshold for database transaction response time, and DB2 Performance Expert lets you easily review how many transactions are violating the previously agreed-upon response time. In the example in Figure 2, 69 percent of the sales_shopping_cart application's database transactions have violated the predefined threshold.
Figure 2. Reviewing the status of database transactions.
This ability to monitor threshold violations enables you to proactively respond to performance issues instead of always being in the mode of reacting to complaints from users. WebSphere applications are automatically detected and monitored by DB2 Performance Expert with the Extended Insight Feature. You might need to tell DB2 Performance Expert how to identify other Java applications.
We've seen how the sales_shopping_cart application has violated its SLA. Now what? With DB2 Performance Expert and the Extended Insight Feature the DBA can easily drill down and review where the application spends its time. Figure 3 shows which layers involved in the database transaction are tracked.
Figure 3. Measuring where the application spends its time.
The DB2 Performance Expert screenshot (Figure 4) illustrates how those various layers are displayed graphically in the product. You can see how much time is spent in each of the layers. This response time is exactly what the application sees and is directly measured on the client side.
The good news is that the measurement is completely transparent to the application. You don't have to recompile or re-link the application. All you have to do is make sure that the latest IBM Data Server Driver for JDBC and SQLJ is installed together with a jar file from DB2 Performance Expert Extended Insight Feature
Figure 4. Graphic representation of where the application spends its time.
To analyze the problem, you would now start the analysis where your application spends most of its time. You will get all the information you need either to identify and fix the root cause or, in case of a problem outside of the database, to forward it to the colleague in your organization who handles these kinds of problems. And you'll have the data you need to validate with your colleagues that indeed the problem is likely to be in their area of control. For example, DB2 Performance Expert will include information about the network so that you can check if it is too slow, or if your application cannot use the full bandwidth because other network workload is consuming too much of it.
When dealing with WebSphere Application Server, you will have even more database-related data, because DB2 Performance Expert will provide WebSphere configuration information, which you can use as part of your root cause analysis. For example, you can compare the configurations of different WebSphere servers that host the same application to see if indeed a configuration problem could be causing an issue with database response times. In the example in Figure 5, the time the application must wait for a connection from WebSphere's connection pool is significantly longer on one of the WebSphere servers.
Figure 5. Comparing the database response times on WebSphere Application Servers.
We hope you enjoyed this sneak preview of the end-to-end database monitoring capability in the new Extended Insight Feature for IBM DB2 Performance Expert. This capability will help prevent slow-running applications from affecting your business by proactively alerting you to potential problems in the database stack. Proactive solutions to potential problems let you avoid continually working in reactive mode to solve users' urgent problems.
When a problem does occur, this new feature in DB2 Performance Expert will help DBAs collaborate more efficiently with other IT staff by providing them with additional information, reducing time and effort spent pinpointing performance problems in Java applications that access DB2 data.
Ute Baumbach is a software developer at the IBM lab in Germany and works as a member of the DB2 Performance Expert development team. In addition, Ute supports customers in setting up DB2 Performance Expert and utilizing it to its best advantage. She is an IBM Certified Database Administrator and a Certified Application Developer for DB2 UDB.
Torsten Steinbach works as a Senior Technical Consultant for DB2 and WebSphere® for IBM Partner Enablement in EMEA. He can be contacted at firstname.lastname@example.org.