In addition to the new data elements for DB2 Connect (see "New Data Elements"), Version 5.2 adds data elements to help you monitor:
Hash join is an additional option for the optimizer (see "Hash Joins" for more details).
Snapshot Information Level Database Application |
API Structure(s) sqlm_dbase sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
Yes |
|
Event Type Database Connection |
Event Record(s) sqlm_db_event sqlm_conn_event |
|
API Element Name Element Type |
total_hash_joins counter | |
| Related Information |
| |
Description: The total number of hash joins executed.
Usage: At the database or application level, use this value in conjunction with "Hash Join Overflows" and "Hash Join Small Overflows" to determine if a significant percentage of hash joins would benefit from modest increases in the sort heap size.
Snapshot Information Level Database Manager |
API Structure(s) sqlm_db2 |
Monitor Switch Basic |
Resettable |
Yes |
|
API Element Name Element Type |
post_threshold_hash_joins counter | |
| Related Information |
| |
Description: The total number of times that a hash join heap request was limited due to concurrent use of shared or private sort heap space.
Usage: If this value is large (greater than 5% of "Hash Join Overflows"), the sort heap threshold should be increased.
Snapshot Information Level Database Application |
API Structure(s) sqlm_dbase sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
Yes |
|
Event Type Database Connection |
Event Record(s) sqlm_db_event sqlm_conn_event |
|
API Element Name Element Type |
total_hash_loops counter | |
| Related Information |
| |
Description: The total number of times that a single partition of a hash join was larger than the available sort heap space.
Usage: Values for this data element 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 in conjunction with the other hash join variables to tune the sort heap size (sortheap) and sort heap threshold (sheapthres) configuration parameters.
Snapshot Information Level Database Application |
API Structure(s) sqlm_dbase sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
Yes |
|
Event Type Database Connection |
Event Record(s) sqlm_db_event sqlm_conn_event |
|
API Element Name Element Type |
hash_join_overflows counter | |
| Related Information |
| |
Description: The number of times that hash join data exceeded the available sort heap space.
Usage: At the database level, if the percentage of "Hash Join Small Overflows" is greater than 10% of this value, then you should consider increasing the sort heap size. Values at the application level can be used to evaluate hash join performance for individual applications.
Snapshot Information Level Database Application |
API Structure(s) sqlm_dbase sqlm_appl |
Monitor Switch Basic Basic |
Resettable |
Yes |
|
Event Type Database Connection |
Event Record(s) sqlm_db_event sqlm_conn_event |
|
API Element Name Element Type |
hash_join_small_overflows counter | |
| Related Information |
| |
Description: The number of times that hash join data exceeded the available sort heap space by less than 10%.
Usage: If this value and "Hash Join Overflows" are high, then you should consider increasing the sort heap threshold. If this value is greater than 10% of "Hash Join Overflows", then you should consider increasing the sort heap size.
When a new row is being inserted or an existing row is being updated, resulting in an increased record size, the page where this record is to be placed may have enough free space, but that space could be fragmented. In these cases the page may require reorganization, which moves all fragmented space to a contiguous area, where the new record can be written. Such a page reorganization (page reorg) is very expensive to perform.
Snapshot Information Level Table |
API Structure(s) sqlm_table |
Monitor Switch Table |
Resettable |
Yes |
|
Event Type Table |
Event Record(s) sqlm_table_event |
|
API Element Name Element Type |
page_reorgs counter | |
| Related Information |
| |
Description: The number of page reorgs executed for a table.
Usage: Too many page reorgs can result in less than optimal insert performance. You can use the REORG TABLE utility to reorganize a table and eliminate fragmentation. You can also use the APPEND parameter for the ALTER TABLE statement to indicate that all inserts are appended at the end of a table and so avoid page reorgs.
In situations where updates to rows causes the row length to increase, the page may have enough space to accommodate the new row, but a page reorg may be required to defragment that space. Or if the page does not have enough space for the new larger row, an overflow record is created being created causing Accesses to Overflowed Records during reads. You can avoid both situations by using fixed length columns instead of varying length columns.
Lock escalation occurs when the number of locks used by a given application exceeds the configured limit or the number of locks configured for all applications is exhausted. During lock escalation a few highly restrictive locks are used to replace many less restrictive locks. Although this reduces the total number of locks used, it also increases the number of lock waits and potentially causes deadlocks.
Snapshot Information Level Lock Lock |
API Structure(s) sqlm_lock sqlm_lock_wait |
Monitor Switch Lock Lock |
Resettable |
No |
|
Event Type Deadlock |
Event Record(s) sqlm_dlconn_event |
|
API Element Name Element Type |
lock_escalation information | |
| Related Information |
| |
Description: Indicates whether a lock request was made as part of a lock escalation.
Usage: Use this element to better understand the cause of deadlocks. If you experience a deadlock that involves applications doing lock escalation, you may want to increase the amount of lock memory or change the percentage of locks that any one application can request.