|
|
|
The fields that are displayed on the DB2View panel are described next.
Is the name of a package (DBRM) that describes the SQL statement
This field is limited to eight characters. If this value exceeds eight characters, use the D (Statement Detail) line command to view the entire field value.
Is the type of SQL being executed; either D for Dynamic or S for Static SQL.
Represents the method used to collect the SQL data being presented.
CA Mainframe Application Tuner collects data about SQL using two different methods. One is through the sampling process and the other is by the Harvester intercepting the SQL as it processes.
H indicates that this SQL data was collected by the DB2 Harvester and was not sampled as part of the sampling process. In this situation, fields that normally contain sample-based data contain zeros. Examples of those fields are %Active and %Wait.
S indicates that this SQL data was collected by the DB2 sampling SRB and was not intercepted by the DB2 Harvester. In this situation, fields that normally contain Harvester-collected data contain zeros. Examples of those fields are Call Count or any of the CPU fields, such as Total CPU.
B indicates that this SQL data was collected by both the Harvester and sampler.
SQL statements that contain data from both the Harvester and the sampler provide the most complete view of that SQL statement’s performance.
Note: There are a number of reasons why data might not be collected by either the DB2 Harvester or the sampling process, based on the DB2 options selected during customization or by the administrator of the environment after customization.
Is the number of the unique SQL statement that is contained in a Package or Pla.
Indicates the type of operation that is being performed with the statement .
CA Mainframe Application Tuner displays ???????? when the DB2 control block is not yet valid. Some values for the operation type are as follows:
Is the number of times during the monitoring session that CA Mainframe Application Tuner detected that this SQL statement was called from the target address space. A zero in this field indicates that this statement was not harvested by the Synchronous Data Gatherer and the data being presented was obtained by the sampling process.
The Synchronous Data Gatherer must be fully initialized before the first
call to DB2 is issued. If the first call is in progress, then the Harvester will not see it.
In addition, when the first call is not captured, several data fields will contain information accumulated from the start of the unit-of-work. This data is also bypassed and can result in a zero call count.
It is not uncommon for one or two rows to show zero call counts, while multi-thread environments, such as CICS, might show even more. This situation is not an error. It is an effort to provide the most accurate data possible for each call.
Is the amount of CPU time in seconds needed by DB2 to process all DB2 calls for this SQL statement by using sampling data collected during the measurement.
Is the amount of CPU time in seconds needed by DB2 to process each DB2 call for this SQL statement by using sampling data collected during the measurement.
Is the total time (in seconds) for the SQL statement to be completed.
Is the average response time for the SQL statement to be completed.
Is the amount of CPU time, in seconds, for this SQL statement while it was running under an Enclave.
Is the amount of zIIP processor CPU time, in seconds, for this SQL statement.
Is the amount of CPU that was qualified to run on a zIIP processor.
Is the amount of CPU that was qualified to run on a zIIP processor, but ran on a standard processor instead.
Is the section number within the package for this statement.
Is the number of sequential and nonsequential GETPAGE requests issued for this SQL statement.
This field addresses selectivity of the SQL statement and might indicate clustering problems.
When a GETPAGE request is made, the Buffer Manager checks if the page is already available in the pool. A high value can indicate a low selectivity of the SQL statement (database design) or a clustering problem.
Having a lot of GETPAGEs per SQL statement indicates that indexing of additional columns might improve performance. Every time a GETPAGE results in a hit (page being located in the pool), the application can begin processing the data immediately. Reducing this number improves performance.
In some cases, using compressed data results in an increase in the number of GETPAGEs, lock requests, and synchronous read I/Os. Sometimes, updated compressed rows cannot fit in the home page, and they must be stored in the overflow page. This condition can cause additional GETPAGE and lock requests.
If a page contains compressed fixed-length rows with no free space, an updated row probably has to be stored in the overflow page. To avoid the potential problem of more GETPAGE and lock requests, add more free space within the page. Start with 10% additional free space and adjust further, as needed. If, for example, 10% free space was used without compression, start with 20% free space with compression for most cases. This recommendation is especially important for data that is heavily updated.
Is a calculated value that you might find useful. It is calculated as follows:
Sync Pages Read + Async Pages Read
Is a calculated value that you might find useful. It is calculated as follows:
((Pages Read From DASD - Additional Pages Read) * 100) / Get Pages
Is a calculated value that you might find useful. It is calculated as follows:
Get Pages - (Pages Read From DASD - Additional Pages Read)
Is a calculated value that you might find useful. It is calculated as follows:
(Pages Read From Pools * 100) / Get Pages
Is a calculated value that you might find useful. It is calculated as follows:
Pages Read From DASD - Get Pages
Is the number of Index GETPAGE requests issued for this SQL statement.
Is the number of synchronous read pages issued for this SQL statement.
Synchronous read I/Os are one type of physical DB2 I/O. They address the effectiveness of the DB2 buffer pools.
This value is the number of synchronous I/O operations. When a logical read operation results in a buffer pool miss, a synchronous read occurs.
Tuning Tip: Unnecessary read I/Os are one of the principal culprits in poorly tuned applications. While random I/O cannot be avoided, critical re-referenced indexes and tables can be kept in memory. At other times, the strategy is to get the data into memory before the application needs it, typically when processing the data pages sequentially. This procedure is done with prefetch and possibly with parallel processing.
This field only appears if at least one row has a non-zero value.
Is the number of asynchronous pages read by prefetch operations that were issued for this SQL statement.
Tuning Tip: Generally, unless the application is totally random, the higher the prefetch number, the more likely it is that the data will be in memory when needed. The more successful that read-ahead buffering is, the faster the application should perform.
This field only appears if at least one row has a non-zero value.
Is the number of sequential prefetch requests that were issued for this SQL statement.
Sequential prefetch is performed concurrently with other operations of the originating application program. It brings pages into the buffer pool before they are required and reads several pages with a single I/O operation. Sequential prefetch can be used to read data pages, by table space scans or index scans with clustered data reference. It can also be used to read index pages in an index scan. Sequential prefetch allows CP and I/O operations to be overlapped.
Tuning Tip: A buffer pool must be at least 1000 pages to get the full benefit of sequential prefetch. In addition, a sufficient number of sequential pages must be allowed in the buffer pool assigned to the accessed table (VPSEQT parameter). Otherwise, the prefetch quantity might be reduced, or prefetch disabled.
This field only appears if at least one row has a non-zero value.
Is the number of list prefetch requests that were issued for this SQL statement.
List prefetch reads a set of data pages determined by a list of RIDs (ROW IDs) taken from an index. The data pages need not be contiguous. The maximum number of pages that can be retrieved in a single list prefetch is 32 (64 for utilities). List prefetch can be used in conjunction with either single or multiple index access. List prefetch is used in the following circumstances:
This field only appears if at least one row has a non-zero value.
Is the number of dynamic prefetch requests issued for this SQL statement.
Dynamic prefetch can reduce paging and improve performance over sequential prefetch for some data access that involves data that is not on consecutive pages.
At runtime, dynamic prefetch might or might not actually be used. Dynamic prefetch is used in prefetch situations when the pages that DB2 will access are distributed in a nonconsecutive manner. If the pages are distributed in a sufficiently consecutive manner, sequential prefetch will be used instead.
Tuning Tip: Normally dynamic prefetch assists programs by providing read-ahead buffering for processing; GETPAGEs that would have to wait for synchronous I/O now find the page in the buffer pool. This number should be monitored because the more dynamic prefetch requests activated, the more buffer pool resources can be strained. It might be necessary to alter the buffer pool size or sequential steal threshold to manage increased demands of dynamic prefetch.
This field only appears if at least one row has a non-zero value.
Is the number of GETPAGE requests issued to satisfy a Large OBject column SQL request.
This field only appears if at least one row has a non-zero value.
Is the number of rows that are returned by a FETCH, updated by an UPDATE, inserted by an INSERT, or returned from cache for a PREPARE.
This field only appears if at least one row has a non-zero value.
Is the statement number of the DECLARE that is associated with the statement or NA if no DECLARE is associated with the statement.
Is the length of the SQL statement. The statement has had all extra spaces removed.
Number of parallel subtasks created on behalf of this statement. This field only appears if at least one row has a non-zero value.
Number of parallel groups executed for this SQL statement. This field only appears if at least one row has a non-zero value.
Number of times a statement trigger was activated by this SQL statement. This field only appears if at least one row has a non-zero value.
Number of times a row trigger was activated by this SQL statement. This field only appears if at least one row has a non-zero value.
Number of times re-optimization occurred for this SQL statement. This field only appears if at least one row has a non-zero value.
Number of times that a prepare for this SQL statement was avoided because KEEPDYNAMIC(YES) was used along with prepared statement caching and DB2 still had a copy of the executable version of the statement. This field only appears if at least one row has a non-zero value.
Number of times that a PREPARE request for this statement was satisfied by making a copy from the prepared statement cache.
Number of times that a PREPARE request for this statement was received, but a matching statement was not found in the prepared statement cache.
Is the number of times DB2 executed an incremental bind to account for changes in the DBRM. This field only appears if at least one row has a non-zero value.
Is the name of the location which originated this statement. This field is limited to eight characters. If this value exceeds eight characters, use the D (Statement Detail) line command to view the entire field value.
Is the correlation ID of the DB2 thread, for example:
Is the original primary auth ID of the requesting user. This field is limited to eight characters. If this value exceeds eight characters, use the D (Statement Detail) line command to view the entire field value.
Is the plan name that describes the SQL statement. This field is limited to eight characters. If this value exceeds eight characters, use the D (Statement Detail) line command to view the entire field value.
Is the name of the group of packages that have the same qualifier. This field is limited to eight characters. If this value exceeds eight characters, use the D (Statement Detail) line command to view the entire field value.
Is the address of the Agent Control Element for this DB2 thread.
Is a counter, controlled by DB2, which is incremented each time the agent calls DB2. The size of this counter is two bytes and will wrap to zero and begin again when the two byte size limit is exceeded.
Is the name of the requesting address space Job Nam.
Is the name of the executing address space Job Name. For example, this name is the name of Stored Procedures address space when a CALL is executed or a FUNCTION is used.
Is the z/OS Workload Manager Service Class name for a DDF thread.
Is the name of the Application Environment defined for this Stored Procedure or Function. This field is limited to eight characters. If this value exceeds eight characters, use the D (Statement Detail) line command to view the entire field value.
Is the name of the cursor for this SQL statement. This field is limited to eight characters. If this value exceeds eight characters, use the D (Statement Detail) line command to view the entire field value.
Is the qualified DB2 stored procedure name or qualified function name invoked by the application program.
The name is derived from the qualifier followed by the name as defined by the creator of stored procedure or function.
This field is limited to 17 characters. If this value exceeds 17 characters, use the D (Statement Detail) line command to view the entire field value.
Displays the complete 24-character call type issued with this statement as an argument. Some values for extended call type are:
Note: some fields might not be displayed automatically when data for those fields has not been collected.
| Copyright © 2011 CA. All rights reserved. | Tell Technical Publications how we can improve this information |