Application00 (KUDDB2APPLGROUP00) attributes (Superseded)

You can use this information to monitor the performance of your applications and identify problem areas for corrective action. All values are integers that are calculated from the first application connection, unless otherwise noted.

Acc Curs Blk The number of times that a request for an I/O block was accepted. Use this attribute with the Rejected Block Cursor Requests attribute to calculate the percentage of blocking requests that are accepted or rejected.

Agent ID The application handle, which is a system-wide unique ID for the application. On multi-node systems, where a database is partitioned, this ID is the same on every node where the application might make a secondary connection. Use the application handle to identify an active application (application handle is synonymous with agent ID) uniquely.

Agent ID Holding Lock The application handle of the agent holding a lock for which this application is waiting. The lock monitor group must be turned on to obtain this information. This attribute can help you to determine which applications are in contention for resources.

Agent Sys CPU Time The total system CPU time (in seconds) that the database manager agent process spent executing database manager code. This element includes CPU time for both SQL and non-SQL statements, and CPU time for any unfenced user-defined functions (UDFs).

Agent User CPU Time The total CPU time (in microseconds) that the database manager agent process used. This counter includes time spent on both SQL and non-SQL statements, and any fenced user-defined functions (UDFs) or stored procedures issued by the application. System CPU represents the time spent in system calls. User CPU represents time spent executing database manager code. The value format is a text string with a maximum of 10 characters. Use this attribute with the other CPU-time related attributes to help you identify applications or queries that consume large amounts of CPU time.

Appl ID The identifier generated when the application connects to the database at the database manager or when DDCS receives a request to connect to a DRDA database. The identifier is unique across the network. The application ID is displayed in various formats that depend on the communication protocol between the client and the server system on which the database manager or DDCS is running. Each of the formats consists of three parts that are separated by periods. Use this ID (known on both the client and server) to correlate the client and server parts of the application.

Appl ID Holding Lock The application ID of the application that is holding a lock on the object that this application is waiting to obtain. The value format is a text string with a maximum of 32 characters. This attribute can help you determine which applications are in contention for resources. You can use it to identify the application handle (agent ID) and table ID that are holding the lock.

Appl Idle Time The number of seconds since an application issued a request to the server. This amount includes applications that have not ended a transaction (for example, have not issued a commit or rollback). Use this information to force users who have been idle for a specified number of seconds.

Appl Name The name of the application running at the client as it is known to the database manager or DB2 Connect. The value format is a text string, with a maximum of 20 characters. For example: *Local.db2inst1.990212202018. Use this attribute with the Application ID attribute to relate data items with your application. In a client/server environment, this name is passed from the client to the server to establish the database connection. For DRDA-AS connections, this name is the DRDA external name.

Auth ID The authorization ID of the user who invoked the monitored application. On a DB2 Connect gateway node, this ID is the user authorization ID on the host. The value format is a text string with a maximum of 20 characters. Use this attribute to determine who invoked the application.

Avg Lock Wait Time The average elapsed time (in milliseconds) that was spent waiting for a lock. If the average lock wait time is high, you must look for applications that hold many locks, or have lock escalations, to focus on tuning your applications to improve concurrency. If escalations cause a high average lock wait time, the values of one or both of the LOCKLIST and MAXLOCKS configuration parameters might be too low.

Avg Pool Read Time The average elapsed time for a read request. This value is derived by dividing the value of the Pool Read Time attribute by the value of the Pool Total Reads attribute. This average can indicate the presence of an I/O wait, which might indicate that you must move data to a different device.

Avg Pool Write Time The average elapsed time for a write request. This value is derived by dividing the value of the Pool Write Time attribute by the value of the Pool Total Writes attribute.

Avg Sort Time The average derived by dividing value of the Total Sort Time attribute by the value of the Total Sorts attribute. The average is expressed as elapsed time. at the database or application level, this attribute can indicate whether sorting is a performance issue. System load affects elapsed time. As you increase the running processes, this elapsed time value becomes higher.

Binds Precompiles The number of attempted binds and precompiles. Use this attribute to gain insight into the current level of activity within the database manager.

Cat Cache Heap Full The number of times that a heap full condition in the database heap caused an insert into the catalog cache to fail. The catalog cache draws its storage dynamically from the database heap. Even if the cache storage has not reached its limit, inserts into the catalog cache can fail if space is lacking in the database heap. If the catalog cache heap full count is not zero, you can correct the insert failure condition by increasing the database heap size or by reducing the catalog cache size.

Cat Cache Hit Ratio The percentage of catalog sections found in the cache. This attribute indicates how well the catalog cache is avoiding catalog accesses. If the value is high (more than 0.8), the cache is performing well. A smaller value might indicate that you must increase the size of the catalog cache. You must expect a large value immediately following the first connection to the database.

Cat Cache Inserts The number of times that the system tried to insert table descriptor information into the catalog cache. Table descriptor information is inserted into the cache following a failed lookup to the catalog cache while processing a table, view, or alias reference in an SQL statement. The catalog cache inserts value includes attempts to insert table descriptor information that fail if the catalog cache overflows and the heap is full.

Cat Cache Overflows The number of times that an insert into the catalog cache failed because the catalog cache was full. If the catalog cache overflows value is large, the catalog cache might be too small for the workload. Increasing the size of the catalog cache might improve its performance. If the workload includes transactions that compile a large number of SQL statements referencing many tables, views, and aliases in a single unit of work, compiling fewer SQL statements in a single transaction might improve the performance of the catalog cache. Or if the workload includes the binding of packages containing many SQL statements referencing many tables, views or aliases, you might want to split the packages so that they include fewer SQL statements to improve performance.

Client PID The process ID of the client application that made the connection to the database. Use this attribute to correlate monitor information such as CPU and I/O time to your client application. If a DRDA AS connection is used, this element is set to 0.

Corr Token The DRDA AS correlation token. The value format is a text string with a maximum of 32 characters. Use the DRDA correlation token to correlate the processing between the application server and the application requester. It is an identifier dumped into logs when errors arise. As a result, you can use it to identify the conversation that is in error. In some cases, it is the LUWID of the conversation. If communications are not using DRDA, this element returns the appl_id attribute.

Country Code The country code of the client application.

Creator The authorization ID of the user that pre-compiled the application. The value format is a text string with a maximum of 20 characters. Use this attribute to help identify the SQL statement that is processing, with the CREATOR column of the package section information in the catalogs.

Cursor Name The name of the cursor corresponding to this SQL statement. The value format is a text string with a maximum of 20 characters. Use this attribute to identify the SQL statement that is processing. This name is used on an OPEN, FETCH, CLOSE, and PREPARE of an SQL SELECT statement. If a cursor is not used, this field is blank.

DB Name The real name of the database for which information is collected or to which the application is connected. This name was given to the database when it was created. The value format is a simple text string with a maximum of 20 characters. Use this attribute to identify the specific database to which the data applies.

DDL SQL Stmts The number of SQL Data Definition Language (DDL) statements that ran. Use this attribute to determine the level of database activity at the application or database level. DDL statements are expensive to run because of their impact on the system catalog tables. As a result, if the value of this attribute is high, you must determine the cause and possibly restrict the identified activity from being performed.

Deadlocks The total number of deadlocks that have occurred. This attribute can indicate that applications are experiencing contention problems. To resolve the problem, determine in which applications (or application processes) the deadlock are occurring. You can then modify the application to enable it to run concurrently. Some applications, however, might not be capable of running concurrently.

Degree Parallelism The degree of parallelism requested when the query was bound. Use with the Agents Top attribute to determine if the query achieved maximum level of parallelism.

Execution ID The ID that the user specified when logging in to the operating system. This ID is distinct from the Authorization ID, which the user specifies when connecting to the database. The value format is a text string with a maximum of 20 characters. Use this attribute to determine the operating system user ID of the individual running the monitored application.

Failed SQL Stmts Pct The percentage of SQL statements that failed to run. This value is derived by dividing the value of the Failed SQL Statements attribute by the value of the Total SQL Statements attribute.

Hash Join Overflows The number of times that hash join data exceeded the available sort heap space. At the database level, if the percentage of Hash Join Small Overflows is greater than 10% of this value, you must consider increasing the sort heap size. You can use values at the application level to evaluate hash join performance for individual applications.

Hash Join Small Overflows The number of times that hash join data exceeded the available sort heap space by less than 10%. If this value and the value of the Hash Join Overflows attribute are high, you must consider increasing the sort heap threshold. If this value is greater than 10% of Hash Join Overflows, you must consider increasing the sort heap size.

Int Auto Rebinds The number of automatic rebinds (or recompiles) that have been attempted. Automatic rebinds are the internal binds the system performs when the validity of a package is removed. Use this attribute to determine the level of database activity at the application or database level. Because internal automatic rebinds can have a significant impact on performance, they must be minimized where possible.

Int Deadlock Rollbacks The total number of forced rollbacks initiated by the database manager due to a deadlock. The database manager initiates a rollback for the current unit of work in an application that is experiencing a deadlock. This attribute shows the number of deadlocks that were broken. It can indicate the possibility of concurrency problems. It is also important because internal rollbacks due to deadlocks can cause performance degradation.

Int Rows Deleted The number of rows deleted from the database as a result of internal activity. This attribute can help to gain insight into internal activity within the database manager. If this activity is high, you must evaluate the table design to determine if the referential constraints or triggers that you defined on the database are necessary.

Int Rows Inserted The number of rows inserted into the database as a result of internal activity caused by triggers. This attribute can help to gain insight into the internal activity within the database manager. If this activity is high, you must evaluate the design to determine if you can alter it to reduce this activity.

Int Rows Updated The number of rows updated from the database as a result of internal activity. This attribute can help to gain insight into internal activity within the database manager. If this activity is high, you must evaluate the table design to determine if the referential constraints that you defined on the database are necessary.

Lock Escals The number of times that locks have been escalated from several row locks to a table lock. A lock is escalated when the total number of locks held by an application reaches the maximum amount of lock list space available to the application, or the lock list space consumed by all applications is approaching the total lock list space. This data item includes a count of all lock escalations, including exclusive lock escalations. When an application reaches the maximum number of locks allowed and there are no more locks to escalate, the application uses space in the lock list that is allocated for other applications. When the entire lock list is full, an error occurs. The value format is an integer.

Lock Timeouts The number of times that a request to lock an object time out instead of being granted. This attribute can help you adjust the setting for the LOCKTIMEOUT database configuration parameter. If the number of lock timeouts becomes excessive when compared to normal operating levels, an application might be holding locks for long durations. In this case, this attribute might indicate that you must analyze some of the other attributes related to locks and deadlocks to determine if an application problem exists. You can have too few lock timeouts if the LOCKTIMEOUT database configuration parameter is set too high. In this case, applications might wait excessively to obtain a lock.

Lock Wait Time The total elapsed time (in milliseconds) that was spent waiting for a lock. At the database level, this is the total amount of elapsed time that all applications were waiting for a lock within this database. At the application-connection and transaction levels, this is the total amount of elapsed time that this connection or transaction has waited for a lock to be granted. This attribute might be used with the Lock Waits attribute to calculate the average wait time for a lock. This calculation can be performed at either the database or the application-connection level.

Lock Waits The total number of times that applications or connections waited for locks. At the database level, the lock waits value is the total number of times that applications waited for locks within this database. At the application-connection level, the lock waits value is the total number of times that this connection requested a lock but waited because another connection was already holding a lock on the data. Use this attribute with the Lock Wait Time attribute to calculate, at the database level, the average wait time for a lock. This calculation can be performed at either the database or the application-connection level. If the average lock wait time is high, look for applications that hold many locks, or have lock escalations, with a focus on tuning your applications to improve concurrency, if appropriate. If escalations are the reason for a high average lock wait time, the values of one or both of the LOCKLIST and MAXLOCKS configuration parameters might be too low.

Locks Held The number of locks currently held. If the monitor information is at the database level, this number represents the total number of locks currently held by all applications in the database. If the information is at the application level, this number represents the total number of locks currently held by all agents for the application.

Node Name For new installations of version 6, release 2, the format is instanceid:hostname:UD for all operating systems. The format for version 6, release 1 of the DB2® agent on Windows systems is instanceid:hostname:UD; on UNIX and Linux system, the format is instanceid:hostname.

Open Local Curs The number of local cursors currently open for this application, including those cursors counted by Open Local Cursors with Blocking attribute. Use this attribute with the Open Local Cursors with Blocking attribute to calculate the percentage of local cursors that are blocking cursors. If the percentage is low, you might be able to improve performance by improving the row blocking in the application. For cursors used by remote applications, see the Open Remote Cursors attribute.

Open Local Curs Blk The number of local blocking cursors currently open for this application. Use this attribute with the Open Local Cursors attribute to calculate the percentage of local cursors that are blocking cursors. Rejected Block Cursor Requests and Accepted Block Cursor Requests attributes provide additional information that might help you tune your configuration parameters to improve row blocking in your application. For blocking cursors used by remote applications, see the Open Remote Cursors with Blocking attribute.

Open Rem Curs The number of remote cursors currently open for this application, including the cursors counted by the Open Remote Cursors with Blocking attribute. Use this attribute with the Open Remote Cursors with Blocking attribute to calculate the percentage of remote cursors that are blocking cursors. If the percentage is low, you might be able to improve performance by improving the row blocking in the application. For the number of open cursors used by applications connected to a local database, see the Open Local Cursors attribute.

Package Name The name of the package that contains the SQL statement currently executing. The value format is a text string with a maximum of 20 characters. Use this attribute to help you identify the application program and the SQL statement that is executing.

Pkg Cache Hit Ratio The percentage of package sections that were found in the cache. This attribute tells you whether the package cache is being used effectively. If the hit ratio is high (more than 0.8), the cache is performing well. A smaller ratio might indicate that the package cache must be increased.

Pool Data from Estore Number of buffer pool data pages copied from extended storage. Required pages are copied from extended storage to the buffer pool. The copy process might incur the cost of connecting to the shared memory segment, but it saves the cost of a disk read.

Pool Data P Reads The number of read requests that required I/O to get data pages into the buffer pool.

Pool Data to Estore Number of buffer pool data pages copied to extended storage. Pages are copied from the buffer pool to extended storage when they are selected as victim pages. As a result of the copying process, there is sufficient space for new pages in the buffer pool.

Pool Hit Ratio The buffer pool hit ratio (as a percentage). The sum of the Pool Data Logical Reads and Pool Index Logical Reads attributes is divided by the value of the Pool Total Reads attribute to derive the ratio. Use this attribute to determine whether buffer pool assignment is efficient. If the pool hit ratio is low, increasing the number of buffer pool pages might improve performance.

Pool Index from Estore Number of buffer pool index pages copied from extended storage. Required index pages are copied from extended storage to the buffer pool. The copy process might incur the cost of connecting to the shared memory segment, but it saves the cost of a disk read.

Pool Index P Reads The number of physical read requests to get index pages into the buffer pool.

Pool Index to Estore Number of buffer pool index pages copied to extended storage. Pages are copied from the buffer pool to extended storage when they are selected as victim pages. As a result of the copying process, there is sufficient space for new pages in the buffer pool.

Pool Index Writes The number of times a buffer pool index page was physically written to disk. If a buffer pool index page is written to disk for a high percentage of the Pool Index Physical Reads, performance might improve by increasing the number of buffer pool pages available for the database. If all applications are updating the database, increasing the size of the buffer pool might have minimal impact on performance; most pages contain updated data that must be written to disk.

Pool Read Time The total amount of elapsed time spent processing read requests that caused data or index pages to be physically read from disk to buffer pool. Use this attribute with the Buffer Pool Data Physical Reads and Buffer Pool Index Physical Reads attributes to calculate the average page-read time. This average is important because it might indicate the presence of an I/O wait, which can indicate that you must move data to a different device.

Pool Write Time The total amount of time spent physically writing data or index pages from the buffer pool to disk. Use this attribute with the Buffer Pool Data Writes and Buffer Pool Index Writes attributes to calculate the average page-write time. This average is important because it might indicate the presence of an I/O wait, which in turn might indicate that you must move data to a different device.

Prefetch Wait Time The time an application spent waiting for an I/O server or prefetcher to finish loading pages into the buffer pool. This attribute can be used to experiment with changing the number of I/O servers and the I/O server sizes.

Query Card Estimate An estimate of the number of rows that are returned by a query. You can compare this estimate by the SQL compiler with the actual runtime values.

Query Cost Estimate Estimated cost, in timerons, for a query, as determined by the SQL compiler. This attribute allows correlation of actual runtime values with the compile-time estimates.

Rej Curs Blk The number of times that a request for an I/O block at the server was rejected and the request was converted to non-blocked I/O. If there are many cursors blocking data, the communication heap might become full. When this heap is full, I/O blocks are not allocated for blocking cursors; however, an error condition does not alert you to this condition. If cursors are unable to block data, performance can be affected adversely.

Rollback SQL Stmts The total number of SQL ROLLBACK statements that have been attempted. A rollback can result from an application request, a deadlock, or an error situation. This attribute counts only the number of rollback statements issued from applications. At the application level, this attribute can help you determine the level of database activity for the application and the amount of conflict with other applications. At the database level, it can help you determine the amount of activity in the database and the amount of conflict between applications on the database.

Rows Deleted The number of row deletions attempted. Use this attribute to gain insight into the current level of activity within the database manager.

Rows Inserted The number of row insertions attempted. Use this attribute to gain insight into the current level of activity within the database manager.

Rows Read The number of rows read from the table. This attribute helps to identify tables with heavy usage for which you might want to create additional indexes.

Rows Selected The number of rows that have been selected and returned to the application. Use this attribute to gain insight into the current level of activity within the database manager.

Rows Updated The number of row updates attempted. Use this attribute to gain insight into the current level of activity within the database manager.

Rows Written The number of rows changed (inserted, deleted, or updated) in the table. A high value for table-level information indicates heavy usage of the table. If so, you might want to use the Run Statistics (RUNSTATS) utility to maintain efficiency of the packages used for this table.

Section Number The internal section number in the package for the SQL statement currently processing or most recently processed.

Sort Overflows The total number of sorts that ran out of sort heap space and might have required disk space for temporary storage. at the database or application level, use this element with the Total Sorts attribute, this attribute can help to determine the source of contention for resources.

Sort Overflows Pct The percentage of sorts that ran out of sort heap space and might have required disk space for temporary storage. This percentage is calculated by dividing the value of the Sort Overflows attribute by the value of the Total Sorts attribute. at the database or application level, use this attribute to evaluate the percentage of sorts that required overflow to disk. If this percentage is high, you might want to adjust the database configuration by increasing the value of the SORTHEAP configuration parameter.

Stmt Stop The string date and time that the most recent SQL statement operation stopped. If the statement is still running, this field is 0 (zero). Use this attribute with the Statement Start attribute to calculate the elapsed execution time for the statement operation.

Stmt Text The text of the dynamic SQL statement. For application snapshots, the statement text helps you identify what the application was executing when the snapshot was taken, or most recently processed if no statement was being processed at the time the snapshot was taken. For dynamic SQL statements, this attribute identifies the SQL text associated with a package.

The statement text can only be fetched during the time when the dynamic SQL query is executing. If the query executing time is short, the statement text might not be retrieved.

Table Name The name of the table the application is waiting to lock. The value format is a text string with a maximum of 20 characters. Use this attribute with the Table Schema attribute to determine the source of contention for resources.

Table Schema The schema of the table the application is waiting to lock. The value format is a text string with a maximum of 20 characters. Along with the Table Name attribute, this attribute can help to determine the source of contention for resources.

Tablespace Name the name of the tablespace that the application is waiting to lock. The value format is a text string with a maximum of 20 characters. This attribute can help you to determine the source of contention for resources.

Total Hash Joins The total number of hash joins that ran. At the database or application level, use this value with the Hash Join Overflows attribute and the Hash Join Small Overflows attribute to determine if a significant percentage of hash joins would benefit from modest increases in the sort heap size.

Total Hash Loops The total number of times that a single partition of a hash join was larger than the available sort heap space. Values for this attribute indicate inefficient execution of hash joins. This might indicate that the sort heap size is too small or the sort heap threshold is too small. Use this value with the other hash join variables to tune the sort heap size (sortheap) and sort heap threshold (sheapthres) configuration parameters.

Total Sort Time The total elapsed time (in milliseconds) for all sorts that ran. at the database or application level, use this element with the Total Sorts attribute to calculate the average sort time. This average can indicate whether sorting is a performance concern.

Total Sorts The total number of sorts that ran. at the database or application level, use this value with the Sort Overflows attribute to calculate the percentage of sorts that need more heap space. You can also use it with the Total Sort Time attribute to calculate the average sort time. If the number of sort overflows is small with respect to the total sorts, increasing the sort heap size might have little impact on performance, unless this buffer size is increased substantially.

Total SQL Stmt The total number of dynamic and static SQL statements. This value is derived by adding the values of the Dynamic SQL Statements and the Static SQL Statements attributes.

UOW Lock Wait Time The total amount of elapsed time this unit of work has spent waiting for locks. This attribute can help you to determine the severity of the resource contention problem.

X Lock Escals The number of times that locks have been escalated from several row locks to one exclusive table lock, or the number of times an exclusive lock on a row caused the table lock to become an exclusive lock. A lock is escalated when the total number of locks held by an application reaches the maximum amount of lock list space available to the application. The amount of lock list space available is determined by the LOCKLIST and MAXLOCKS configuration parameters. Other applications cannot access data held by an exclusive lock. Because exclusive locks can affect the concurrency of your data, it is important to track them. When an application reaches the maximum number of locks allowed and there are no more locks to escalate, it uses space in the lock list allocated for other applications. When the entire lock list is full, an error occurs. Use this attribute with the Previous UOW Stop Time attribute to calculate the total elapsed time between COMMIT/ROLLBACK points, and with the UOW Start Time attribute to calculate the elapsed time of the latest unit of work.