Users View (DB2 LUW Performance Analyst)

From RapidSQL
Jump to: navigation, search

Go Up to IBM DB2 (W/U/L) Performance Analyst statistics

The database activity can be examined using key performance metrics and wait/bottleneck-based analysis. In addition to a Home page, the Users category of DB2 Performance Analyst includes the following tabbed pages:

Home

The Home page of the Users performance category view displays the vital DB2 user statistics in the following categories:

Key User Analysis Pane

Database performance analysts typically use one of two methods for examining the performance levels of a database - ratio-based or wait/bottleneck-based. Ratio-based analysis involves examining a number of key database ratios that can be used to indicate how well a database is running. Performance ratios serve as very good roll-up mechanisms for busy DBAs to use for at-a-glance performance analysis. Many DBAs have large database farms to contend with and cannot spend time checking detailed wait-based analysis outputs for each and every database they oversee. Succinctly presented performance ratios can assist in such situations by giving DBAs a few solid indicators that can be quickly scanned to see if any database needs immediate attention.

While there are certainly many opinions as to what rules to follow, there are some standards that should always be adhered to. To start with, many of the formulas that make up ratio-based analysis must be derived from delta measurements instead of cumulative statistics. Many of the global ratios that a DBA will examine come from the DB2 Snapshot Monitor. The snapshot monitor returns metrics for the Instance and particular databases since the start of monitoring. Some metrics are cumulative (e.g., counters) and others are instantaneous (e.g., gauges).

A final thing to remember about using ratio-based analysis is that, while there are a number of rules of thumb that can be used as starting points to begin the evaluation of database performance, DBAs must determine each SQL Server's individual 'personality' with respect to the key performance ratios. Some hard and fast rules simply do not apply to every database. The danger in using blanket ratio standards is that they can lead the DBA to haphazardly take action, which can at times contribute nothing to the situation, and sometimes even degrade performance.

The following ratios are used on the Performance Analyst User page to succinctly communicate the general overall performance levels of the monitored database:

Transactions/sec

Transactions/sec is the number of transactions (units of work) completed per second on the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Key User Analysis Pane

Metrics

A small rate of transactional activity on the database can indicate that applications are not doing frequent commits, which may lead to logging and concurrency problems.

Correction

Drill down to the Users Detail>SQL Activity page to check which applications are running their transactions for long periods of time

Static SQL/sec

The static SQL/sec metric is the number of static SQL statement executions attempt on the database each second.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Key User Analysis Pane

Dynamic SQL/sec

This metric is the number of dynamic SQL statement executions being attempted on the database each second.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Key User Analysis Pane

Applications Idle

The idle applications metric indicates the number of applications that are currently connected to the database for which the database manager is not executing any requests

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Key User Analysis Pane

Metrics

This can help you understand the level of activity within a database and the amount of system resource being used.

Applications Executing

The executing applications statistic indicates the number of applications for which the database manager is currently executing requests.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Key User Analysis Pane

Metrics

You can use this element to understand how many of the database manager agent tokens are being used by applications connected to this database.

Connections High Watermark

The connections high watermark is the highest number of simultaneous connections to the database since the database was activated.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Key User Analysis Pane

Metrics

You can use this element to evaluate the setting of the maxappls configuration parameter. If the value of this element is the same as the maxappls parameter, it is likely that some database connection requests were rejected, since maxappls limits the number of database connections allowed.

Correction

Increase the value of the maxappls parameter to allow more connections.

Bottleneck Analysis Pane

When a DB2 database is active, the applications accessing the database can either successfully access the database resources, or they can wait for resources to become available. An application that is waiting can indicate normal database activity, or it can be an indicator that a database bottleneck exists. This is where wait-based or bottleneck analysis comes into play. A database administrator can use this form of performance analysis to determine if perceived bottlenecks in a database are contributing to a performance problem.

Performance Analyst Bottleneck analysis section helps a DBA isolate potential problem areas where there are waits, resource overloads or potential failures. If concurrency issues or heavy table scan activity has been dragging a database's performance down, a DBA can use bottleneck analysis to isolate the root cause. Once one or more bottlenecks have been isolated as possible areas of interest, the DBA can drill down and examine details on why the applications or resources are causing the problem. The Performance Analyst Users page identifies the top system and application bottlenecks that may be currently a cause of lessened performance.

The following bottleneck indicators are used on the Performance Analyst Home page to succinctly communicate the general overall performance level of the monitored database:

The following statistics, appearing on this page, duplicate statistics available on the Bottleneck Analysis Pane of the Home View (DB2 LUW Performance Analyst) page:

The following statistic, available on this pane, duplicates a statistic on the SQL Analysis Pane of the Home View (DB2 LUW Performance Analyst) page:

Top Bottlenecked User Processes

The top bottlenecked user processes grid shows the top bottlenecked applications in the system. These applications are either performing the top I/O operations or have idled the longest to obtain locks.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Automatic Rebinds

The automatic rebinds statistic is the number of automatic rebinds (or recompiles) that have been attempted.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

Automatic rebinds are the internal binds the system performs when an package has been invalidated. The rebind is performed the first time the database manager needs to execute an SQL statement from the package. For example, packages are invalidated when you: Drop an object, such as a table, view, or index, on which the plan depends Add or drop a foreign key Revoke object privileges on which the plan depends. Since automatic rebinds can have a significant impact on performance, they should be minimized where possible.

Internal Rollbacks

The internal rollbacks statistic is the total number of rollbacks initiated internally by the database manager.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

An internal rollback occurs when any of the following cannot complete successfully: A reorganization An import A bind or pre-compile An application ends as a result of a deadlock situation or lock timeout situation An application ends without executing an explicit commit or rollback statement (on Windows).

Waits

The lock waits metric is the total number of times that applications or connections waited for locks within the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Deadlocks

The deadlocks statistic is the total number of deadlocks that have occurred since this instance of Performance Analyst started monitoring the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

If a large number of deadlocks are detected, it can indicate that applications are experiencing lock contention problems. Deadlocks are usually caused by one of the following situations: Lock escalations on the database Catalog tables locked for Repeatable Read Applications are using inappropriate isolation levels at bind time Applications are obtaining the same locks in a different order Applications are locking tables explicitly where row level locks are sufficient.

Correction

You may be able to modify the applications causing lock contentions for better concurrency. To identify the applications that may be causing contentions, go to the Lock Waits tab of the Users detail page and review the Application Details section.

Timeouts

The lock timeouts metric identifies the number of times that a request to lock an object timed out without being granted.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Bottleneck Analysis Pane

Metrics

If the number of lock timeouts becomes excessive when compared to the acceptable range for your database, it can indicate that an application is holding locks for long durations. It can also indicate that the amount of time an application waits for a lock before timing out is too short. If you have too few lock timeouts and the average lock wait time is too high, it can indicate that the lock timeout configuration parameter is set to an excessively high value

Correction

First you should examine the lock activity at the application level (Users>Lock, Lock Waits drilldown) to identify any particular application that is causing excessive lock contentions. If so, you can tune the application to provide better concurrency. If lock timeouts are excessive, and average lock wait times are very short, you can increase the locktimeout database configuration parameter to make the applications wait longer before timing out.

SQL Analysis Pane

Most of database's I/O performance can be attributed to SQL statement execution. Poorly optimized SQL statements can drag down an otherwise well-configured server in terms of user response times.

This section shows a snapshot of row level SQL operations that have been performed on the database since you started monitoring. This gives you an insight into the row level of read/write activity that is currently occurring within the database and comparing this to the prefetcher, and cleaner activity allows you to relate the two.

The following statistics are used on the Performance Analyst for DB2 User home page to succinctly communicate the general overall performance levels of I/O. They duplicate statistics appearing on the SQL Analysis Pane of the I/O Home page.:

Connection Analysis Pane

The Connection Analysis section displays the level of application connect/disconnect activity occurring on the database.

The following statistics are used on the Performance Analyst for DB2 User home page to succinctly communicate general overall connectivity:

Current Connections

The current connections metric is the number of applications currently connected to the database.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Connection Analysis Pane

Metrics

You can use this metric to help you get an overview of the level of database activity and the amount of system resources in use.

Correction

This metric can help you adjust the setting of the maxappls and max_coordagents configuration parameters. For example, if this value is always the same as maxappls, you consider increasing the value of maxappls.

Disconnects

This metric shows the number of disconnects from the database since the first connect, activate, or last reset.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Connection Analysis Pane

Connections Since Database Activation

The connections since database activation statistic indicates the number of connections to the database since the first connect, activate, or last reset.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Connection Analysis Pane

Metrics

Use this metric in conjunction with the database connection time and the database start time to see if the application connection frequency is low. When there are no connections to db2, by default the database is automatically deactivated until the next connection. The connection that reactivates the database encounters slow connection time because it needs to wait for the database to reinitialize. If very few applications are connecting to the database, some connections can be slow because of this

Correction

If there are short periods when your database has no applications connected to it, activate the database explicitly using the ACTIVATE DATABASE command. This significantly improves the connection performance for the database.

High Watermark for Concurrent Connections

The connections high watermark is the highest number of simultaneous connections to the database since the database was activated.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Home > Connection Analysis Pane

Metrics

You can use this element to evaluate the setting of the maxappls configuration parameter. If the value of this element is the same as the maxappls parameter, it is likely that some database connection requests were rejected, since maxappls limits the number of database connections allowed.

Correction

Increase the value of the maxappls parameter to allow more connections.

Workload Analysis Pane

When your phone starts ringing with complaints of performance slowdowns, one of the first things you should get a handle on is:

  • Who is currently connected to the database?
  • What resources are they using?
  • What are they currently executing?

The Workload Analysis section of the Performance Analyst Users page provides insight into the leading resource hogs of a server. Drill-downs are available so you can easily get detailed information into what each leading application is currently involved with. The Workload Analysis section presents the following metrics:

Top Lock Escalations

The top lock escalation displays the application with largest number of lock escalations.

Top CPU Process

The top CPU process displays the application with the highest CPU usage.

Top I/O Process

The top I/O process displays the application with the highest I/O read/write time.

Oldest Transaction

The oldest transaction displays the application with the longest running current transaction.

Top Sessions tab

Very often, one renegade client application brings a healthy database down by hogging most of the available resources. This page shows the top resource hogs in each of the resource usage areas to help you quickly identify the worst offenders in each area. You see:

Top I/O Process

This section shows the application with the top IO time and compares it with the IO time of all other applications.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Top Sessions tab

Metrics

The metrics are calculated by measuring the total time spent by each application doing reads and writes (buffered and non-buffered).

Top Memory Process

This section shows the application with the largest memory pool usage aggregate. It also shows the total memory pool usage for all other applications.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Top Sessions tab

Metrics

This metric is calculated by aggregating all of the memory pools for each application and showing the top application. This section is useful in determining if the top application is using a lot more resources

Top CPU Process

This section shows the application with the top CPU time usage and compares it with the CPU usage of all other applications.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Top Sessions tab

Metrics

Metrics are calculated using the total user and system CPU time for each application connected to the database.

Top Sessions

Shows a list of all applications connected to the database and the resource usage for these applications. The information presented for each application includes:

  • Auth ID: The authorization ID of the user who invoked the application that is being monitored
  • Name: The name of the application running at the client as known to the database manager
  • Handle: A system-wide unique ID for the application. On a single-partitioned database, it consists of a 16 bit counter. On a multi-partitioned database, it consists of the coordinating partition number concatenated with a 16 bit counter. In addition, this identifier will be the same on every partition where the application may make a secondary connection.
  • ID: This identifier is generated when the application connects to the database at the database manager
  • Client PID: The process ID of the client application that made the connection to the database
  • I/O Time: The total time spent by application in performing buffered and non-buffered reads and writes
  • Memory Usage: Total memory pool usage for the application
  • CPU Time: The total user + system cpu time used by the application agents
    1. Agents: Number of agents for the applicatio
  • Status: Current Status of the application. Values for this field are:
    • Database Connect Pending: The application has initiated a database connection but the request has not yet completed.
    • Database Connect Completed: The application has initiated a database connection and the request has completed.
    • Unit of Work Executing: The database manager is executing requests on behalf of the unit of work.
    • Unit of Work Waiting: The database manager is waiting on behalf of the unit of work in the application. This status typically means that the system is executing in the application's code.
    • Lock Wait: The unit of work is waiting for a lock. After the lock is granted, the status is restored to its previous value.
    • Commit Active: The unit of work is committing its database changes.
    • Rollback Active: The unit of work is rolling back its database changes.
    • Recompiling: The database manager is recompiling (that is, rebinding) a plan on behalf of the application.
    • Compiling: The database manager is compiling an SQL statement or precompiling a plan on behalf of the application.
    • Request Interrupted: An interrupt of a request is in progress.
    • Database Disconnect Pending: The application has initiated a database disconnect but the command has not yet completed executing. The application may not have explicitly executed the database disconnect command. The database manager will disconnect from a database if the application ends without disconnecting.
    • Decoupled from Agent: The application has been decoupled from an agent.
    • Transaction Prepared: The unit of work is part of a global transaction that has entered the prepared phase of the two-phase commit protocol.
    • Transaction Heuristically Committed: The unit of work is part of a global transaction that has been heuristically committed.
    • Transaction Heuristically Rolled Back: The unit of work is part of a global transaction that has been heuristically rolled-back.
    • Transaction Ended: The unit of work is part of a global transaction that has ended but has not yet entered the prepared phase of the two-phase commit protocol.
    • Creating Database: The agent has initiated a request to create a database and that request has not yet completed.
    • Restarting Database: The application is restarting a database in order to perform crash recovery.
    • Restoring Database: The application is restoring a backup image to the database.
    • Backing Up Database: The application is performing a backup of the database.
    • Data Fast Load: The application is performing a "fast load" of data into the database.
    • Data Fast Unload: The application is performing a "fast unload" of data from the database.
    • Wait to Disable Tablespace: The application has detected an I/O error and is attempting to disable a particular table space. The application has to wait for all other active transactions on the table space to complete before it can disable the table space.
    • Quiescing a Tablespace: The application is performing a quiesce table space request.
    • Wait for Remote Partition: The application is waiting for a response from a remote partition in a partitioned database instance.
    • Remote Request Pending: The application is waiting for results from a federated datasource.
  • Last UOW Start Time: The date and time that the unit of work first required database resource
  • Last UOW End Time: The date and time that the most recent unit of work completed, which occurs when database changes are committed or rolled back
  • Current UOW Elapsed Time: The elapsed execution time of the most recently completed unit of work.
  • Login Timestamp: The date and time that a connection request was granted.
  • Inbound Comm Address: This is the communication address of the client.
  • Client Protocol: The communication protocol that the client application is using to communicate with the server.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Top Sessions tab

Cursors tab

The Cursors tab of the Users Detail view displays the Application SQL Cursor Activity. It details the SQL cursors open for each application. The information presented here includes:

  • Open Cursors: The number of cursors (local and remote) currently open for an application.
  • Blocking Cursors: The number of blocking cursors (local and remote) currently open for the application
  • Block Requests Accepted: The percentage of all blocking I/O requests that were accepted by the database
  • Block Requests Rejected: The percentage of all blocking I/O requests that were rejected by the database

Metrics

The number of blocking cursors for an application should be close to the number of open cursors. If this is not the case the application performance may be affected. If there are many cursors blocking data, the communication heap may become full. When this happens, instead of returning an error, the database stops allocating I/O blocks for blocking cursors.

Correction

If the number of blocking cursors in an application is not close to the number of open cursors, you may be able to improve application performance by tweaking the precompile options for record blocking for the treatment of ambiguous cursors, and by redefining the cursors with FOR FETCH ONLY clause where possible

If a large number of cursors were unable to perform blocking, you may be able to improve performance by increasing the query_heap database manager configuration parameter.

SQL Activity tab

The SQL Activity tab of the Users Detail view displays the following statistics:

Application List

This section shows a list of applications connected to the database. You can select an application from this list and see details for the SQL activity for this application.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > SQL Activity tab

SQL Executed

This section shows the details for the various types of SQL statements executed by the particular application. It also gives you the total application throughput. The information presented here includes:

  • Selects: Percentage of statements that are select statements
  • Updates/Inserts/Deletes: Percentage of statements that modify/write data on the database
  • DDLs: Percentage of CREATE/DROP/ALTER statements
  • Others: All other types of statements (Fetch, Open cursor etc)

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > SQL Activity tab

Metrics

Various types of statements have varying impact on the performance and concurrency of the database. For example - DDL statements can cause catalog and package cache invalidations, Insert/update/delete statements usually obtain locks and perform log IO. This section can help you determine the contributing statement types to the activity for an application.

SQL Activity

This section gives details on the sql activity for the selected application. It allows you to quick compare the number of static dynamic and failed sql statements with the overall application throughput. The metrics presented here include:

  • Static SQL: The number of static SQL statements that the application tried to execute
  • Dynamic SQL: The number of dynamic SQL statements that the application tried to execute
  • Failed SQL: The number of SQL statements (static and dynamic) that the application tried to execute but failed
  • SQL Throughput: Number of SQL statements (dynamic and static) that the application executed successfully

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > SQL Activity tab

Metrics

These elements may help you determine the nature of the application. Failed SQL statements metric may also help identify the cause of poor performance on the database since failed statements mean time wasted by the database manager.

UOW Details

Shows the units of work being completed by the application over the refresh time interval.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > SQL Activity tab

Metrics

A low rate of unit of work completion may be an indicator of one of the following: The application is not committing transactions frequently and may be causing concurrency issues for other applications The application is idling and not performing any work on the server

Correction

Examine the locks obtained by the application (Users Detail >Locks page) to see if a low number here should be a cause for concern.

Binds and Precompiles Executed

This section shows the total number of implicit and explicit binds and precompiles executed by the application.

Implicit rebinds are performed by the system when the application tries to execute a sql statement from a package and the package has been invalidated. A package is usually invalidated when you perform DDL/DCL operations on objects on which the package plan is dependent. Explicit rebinds occur when the user executes the REBIND PACKAGE command.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > SQL Activity tab

Metrics

Bind and precompile operations have significant overhead for the database operations and they should be minimized during periods of heavy usage when possible.

Attributes tab

The Attributes tab of the Users Detail page displays the following statistics:

Application List

This section shows the list of applications connected to the database. The user can obtain further details for an application from other sections on this page by selecting the application from this list.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Attributes tab

Application Attributes

Shows the main information for the selected application. This information is useful in determining the applications connection attributes and its state.The information shown here includes:

  • Application Status: The status of the application. Values for this field are:
    • Database Connect Pending: The application has initiated a database connection but the request has not yet completed.
    • Database Connect Completed: The application has initiated a database connection and the request has completed.
    • Unit of Work Executing: The database manager is executing requests on behalf of the unit of work.
    • Unit of Work Waiting: The database manager is waiting on behalf of the unit of work in the application. This status typically means that the system is executing in the application's code.
    • Lock Wait: The unit of work is waiting for a lock. After the lock is granted, the status is restored to its previous value.
    • Commit Active: The unit of work is committing its database changes.
    • Rollback Active: The unit of work is rolling back its database changes.
    • Recompiling: The database manager is recompiling (that is, rebinding) a plan on behalf of the application.
    • Compiling: The database manager is compiling an SQL statement or precompiling a plan on behalf of the application.
    • Request Interrupted: An interrupt of a request is in progress.
    • Database Disconnect Pending: The application has initiated a database disconnect but the command has not yet completed executing. The application may not have explicitly executed the database disconnect command. The database manager will disconnect from a database if the application ends without disconnecting.
    • Decoupled from Agent: The application has been decoupled from an agent.
    • Transaction Prepared: The unit of work is part of a global transaction that has entered the prepared phase of the two-phase commit protocol.
    • Transaction Heuristically Committed: The unit of work is part of a global transaction that has been heuristically committed.
    • Transaction Heuristically Rolled Back: The unit of work is part of a global transaction that has been heuristically rolled-back.
    • Transaction Ended: The unit of work is part of a global transaction that has ended but has not yet entered the prepared phase of the two-phase commit protocol.
    • Creating Database: The agent has initiated a request to create a database and that request has not yet completed.
    • Restarting Database: The application is restarting a database in order to perform crash recovery.
    • Restoring Database: The application is restoring a backup image to the database.
    • Backing Up Database: The application is performing a backup of the database.
    • Data Fast Load: The application is performing a "fast load" of data into the database.
    • Data Fast Unload: The application is performing a "fast unload" of data from the database.
    • Wait to Disable Tablespace: The application has detected an I/O error and is attempting to disable a particular table space. The application has to wait for all other active transactions on the table space to complete before it can disable the table space.
    • Quiescing a Tablespace: The application is performing a quiesce table space request.
    • Wait for Remote Partition: The application is waiting for a response from a remote partition in a partitioned database instance.
    • Remote Request Pending: The application is waiting for results from a federated datasource.
  • ID of code page used by the application: The code page identifier.
  • Application status change time: The date and time the application entered its current status.
  • Sequence Number: This is incremented whenever a unit of work ends (that is, when a COMMIT or ROLLBACK terminates a unit of work). Together, the application id and sequence number uniquely identify a transaction.
  • Client Product/Version ID: The product and version of DB2 that is running on the client.
  • Database Alias used by the Application: The alias of the database provided by the application to connect to the database.
  • User login id: The ID that the user specified when logging in to the operating system. This ID is distinct from auth_id, which the user specifies when connecting to the database
  • DRDA correlation token: The DRDA correlation token is used for correlating the processing between the application server and the application requester. It is an identifier dumped into logs when errors arise, that you can use to identify the conversation that is in error. In some cases, it will be the LUWID of the conversation
  • Client operating platform: The operating system on which the client application is running.
  • Database territory code: The territory code of the database for which the monitor data is collected
  • Application agent priority: The priority of the agents working for this application
  • Application priority type: Operating system priority type for the agent working on behalf of the application
  • Coordinating node: In a multinode system, the node number of the node where the application connected or attached to the instance
  • Connection request start timestamp: The date and time that an application started a connection request.
  • Connection request completion timestamp: The date and time that a connection request was granted.
  • Previous unit of work completion timestamp: This is the time the unit of work completed.
  • Unit of work start timestamp: The date and time that the unit of work first required database resources.
  • Unit of work stop timestamp: The date and time that the most recent unit of work completed, which occurs when database changes are committed or rolled back.
  • Most recent unit of work elapsed time: The elapsed execution time of the most recently completed unit of work.
  • Unit of work completion status: The status of the unit of work and how it stopped.
  • Application idle time: Number of seconds since an application has issued any requests to the server. This includes applications that have not terminated a transaction, for example not issued a commit or rollback.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Attributes tab

Direct Authorities

Shows the highest level of direct authority granted to the application

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Attributes tab

Indirect Authorities

Shows the highest level of indirect authority granted to the application

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Attributes tab

Lock Waits tab

The Lock Waits tab of the Users Detail view shows the details for all applications in the database that are waiting on locks. This information is useful in tracking concurrency issues in the database. The statistics available here are:

The following statistic, available on this tab, duplicates a statistic on the Bottleneck Analysis Pane of the Home View (DB2 LUW Performance Analyst) page:

Application Details

This section shows the list of applications waiting for locks to be released on objects they need to access. The information presented here includes:

  • Auth ID: The authorization ID of the user who invoked the application that is being monitored.
  • Name: Name of the application executable
  • Handle: A systemwide unique ID for the application
  • ID: This identifier is generated when the application connects to the database at the database manager
  • Client PID: The process ID of the client application that made the connection to the database
  • Total Lock Waits: The total number of times that applications or connections waited for locks
  • Total Lock Waiting Time: This is the total amount of elapsed time that this connection or transaction has waited for a lock to be granted to it.
  • Locks Waiting: Indicates the number of agents waiting on a lock
  • UOW Lock Waiting Time: The total amount of elapsed time current unit of work has spent waiting for locks
  • Wait Start Time: The date and time that this application started waiting to obtain a lock on the object that is currently locked by another application
  • Lock App Handle: System wide Unique ID of the application holding the lock
  • Lock App ID: ID of the application holding the lock
  • Lock Sequence ID: The sequence number of the application that is holding a lock on the object that this application is waiting to obtain.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Lock Waits tab

Metrics

AuthID, Name, Handle, ID, and Client PID help you identify the application waiting on the lock. Lock App Handle, Lock App ID, and Lock Sequence number help you identify the application that is holding the waited on lock. You can use this information on Users Detail Locks tab to get further details on the application holding the locks and the locks it’s holding.

Correction

If an application is not performing well, and it has a high number of total lock waits, and a high total lock waiting time you may need to improve concurrency of the applications that are holding the locks.

Applications Waiting on Locks

This metric indicates the percentage of all connected applications waiting on locks.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Lock Waits tab

Metrics

If this number is high, the application may have concurrency problems.

Correction

You can identify the applications that are holding locks or exclusive locks for a long time from the Users Detail> Locks page and tune such applications for better concurrency.

Locks tab

The Locks tab of the Users Detail view gives you information for the following statistics:

Application Details

This section shows a list of applications connected to the database. You can look at the lock details for an application in the list by selecting the application. The attributes presented for each application include:

  • Handle: A system-wide unique ID for the application
  • ID: This identifier is generated when the application connects to the database at the database manager
  • Seq No
  • Name: Name of the application executable
  • Auth ID: The authorization ID of the user who invoked the application that is being monitored.
  • Status
  • Code Page
  • Locks Held
  • Total Wait Time
  • UOW Lock Waiting Time

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Locks tab

Locks Held Details

This section gives the details of all the locks that the selected application is currently holding. The details presented here include:

  • Lock Name: Internal binary lock name. This element serves as a unique identifier for locks
  • Lock Attributes: Gives the lock attribute settings
  • Release Flags: The lock release flags can either be untracked or SQL Compiler
  • Lock Count: The number of locks on the lock being held. This value ranges from 1 to 255. It is incremented as new locks are acquired, and decremented as locks are released. When lock_count has a value of 255, this indicates that a transaction duration lock is being held. At this point, lock_count is no longer incremented or decremented when locks are acquired or released. The lock_count element is set to a value of 255 in one of two possible ways
    1. lock_count is incremented 255 times due to new locks being acquired.
    2. A transaction duration lock is explicitly acquired. For example, with a LOCK TABLE statement, or an INSERT.
  • Hold Count: The number of holds placed on the lock. Holds are placed on locks by cursors registered with the WITH HOLD clause and some DB2 utilities. Locks with holds are not released when transactions are committed.
  • Object Name: It is the name of the object for table-level locks is the file ID (FID) for SMS and DMS table spaces. For row-level locks, the object name is the row ID (RID). For table space locks, the object name is blank. For buffer pool locks, the object name is the name of the buffer pool.
  • Object Type: The type of object against which the application holds a lock (for object-lock-level information), or the type of object for which the application is waiting to obtain a lock (for application-level and deadlock-level information).
  • Tablespace: This is the name of the table space against which the lock is held.
  • Table Schema: Schema of the table that the lock is on
  • Table Name: Name of the table that the lock is on. This element is only set if Object Type indicates Table
  • Lock Mode: The type of lock being held.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Locks tab

Locks Waiting Details

This section gives the details of all the locks that the selected application is currently waiting on. The details include:

  • Subsection: Identifies the subsection associated with the waited on lock
  • Agent ID: The application handle of the agent holding a lock for which this application is waiting
  • App ID: The application ID of the application holding a lock on the object that this application is waiting to obtain.
  • Lock Name: Internal binary lock name. This element serves as a unique identifier for locks.
  • Lock Attributes: Gives the lock attribute settings
  • Release Flags: Lock release flags
  • Object Type: The type of object against which the application holds a lock
  • Lock Mode: The type of lock being held
  • Lock Mode Requested: The lock mode being requested by the application
  • Tablespace: This is the name of the table space against which the lock is held.
  • Table Schema: Schema of the table that the lock is on
  • Table Name: Name of the table that the lock is on. This element is only set if Object Type indicates Table
  • Wait Start Timestamp: The date and time that this application started waiting to obtain a lock on the object that is currently locked by another application
  • Escalation: Indicates whether a lock request was made as part of a lock escalation.

Location

IBM DB2 (W/U/L) Performance Analyst statistics > Users View (DB2 LUW Performance Analyst) > Locks tab

Retrieved from "https://docwiki.embarcadero.com/RapidSQL/171/e/index.php?title=Users_View_(DB2_LUW_Performance_Analyst)&oldid=10440"