This section includes the following topics:
- About the Current tab
- How the Current tab is structured
- About examining entities in the Current tab
- How the Current tag can help you identify performance problems
About the Current tab
The Current tab displays near real-time information on all applications that were connected to your DB2 database during the last minute or, in the Application context, from the beginning of the time slice. If you have discerned an immediate performance issue and are looking for an indication as to where to begin your analysis, start with the Current tab. It is also worthwhile to start with the Current tab if your system appears to be 'hung' and there is no throughput.
You can use this tab to monitor your system’s current activities and resource consumption and to identify performance problems, such as current locking problems and applications involved in locks, as they occur.
The Current tab provides answers to the following types of questions: "How many applications are currently running?" or "Why doesn’t my application work when I click OK?" The Current tab also shows you which applications are currently active, and which machines are connected to which users.
Using the Current tab, you can monitor current connections to your DB2 database partition, check the state of each connection, and identify the resources currently utilized by each application. Further investigation of each application provides a thorough understanding of the SQL statement being executed, the login name executing the statement, the program, and the resource utilization for the application. In addition, you can view the statements executed by the selected application within the last time slice.
In the Current tab, data on a current application is continuously updated to provide information on what happened during the last minute or time slice. Because the displayed application information is derived from a dynamic application, it is recommended to regularly refresh the screen, either manually or automatically, to ensure that the information displayed is up-to-date.
The Current tab displays information on the last minute of activity or, in the Application context, from the beginning of the time slice. For example, if you’ve worked at your screen for ten minutes and then refresh the information displayed on your screen, you will be able to view new information that occurred during the last minute of activity or from the beginning of the time slice in the Application context.
See Analyzing currently held locks and “Getting an overview of your application” on page 23.
How the Current tab is structured
The Current tab displays information on a selected entity and its associated entities. For example, if the parent entity is an application, its associated entities would be Statements.
When you open the Current tab from the Dashboard tab, the selected entity is by default a database partition, meaning that information is displayed on the database partition level. If you open the Current tab from another tab, the historical settings (meaning those settings which were selected when you left the tab, such as a drill-down to a Locked Objects entity) are taken into account and the information displayed the last time you viewed this tab is displayed (similar to clicking the History button and returning to a previous tab).
The selected entity is always reflected in the Tab heading, which serves as a point of orientation. The highest-level entity you can view information for in the Current tab is database partition. You can view another database partition by selecting it from the database partition list.
About the Main area in the Current tab
The Main area shows comprehensive information on the selected entity. You can choose from several views to examine the entity from different angles. For example, if you are analyzing a database partition entity, you can, focus exclusively on the data displayed in the overview, such as, In DB2 data (default).
About the Association area in the Current tab
The Association area provides corresponding information on the entities associated with the selected entity (displayed in the Main area). The selection you make is reflected in the Association area only; the Main area remains unchanged. For some entities, tabs above the Association area enable you to view additional information. For example, the tabs displayed for a database entity are: Application, In DB2, and Connection. Clicking a tab displays different table columns with the same type of child entities.
From the Association area, you can also drill down to another entity by clicking a table row. A drill-down affects the entire tab. When you drill down to another entity, the Tab heading displays the new selection, the Main area displays information on the newly selected entity, and the Association area displays the entities associated with the selected entity.
For example, when you want to view information on a specific application in the Current tab, choose Applications from the Association controls. The Association area changes to display application statement information. Note that the Tab heading remains unchanged and the Main area changes to application context.
Locks can be viewed in both an application context and a data partition context. In the application context you can see which application is locking you. In the data partition context a lock tree is displayed showing details of all locks holding the application.
If you want to view detailed information on a specific application holding lock, click the row of the applications holding locks you want to view detailed information for. The Tab heading indicates the newly selected entity; the Main area displays locking and In DB2 information on the application you drilled down to, and the Association area shows information on the statements associated with this application.
See “Precise for DB2 basics” on page 14 and “Getting an overview of your application” on page 23.
About examining entities in the Current tab
The Current tab displays information on different entities. This section provides an overview of all entities, their meaning, and their views.
The following entities can be examined in the Current tab:
- About the Database Partition
- About the Application entity
- About the Application Holding Locks entity
- About the Statement entity
About the Database Partition
Displays information on the activity and resource consumption of the database, including the number of applications connected to the database during the last sample taken and the database resource consumption breakdown for the last minute.
About getting an overview of current Database activities
Displays information on the activity and resource consumption of the database, including the number of applications connected to the database during the last sample taken and the database resource consumption breakdown for the last minute.
The following table describes the information displayed in the Database Overview view.
Table 1 Database Overview view
| View area | Description |
|---|---|
| Application | Displays the following application-related information:
|
| In DB2 | Displays the resource consumption breakdown of the database during the last minute or, in the Application context, from the beginning of the time slice. In addition to the resource distribution, the view displays the number of applications in each state during the last sample. The following information is displayed:
All the information displayed in this table represents information collected during the last minute, with the exception of the Applications column, which represents the number of applications, in the last sample, that were in a particular state. |
About viewing Database locks
Displays lock wait duration for the selected Database during the last minute. The applications that are currently involved in a locking or blocking situation are displayed in tree format.
Since the Lock tree represents the locks that are currently occurring, and the lock breakdown represents the duration of locks occurring during the last minute, it is possible that a situation may occur whereby a lock was freed, but since the last minute has not yet passed, the lock tree will display the message ‘No locks detected' while the Lock Breakdown grid still shows lock duration.
The following table provides a detailed explanation of the Lock Tree
Table 2 Database Locks view
| View area | Description |
|---|---|
| Lock tree | It is important to determine the following information for each blocking chain:
The Lock Tree provides the necessary information to get to the root of the problem by providing information on applications currently involved in the blocking situation. A yellow lock indicates that the application is currently locking other applications but is not itself blocked. A red lock indicates the application is currently blocked by another application. Note that an application application may also block other applications while being blocked by itself. To gain a better understanding of the lock tree take a closer look at its structure. Notice that the application located in the root of each chain is the application that blocks all others. Each application directly blocks all applications positioned one level beneath it. The Lock Tree displays the application handle, program name, the object being blocked and the lock type. |
About the Application entity
Displays information on a single connection with Microsoft SQL Server. Precise for SQL Server identifies an application by the following attributes:
- Application ID. A unique integer assigned to each user connection when the connection is made.
- Application identifiers such as program or database user.
When one of these attributes changes, a new application is recorded and displayed. The following table describes the information displayed in the Application overview.
Table 3 Application Overview
| View area | Description |
|---|---|
| Application tab | Displays the following information:
|
| In DB2 tab (last minute) | Displays the resource consumption breakdown of the application during the last minute (that is, 60 samples of the Collector). In addition to the resource distribution, the view displays the number of applications in each state during the last sample. It is important to remember that since this view only displays the last minute's activities, you will always be viewing the activities that occurred during the last minute and not the activities that occurred since the last refresh action. The following information is displayed:
|
| Connection tab | A connection includes application identifiers such as User, Login, and Machine. The following information is displayed:
|
The following table describes the information displayed in the Application Text view.
Table 4 Application Text view
| View area | Description |
|---|---|
| Text | Displays the text of the current statement. If the application is not active and executing a statement, a message notifying you that the application is not active and that no text is available is displayed. |
About the Application Holding Locks entity
The fact that a currently active application is holding a resource does not necessarily mean that there is a contention between resources. The Application Holding Locks entity displays information that lets you differentiate between applications that are locking resources and applications that are holding resources.
About getting an overview of current applications holding locks
Displays information on the application identifier, including connection details, application-related statistics and a breakdown of application resource consumption, during the last minute.
The following table describes the information displayed by the Application Holding Locks overview.
Table 5 Applications Holding Locks overview
| View area | Description |
|---|---|
| Application tab | Displays the following information:
|
| In DB2 tab (last minute) | Displays the resource consumption breakdown of the application during the last minute (that is, 60 samples of the Collector). In addition to the resource distribution, the view displays the number of applications in each state during the last sample. It is important to remember that since this view only displays the last minute's activities, you will always be viewing the activities that occurred during the last minute and not the activities that occurred since the last refresh action. The following information is displayed:
|
| Connection tab | A connection includes application identifiers such as User, Login, and Machine. The following information is displayed:
|
About viewing information on the lock breakdown of an application that is holding locks
The Locks view displays lock wait duration broken down into lock levels such as Table, Page and Key, for the selected application, during the last minute.
The applications that are currently involved in a locking or blocking situation with the selected application are displayed in tree format. Since the Lock tree represents the locks that are currently occurring, and the lock breakdown represents the duration of locks occurring during the last minute, it is possible that a situation may occur whereby a lock was freed, but since the last minute has not yet passed, the lock tree will display the message ‘No locks detected' while the Lock Breakdown grid still shows lock duration.
To gain a better understanding of the lock tree take a closer look at its structure. Notice that the application located in the root of each chain is the application that blocks all others. Each application directly blocks all applications positioned one level beneath it.
The Lock Tree displays the SPID, program name, the object being blocked and the lock type. The following table describes the information displayed in this view.
Table 6 Locks view for the Application Holding Locks entity
| View area | Description |
|---|---|
| Lock Tree | The Lock tree provides the necessary information to determine the following information for each blocking chain:
An application may also block other applications while being blocked by itself. |
About viewing the text of an application that is holding locks
Displays the text of the current statement. If the application is not active and executing a statement, a message notifying you that the application is not active and that no text is available is displayed.
About the Statement entity
The Statement entities display information on an SQL statement that is executed by the selected application during the last time slice.
About getting an overview of current Statement entities
Displays general information on the statement, including overall properties, its breakdown In DB2 during the last time slice, and the breakdown of the locks during the last time slice.
The following table describes the information displayed in the Statement Overview view.
Table 7 Statement Overview view
| View area | Description |
|---|---|
| General tab |
|
| In DB2 (Last minute) | The following information is displayed:
|
About viewing current Statement text
Displays text of the selected statement.
How the Current tag can help you identify performance problems
You can identify a performance problem by doing one or more of the following:
- Examining resource consumption of an entire database partition
- Observing the activity of a single application
- Examining a single connection
- Analyzing currently held locks
Examining resource consumption of an entire database partition
The Current tag provides an almost real-time picture of the performance and behavior of your DB2 database partition. The Current tag displays information on the last time slice of activity, unless otherwise configured as for the last minute. Usually you will examine the activities of the entire database partition level to determine what is happening in your system. By examining the current activity at a database partition level of display, you can see how it is currently behaving and determine if there is an overall problem that affects all applications or if there is a problem with specific applications.
To examine resource consumption of an entire instance and database
- In the database partition list, choose the database partition you want to analyze.
- On the View controls in the Main area, click Overview. This will show you a breakdown of resource consumption for the last minute.
Observing the activity of a single application
The Association area of the Current tag lets you view which applications are currently connected to the database partition. You can observe the current state of the application and determine if it is active or waiting for a resource. You can also identify the application by different identifiers such as its login information, the machine it is running on, or the programs it is running. The currently active TSQL code is displayed for active applications.
To observe current application activity
- In the database partition list, choose the database partition you want to analyze.
- On the Association controls in the Association area, select one of the following to focus your investigation on the applications that were active in SQL Server during the last minute:
- Active Applications
- Applications
Examining a single connection
You may need to explore a single running application, to obtain more information on it. In the Association area you can drill down to the specific application in question, and continue your analysis.
To examine a single connection
- In the database partition list, choose the database partition you want to analyze.
- In the Association area, drill down to the application whose connection you want to analyze.
- Continue your analysis by examining its resource breakdown and its recent statements, during the last few minutes.
Analyzing currently held locks
To be able to avoid a blocking situation, it is important to have an accurate picture of all the locks held in the system. This will help you understand if the lock system is tuned and if it is possible to avoid a potential blocking situation. Using Precise for DB2, you can now observe the locks currently held in the database partition and group them according to lock level and lock mode.
Locks can be viewed in two levels:
- Application context. To see which application is locking you.
- DB partition context. To see the Lock tree with details of all locks holding the application. To analyze locks currently held
- In the database partition list, choose the item you want to analyze.
- On the View controls in the Main area, click Locks and examine the locking contentions. Move the cursor over the locked application in the tree to display the text of the locked application.