Database02 (KUD_DB2_Database02) attributes

Use these attributes to obtain information about database activities.

Active Hash Joins The total number of hash joins that are currently running and consuming memory.

Active OLAP Funcs The total number of OLAP functions that are currently running and consuming sort heap memory.

Appl ID Oldest Xact The application ID of the application that has the oldest transaction. Use this attribute to which application has the oldest active transaction. This application can be forced to free up log space. If the application take a large amount of log space, examine the application to determine if it can be modified to commit more frequently.

Async Runstats The total number of successful asynchronous RUNSTATS activities that are performed by real-time statistics gathering for all the applications in the database. Values reported by all the database partitions are aggregated together.

Blocks Pending Cleanup The total number of MDC table blocks in the database that are pending asynchronous cleanup following a roll out delete.

Cat Cache Size Top The largest size that is reached by the catalog cache. This attribute indicates the maximum number of bytes the catalog cache required for the workload run against the database since it is activated. If the catalog cache overflows, the value is the largest size reached by the catalog cache during the overflow.

Catalog Partition The number of the catalog node.

Catalog Partition Name The network name of the catalog node.

Data Temp Pool Hit Ratio The data page hit ratio for buffer pools that are located in temporary tablespaces.

DB Name The real name of the host 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 60 bytes.

DB Partition The DB2® database partition node number, which can range from 0 to 999. The Aggregated and Current® Partition values can be used within a query or situation filter. If a db partition filter is not specified, data is returned for the current database partition. If a db partition filter is set to Aggregated, only aggregated partition data is returned. Historical data collection includes both aggregated and individual partition attribute data. In addition to numeric partition numbers in the 0 to 999 range, the following values are also valid:

External value	Internal value
Aggregated	-1
Current Partition	-2
All	-3

Elapsed Exec Time MS At the DCS statement level, this is the elapsed time (in ms) spent processing an SQL request on a host database server.

Elapsed Exec Time S At the DCS statement level, this is the elapsed time (in seconds) spent processing an SQL request on a host database server.

Instance Name The name of the monitored DB2 instance. The valid format is a text string with a maximum of 60 bytes.

Last Reset Indicates the most recent date and time when the monitor counters are reset for the application issuing the GET SNAPSHOT command. Use this attribute to determine the scope of information returned by the database system monitor.

Log Held by Dirty Pages The amount of log (in bytes) corresponding to the difference between the oldest dirty page in the database and the top of the active log. When the snapshot is taken, this value is calculated based on conditions at the time of that snapshot. Use this element to evaluate the effectiveness of page cleaning for older pages in the buffer pool. The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Log Read Time NS The total elapsed time (in ns) that the logger spends reading log data from the disk. Use this attribute with the log reads, num log read io, and num log data found in buffer attributes to determine the following items:

Whether the current disk is adequate for logging.
Whether the log buffer size is adequate.

The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Log Read Time S The total elapsed time (in seconds) that the logger spends reading log data from the disk. Use this attribute with the log reads, num log read io, and num log data found in buffer attributes to determine the following items:

Whether the current disk is adequate for logging.
Whether the log buffer size is adequate.

The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Log to Redo for Recovery The amount of log (in bytes) that has to be redone for crash recovery.

Log Write Time NS The total elapsed time (in ns) that the logger spends writing log data to the disk. Use this attribute with the log writes and num log write io attributes to determine whether the current disk is adequate for logging. The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Log Write Time S The total elapsed time (in seconds) that the logger spends writing log data to the disk. Use this attribute with the log writes and num log write io attributes to determine whether the current disk is adequate for logging. The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Min Catalog Cache Size The minimum size of the catalog cache that is required by your workload.

Min Pkg Cache Size The minimum size of the package cache that is required by your workload.

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 Windows systems is instanceid:hostname:UD; on UNIX and Linux® systems, the format is instanceid:hostname.

Num DB Storage Paths The number of automatic storage paths that are associated with this database.

Num Indoubt Trans The number of outstanding indoubt transactions in the database. Indoubt transactions hold log space for uncommitted transactions, which can cause the logs to become full. When the logs are full, further transactions can not be completed. The resolution of this problem involves a manual process of heuristically resolving the indoubt transactions. This attributes provides a count of the number of currently outstanding indoubt transactions that must be heuristically resolved.

Num Log Buffer Full The number of times that agents have to wait for log data to write to disk while copying log records into the log buffer. This value is increased per agent per incident. For example, if two agents attempt to copy log data while the buffer is full, this value is increased by two. Use this attribute to determine if the LOGBUFSZ database configuration parameter needs to be increased. The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Num Log Data Found in Buffer The number of times that an agent reads log data from the buffer. Reading log data from the buffer is preferable to reading from the disk because the latter is slower. Use this attribute with the num log read io attribute to determine if the LOGBUFSZ database configuration parameter needs to be increased. The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Num Log Part Page IO The number of I/O requests that are issued by the logger for writing partial log data to the disk. Use this attribute with the log writes, log write time, and num log write io attributes to determine if the current disk is adequate for logging. The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Num Log Read IO The number of I/O requests that are issued by the logger for reading log data from the disk. Use this attribute with the log reads and log read time attributes to determine if the current disk is adequate for logging. The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Num Log Write IO The number of I/O requests that are issued by the logger for writing log data to the disk. Use this attribute with the log writes and log write time attributes to determine if the current disk is adequate for logging. The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Num Threshold Violations The number of threshold violations that have taken place in this database since the database was last activated. Use this attribute to determine whether thresholds are effective for this particular application or whether the threshold violations are excessive.

OLAP Func Overflows The number of times that OLAP function data exceeded the available sort heap space. At the database level, use this attribute in conjunction with the total olapfuncs attribute to calculate the percentage of OLAP functions that overflowed to disk. If this percentage is high and the performance of applications using OLAP functions needs to be improved, consider increasing the sort heap size. At the application level, use this attribute to evaluate OLAP function performance for individual applications.

Pkg Cache Num Overflows The number of times that the package cache overflowed the bounds of its allocated memory.

Pkg Cache Size Top The largest size that is reached by the package cache. If the package cache overflowed, this attribute value is the largest size that is reached by the package cache during the overflow. Check the pkg cache num overflows attribute to determine if such a condition occurred.

Pool No Victim Buffer The number of times an agent does not have a preselected victim buffer that is available. Use this attribute to help evaluate whether you have enough page cleaners for a given buffer pool when using proactive page cleaning.

Pool Temp Data L Reads The number of data pages that have been requested from the buffer pool (logical) for temporary tablespaces. In conjunction with the pool temp data p reads attribute, the data page hit ratio for buffer pools located in temporary tablespaces can be calculated using the following formula:

1 - (pool temp data p reads / pool temp data l reads)

Pool Temp Data P Reads The number of data pages read in from the tablespace containers (physical) for temporary tablespaces.

Pool Temp Hit Ratio The data page and index page hit ratio for buffer pools that are located in temporary tablespaces.

Pool Temp Index L Reads The number of index pages that have been requested from the buffer pool (logical) for temporary tablespaces.

Pool Temp Index P Reads The number of index pages read in from the tablespace containers (physical) for temporary tablespaces.

Pool Temp XDA L Reads The number of pages for XML storage object (XDA) Data that is requested from the buffer pool (logical) for temporary tablespaces.

Pool Temp XDA P Reads The number of pages for XML storage object (XDA) Data that is read in from the tablespace containers (physical) for temporary tablespaces.

Pool XDA L Reads The number of data pages for XML storage objects (XDAs) that are requested from the buffer pool (logical) for regular and large tablespaces.

Pool XDA P Reads The number of data pages for XML storage objects (XDAs) that are read in from the tablespace containers (physical) for regular and large tablespaces.

Pool XDA Writes The number of times that a buffer pool data page for an XML storage object (XDA) is physically written to disk.

Post Shr Threshold Hash Joins The total number of hash joins that were throttled back by the sort memory throttling algorithm. A throttled hash join is a hash join that was granted less memory than requested by the sort memory manager. A hash join is throttled back when the memory allocation from the shared sort heap is close to the limit that is set by the sheapthres_shr database configuration parameter. This throttling significantly reduces the number of overflows over sheapthres_shr limit in a system that is not properly configured. The data reported in this element only reflects hash joins using memory allocated from the shared sort heap.

Post Shr Threshold Sorts The total number of sorts that were throttled back by the sort memory throttling algorithm. A throttled sort is a sort that was granted less memory than requested by the sort memory manager. A sort is throttled back when the memory allocation for sorts is close to the limit that is set by the sheapthres_shr database configuration parameter. This throttling significantly reduces the number of overflows over sheapthres_shr limit in a system that is not properly configured. The data reported by this attribute only reflects sorts using memory allocated from the shared sort heap.

Priv Workspace Num Overflows The number of times that the private workspaces overflowed the bounds of its allocated memory. Use this attribute with the priv workspace size top attribute to determine whether the size of the private workspace needs to be increased to avoid overflowing. Overflows of the private workspace might cause performance degradation and out of memory errors from the other heaps allocated out of agent private memory.

Priv Workspace Section Inserts The number of inserts of SQL sections by applications into the private workspace. The working copy of executable sections are stored in the private workspace. This attribute indicates the number of times when a copy was not available and had to be inserted. At the database level, it is the cumulative total of all inserts for every application across all private workspaces in the database. At the application level, it is the cumulative total of all inserts for all sections in the private workspace for this application.

Priv Workspace Section Lookups Indicates how many times the private workspace was accessed in order to locate a specific section for an application. At the database level, it is the cumulative total of all lookups for every application across all private workspaces in the database. At the application level, it is the cumulative total of all lookups for all sections in the private workspace for this application.

Priv Workspace Size Top The largest size reached by the private workspace.

Rows Read The number of rows that are read from the table. Use this attribute to identify tables with heavy usage, and for which you might want to create additional indexes. This attribute is not the number of rows that are returned to the calling application; it is the number of rows that must be read in order to return the result set.

Shr Workspace Num Overflows The number of times that shared workspaces overflowed the bounds of their allocated memory. Use this attribute with the shr workspace size top attribute to determine whether the size of the shared workspaces need to be increased to avoid overflowing. Overflows of shared workspaces might cause performance degradation and out of memory errors from the other heaps that are allocated out of application shared memory.

Shr Workspace Section Inserts The number of inserts of SQL sections by applications into shared workspaces. The working copy of executable sections are stored in shared workspaces. This attribute indicates the number of times when a copy was not available and had to be inserted. At the database level, it is the cumulative total of all inserts for every application across all shared workspaces in the database. At the application level, it is the cumulative total of all inserts for all sections in the shared workspace for this application.

Shr Workspace Section Lookups Indicates how many times shared workspaces were accessed in order to locate a specific section for an application. At the database level, it is the cumulative total of all lookups for every application across all shared workspaces in the database. At the application level, it is the cumulative total of all lookups for all sections in the shared workspace for this application.

Shr Workspace Size Top The largest size reached by shared workspaces.

Smallest Log Avail Node Indicates the node with the least amount (in bytes) of available log space.

Snapshot Timestamp The date and time when the database system monitor information was collected. Use this attribute to help relate data chronologically if you are saving the results in a file or database for ongoing analysis.

Sort Shrheap Allocated The total amount of shared sort memory allocated in the database.

Sort Shrheap Top The high watermark (in 4KB pages) of the database-wide shared sort memory.

Stats Cache Size The current size of the statistics cache, which is used in a catalog partition to cache statistics information generated by real-time statistics gathering. Use this attribute to determine the size of the current statistics cache.

Stats Fabricate Time The total time (in milliseconds) spent on statistics fabrications by real-time statistics gathering. Statistics fabrication is the statistics collection activity needed to generate statistics during query compilation. If this attribute value is collected at the database level, it represents the total time spent on real-time statistics gathering activities for all the applications running on the database. If this attribute value is collected at the statement level, it represents the time spent on the latest real-time statistics gathering activities for the statement. The times reported by all the database partitions are aggregated together.

Stats Fabrications The total number of statistics fabrications that are performed by real-time statistics during query compilation for all the database applications. Instead of obtaining statistics by scanning data stored in a table or an index, statistics are fabricated based on metadata maintained by the index and data manager. Values reported by all the database partitions are aggregated together.

Sync Runstats The total number of synchronous RUNSTATS activities triggered by real-time statistics gathering for all the applications in the database. This value includes both successful and unsuccessful synchronous RUNSTATS commands. Values reported by all the database partitions are aggregated together.

Sync Runstats Time The total time spent on synchronous RUNSTATS activities triggered by real-time statistics gathering, in milliseconds. The synchronous RUNSTATS activities occur during query compilation. At the database level, this attribute value represents the total time spent on synchronous RUNSTATS activities for all the applications running on the database, triggered by real-time statistics gathering. At the statement level, this attribute value represents the time spent on the latest synchronous RUNSTATS activities for a particular statement, triggered by real-time statistics gathering. Values reported by all the database partitions are aggregated together.

Total Log Available The amount of active log space in the database that is not being used by uncommitted transactions (in bytes). Use this element in conjunction with the total log used attribute to determine whether you need to adjust the following configuration parameters of the monitored DB2 instance to avoid running out of log space:

logfilsiz
logprimary
logsecond

The following value is valid:

External value	Internal value
Value Exceeds Maximum	9223372036854775807

Total Log Used Pct The percentage of the log space that is used in the database.

Total OLAP Funcs The total number of OLAP functions that run.

Unread Prefetch Pages The number of pages that the prefetcher read in but are never used. If the value is high, prefetchers are causing unnecessary I/O by reading pages into the buffer pool that will not be used.