Exception detection and reporting includes Print
Other
  • Objects in unexpected restricted state and the affected plans/packages – the tool recognizes restricted states on table- and indexspaces, in order to distinguish between problems and harmless events, XM checks simultaneously whether utilities are working with these objects and whether those utilities are stopped.
  • Predictable lack of space of objects, whether attributable to ICF or DB2 – various limits restrict the maximum size of a table- or indexspace, XM determines the current size and the current number of extents, it reports an exception if the fill level or number of extents exceed a threshold.
  • LPL Status  - Volume/disk errors invoke LPL, as a result a -904 will occur, XM warns you in time.
  • Stopped utilities and involved objects  - XM warns immediately and offers reports on current and past issues.
  • Stopped procedures & queue length. - Contemporary workloads invoke countless procedures. DB2 itself shifts version by version more and more tasks into procedures and the dependency on procedures increases continuously and thus surveillance becomes more important.
  • In-doubt threads and implications to objects -  Most DB2 systems are automatically restarted after a system failure by ARM or another restart mechanism. Postponed or in-doubt threads are easily forgotten, which leads to program failures due to old locks. Ideally the DBA receives two emails from XM, one stating the problem and the other reporting the applied solution.
  • Difficulties with DDF and queue length - there is a tendency for more dynamic SQL versus static SQL so that DB2DIST becomes more and more important, surveillance is required.
  • Invalid or inoperative plans/packages - every installation has plans that are not valid, but more troublesome can be new, upcoming invalidations. XM reports invalidation events immediately. XM also reports plans/packages as implicitly invalid as soon as objects become unavailable that are accessed through these plans/packages. XM also reports programs that are non-executable.
  • Exceptions in respect of DB2 Log & BSDS – log data is stored in memory and from time to time written to a log dataset, XM recognizes whether there are write or off-load problems; the BSDS contains the log dataset list, XM checks this list for validity and completeness.
  • Bufferpool and group bufferpool related issues - The buffer pools significantly affect throughput and performance. XM examines pool behavior and IO performance and detects and alerts:
    • Insufficient IO Performance due improper pool adjustment
    • Paging due to memory over-allocation
    • Shortage in Coupling Facility, SCA and Group Bufferpools
    • Problems due to inappropriate bufferpool thresholds and improper allocated memory
    • General IO problems
  • Dynamic SQL: SQL surveillance and detection of unexpected peaks by monitoring of the statement cache - DB2 Version 9 came with substantial changes to the optimizer -  changes that do not always lead to improvements. Some applications suffer with bad response times and show excess consumption of CPU time. Whereas static SQL is fairly controllable because changes require a rebind, dynamic SQL is more vulnerable. XM monitors and examines the dynamic workload, filters out of thousands of SQL statements those that have problems. XM automatically:
    • Identifies SQL statements with excessive CPU consumption
    • Identifies SQL statements that do not use parameter markers, flood the cache, jack up CPU consumption, and reduce the number of cache hits
    • Verifies statistics by comparing optimizer estimations with the actual run time - alerts deviations and corrects statistics
    • Identifies problems with access path - the affected statements are listed for further analysis, for example under IBM's free OSC (Optimization Center)
    • Identifies the cause of Locks
    • Determines costly SORTS
  • The focus of this capability is cost reduction. End users increasingly deploy reporting tools and process large amounts of ad-hoc data, which leads to inefficiency and waste of resources. Supervision is time consuming. With XM the DBA sets threshold values and receives emails that present the “wasters”. XM identifies performance killers like DSC trashing statements, statements with inefficient search criteria, excessive CPU consuming sorts, and SQL's with a strong discrepancy between optimizer’s estimation and the true runtime.
  • Recoverability - Service level agreements call for maximum availability and stipulate shortest affordable downtimes. XM will:
  • Ascertain the recoverability of all objects through ongoing checks of BSDS, Imagecopies and Log datasets.
  • Determine the expected recovery time, compares it with the set point of the recoverability regulations and raises the alarm where necessary.
  • Take immediate actions to ensure recoverability.