Application00U (KUDDB2APPLGROUP00_U) attributes (Superseded)

By using this information, you can determine the efficiency of the database and identify any problem areas for corrective action. All values are integers that are calculated from the first application connection, unless otherwise noted. This attribute group is superseded. Use the attributes in the Application00 (KUDAPPL00) attribute group.

Acc Curs Blk The number of times that a request for an I/O block was accepted. The value format is an integer. 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. The value format is an integer. 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.

Agent ID Holding Lock The application handle of the agent holding a lock for which this application is waiting. The value format is an integer. The lock monitor group must be turned on to obtain this information.

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) used by the database manager agent process.

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 value format is a text string, with a maximum of 32 characters.

Appl ID Holding Lock (Unicode) The application ID of the application that is holding a lock on the object that this application is waiting to obtain (Unicode). The value format is a text string with a maximum of 96 bytes.

Appl Idle Time The number of seconds since an application issued a request to the server. The value format is an integer.

Appl Name (Unicode) The name of the application running at the client as it is known to the database manager or DB2 Connect (Unicode). The value format is a text string, with a maximum of 60 bytes. For example: *Local.db2inst1.990212202018.

Auth ID (Unicode) The authorization ID of the user who invoked the application that is being monitored (Unicode). 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 60 bytes. 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. The value format is an integer. If the average lock wait time is high, you must 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.

Avg Pool Read Time The average elapsed time for a read request. The value format is an integer.

Avg Pool Write Time The average elapsed time for a write request. The value format is an integer.

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 and has an integer value.

Binds Precompiles The number of binds and precompiles attempted. The value format is an integer. 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 an insert into the catalog cache failed because of a heap full condition in the database heap. The value format is an integer. 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 might fail due to a lack of space 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. The value format is an integer.

Cat Cache Inserts The number of times that the system tried to insert table descriptor information into the catalog cache. The value format is an integer. 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 due to catalog cache overflow and heap full conditions.

Cat Cache Lookups The number of times that the catalog cache was referenced to obtain table descriptor information. The value format is an integer.

Cat Cache Overflows The number of times that an insert into the catalog cache failed because the catalog cache was full. The value format is an integer. 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. The value format is an integer. 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.

Client Prdid The product and version identifier for the software on the client. The value format is a text string with a maximum of 20 characters. For example: SQL06010.

Commit SQL Stmts The total number of SQL COMMIT statements that have been attempted. The value format is an integer. A small rate of change in this counter during the monitor period might indicate that applications are not doing frequent commits. The lack of frequent commits can lead to problems with logging and data concurrency.

Corr Token (Unicode) The DRDA AS correlation token (Unicode). The value format is a text string with a maximum of 96 bytes.

Country Code The country code of the client application. The value format is an integer.

Creator (Unicode) The authorization ID of the user that precompiled the application (Unicode). The value format is a text string with a maximum of 60 bytes. 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 (Unicode) The name of the cursor corresponding to this SQL statement (Unicode). The value format is a text string with a maximum of 60 bytes.

DB Name (Unicode) The real name of the database for which information is collected or to which the application is connected (Unicode). This name was given to the database when it was created. The value format is a simple text string with a maximum of 60 bytes. 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. The value format is an integer.

Deadlocks The total number of deadlocks that have occurred. The value format is an integer. This attribute can indicate that applications are experiencing contention problems. To resolve the problem, determine in which applications (or application processes) the deadlocks 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. The value format is an integer. Use with the Agents Top attribute to determine if the query achieved maximum level of parallelism.

Direct Read Reqs The number of requests to perform a direct read of one or more sectors of data. The value format is an integer.

Direct Read Time The elapsed time (in milliseconds) required to perform the direct reads. The value format is an integer.

Direct Reads The number of read operations that do not use the buffer pool. The value format is an integer.

Direct Write Reqs The number of requests to perform a direct write of one or more sectors of data. The value format is an integer.

Direct Write Time The elapsed time (in milliseconds) required to perform the direct writes. The value format is an integer.

Direct Writes The number of write operations that do not use the buffer pool. The value format is an integer.

Dynamic SQL Stmts The number of dynamic SQL statements that were attempted. The value format is an integer.

Execution ID (Unicode) The ID that the user specified when logging in to the operating system (Unicode). 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 60 bytes. Use this attribute to determine the operating system user ID of the individual running the monitored application.

Failed SQL Stmts The number of SQL statements that were attempted, but failed. The value format is an integer.

Failed SQL Stmts Pct The percentage of SQL statements that failed to run succesfully. The value format is an integer. 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. The value format is an integer.

Hash Join Small Overflows The number of times that hash join data exceeded the available sort heap space by less than 10%. The value format is an integer. 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. The value format is an integer. Automatic rebinds are the internal binds the system performs when a package has been invalidated. Use this attribute to determine the level of database activity at the application or database level.

Int Commits The total number of commits initiated internally by the database manager. The value format is an integer.

Int Deadlock Rollbacks The total number of forced rollbacks initiated by the database manager due to a deadlock. The value format is an integer. 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 have been broken. It can indicate the possibility of concurrency problems. It is also important because internal rollbacks due to deadlocks can cause performance degradation.

Int Rollbacks The total number of rollbacks initiated internally by the database manager. The value format is an integer.

Int Rows Deleted The number of rows deleted from the database as a result of internal activity. The value format is an integer. 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. The value format is an integer. 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. The value format is an integer. 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. The value format is an integer.

Lock Wait Time The total elapsed time (in milliseconds) that was spent waiting for a lock.

Lock Waits The total number of times that connections waited for locks. The value format is an integer. 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.

Locks Held The number of locks currently held. The value format is an integer.

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® systems, 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. The value format is an integer.

Open Local Curs Blk The number of local blocking cursors currently open for this application. The value format is an integer.

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. The value format is an integer.

Open Rem Curs Blk The number of remote blocking cursors currently open for this application. The value format is an integer. Use this attribute with the Open Remote Cursors attribute to calculate the percentage of remote cursors that are blocking cursors.

Package Name (Unicode) The name of the package that contains the SQL statement currently executing (Unicode). The value format is a text string with a maximum of 60 bytes.

Pkg Cache Hit Ratio The percentage of package sections that were found in the cache. The value format is an integer.

Pkg Cache Inserts The total number of times that a requested section was not available for use and had to be loaded into the package cache. The value format is an integer. This count includes any implicit prepares performed by the system.

Pkg Cache Lookups The number of times that an application looked for a section or package in the package cache. The value format is an integer. At a database level, it indicates the overall number of references since the database was started, or monitor data was reset. Note that this counter includes the cases where the section is already loaded in the cache and when the section has to be loaded into the cache.

Pool Data from Estore The number of buffer pool data pages that are copied from extended storage. The value format is an integer.

Pool Data L Reads The number of logical read requests for data pages that have gone through the buffer pool. The value format is an integer. This count includes accesses to data that is already in the buffer pool when the database manager needs to process the page or read into the buffer pool before the database manager can process the page.

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

Pool Data to Estore Number of buffer pool data pages copied to extended storage. The value format is an integer.

Pool Data Writes The number of times a buffer pool data page was physically written to disk. The value format is an integer.

Pool Hit Ratio The buffer pool hit ratio (as a percentage). The value format is an integer. 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.

Pool Index from Estore Number of buffer pool index pages copied from extended storage. The value format is an integer.

Pool Index L Reads The number of logical read requests for index pages that have gone through the buffer pool. The value format is an integer.

Pool Index P Reads The number of physical read requests to get index pages into the buffer pool. The value format is an integer.

Pool Index to Estore Number of buffer pool index pages copied to extended storage. The value format is an integer. 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. The value format is an integer. 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. The value format is an integer.

Pool Write Time The total amount of time spent physically writing data or index pages from the buffer pool to disk. The value format is an integer. 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 (prefetcher) to finish loading pages into the buffer pool. The value format is an integer. 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. The value format is an integer. 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. The value format is an integer. 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. The value format is an integer. 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. The value format is an integer. 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.

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

Rows Inserted The number of row insertions attempted. The value format is an integer. 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. The value format is an integer. 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. The value format is an integer. Use this attribute to gain insight into the current level of activity within the database manager.

Rows Updated The number of row updates attempted. The value format is an integer. 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. The value format is an integer. 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. The value format is an integer.

Select SQL Stmts The number of SQL SELECT statements that ran. The value format is an integer.

Sort Overflows The total number of sorts that ran out of sort heap space and might have required disk space for temporary storage. The value format is an integer. 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. The value format is an integer. 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.

Static SQL Stmts The number of static SQL statements that were attempted. The value format is an integer.

Stmt Stop The string date and time that the most recent SQL statement operation stopped. The value format is a text string with a maximum of 16 characters. 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 (Unicode) The text of the dynamic SQL statement (Unicode). 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 value format is a text string with a maximum of 2000 bytes.

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 (Unicode) The name of the table the application is waiting to lock (Unicode). The value format is a text string with a maximum of 60 bytes. Use this attribute with the Table Schema attribute to determine the source of contention for resources.

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

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

Total Hash Joins The total number of hash joins that ran. The value format is an integer.

Total Hash Loops The total number of times that a single partition of a hash join was larger than the available sort heap space. The value format is an integer. 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.

Total Sort Time The total elapsed time (in milliseconds) for all sorts that ran. The value format is an integer. 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. The value format is an integer. 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.

UID SQL Stmts The number of SQL UPDATE, INSERT, and DELETE statements that ran. The value format is an integer.

UOW Lock Wait Time The time the UOW (unit of work) waited on locks (in seconds).

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. The value format is an integer. 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.