Monitoring and workload management fixpack enhancements that may have slipped your notice

At times, we do slide additional enhancements or new/different behaviours into a DB2 for LUW version after it has been GA'd to address things that we found as the result of our ongoing internal use of the new feature or specific customer feedback. These changes are always rolled into the next version of DB2 but often their introduction in fix packs for the earlier versions is missed by our customers.

Here is a list that some people have found useful of what is available (and when) in DB2 LUW fixpacks from a workload management and monitoring perspective... I don't claim the list is exhaustive but it is as close as I can get :)

You can also find what has been added yourself by typing "what's new in fix pack" in the search window of the DB2 Knowledge Center.

 

DB2 10.5 Fix Pack 4 (Cancun)

Monitor enhancements:

  • New time-spent monitor elements provide more granular information about the time that is spent by the database manager on various operations
  • New monitor elements assist with aggregation of package cache monitor data and detection of plan changes
  • New monitor elements identify statements in the package cache that had a suboptimal compilation
  • New built-in routine MON_GET_SECTION helps to retrieve and store a copy of the executable section for further investigation
  • New monitor elements monitor the use of sort heap memory to help you with its sizing and allocation
  • New columns added to MON_GET_HADR to help identify communication problems

DB2 10.1 Fix Pack 1

UOWTOTALTIME Threshold Enhancement

  • Threshold time can now be expressed in units of 10 seconds with the minimum time reduced from 1 minute to 10 seconds
  • This threshold will now act upon a violation when it occurs rather than wait for the next incoming request.

ACTIVITYTOTALTIME Threshold Enhancement

  • Threshold time can now be expressed in units of 10 seconds with the minimum time reduced from 1 minute to 10 seconds

Statistics Event Monitor Enhancement

  • Additional logical data groups added to simplify use of in-memory metrics
    • Separate table for workload and service classes
    • Column per metric
    • Column value is delta value from the last statistics collection or database activation, whichever was more recent
  • In addition, a new element was added to present all these metrics into a single XML document

DB2 10.1 Fix Pack 2

Monitoring enhancements

  • New table functions collect routine information:
    • MON_GET_ROUTINE
    • MON_GET_ROUTINE_DETAILS
    • MON_GET_ROUTINE_EXEC_LIST
    • MON_GET_SECTION_ROUTINE
  • Updated table functions now contain additional statement information to help correlate statements to the routine that executed them:
    • MON_GET_PKG_CACHE_STMT
    • MON_GET_PKG_CACHE_STMT_DETAILS
    • MON_GET_ACTIVITY_DETAILS
    • WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES
  • Event monitors enhanced to contain additional statement information to help correlate statements to the routine that executed them:
    • Activities
    • Package cache
  • ENV_GET_NETWORK_RESOURCES table function ectended to get more operating system metrics for the HP-UX and Solaris platforms.

DB2 9.7 Fix Pack 1

Migration Script for Query Patroller to WLM

  • New script will be provided to aid in the conversion from Query Patroller to DB2 Workload Management
  • Generates the DDL statements needed to establish control mechanisms that closely emulate those implemented in a current Query Patroller installation

Synchronizing WLM Statistics Collection

  • Automatic WLM statistics collection will now be synchronized to a fixed base time. The new implementation has intervals starting from a fixed base of Sunday 00:00

DB2 Work Action Set Enhancements

  • Introduce the ability to assign a work action set to a workload definition

DB2 Threshold Enhancements

  • New UOW Time Threshold

DB2 9.7 Fix Pack 2

WLM_SET_CONN_ENV() Procedure

  • A procedure that lets you set the collection attributes for a session without defining a workload

WLM_GET_CONN_ENV() Procedure

  • A procedure to read the current collection settings for a session

DB2 9.7 Fix Pack 4

Activity Event Monitor Enhancement

  • Add event_activitymetrics logical group to activity event monitor to provide new DB2 9.7 metrics in a separate table for easy SQL access

WLM_COLLECT_STATS Enhancements

  • A new input parameter on this procedure to have it wait to return to called until all event monitor records have been flushed to disk
  • A new output parameter to return timestamp for statistics just collected

DB2 9.7 Fix Pack 5

Monitoring Enhancements

  • Two new interfaces introduced to provide access to memory usage information through SQL
    • MON_GET_MEMORY_POOL
    • MON_GET_MEMORY_SET
  • New columns added to
    • MON_BP_UTILIZATION
    • MON_GET_BUFFERPOOL
    • MON_GET_TABLE
    • MON_GET_TABLESPACE

ACTIVITYTOTALTIME Threshold Enhancement

  • Threshold time can now be expressed in units of 10 seconds with the minimum time reduced from 1 minute to 10 seconds

DB2 9.7 Fix Pack 6

Statistics Event Monitor Enhancement

  • Additional logical data groups added to simplify use of in-memory metricsSeparate table for workload and service classes
    • Column per metric
    • Column value is delta value from the last statistics collection or database activation, whichever was more recent
    • In addition, a new element was added to present all these metrics into a single XML document

UOWTOTALTIME Threshold Enhancement

  • Threshold time can now be expressed in units of 10 seconds with the minimum time reduced from 1 minute to 10 seconds
  • This threshold will now act upon a violation when it occurs rather than wait for the next incoming request.

Threshold Violation Event Monitor Enhancement

  • Additional columns added to output including:
    • application name
    • workload ID
    • hostname of the machine the client application is connecting from

Monitoring Enhancements

  • Three new table functions provide information about the environment in which the DB2 product runs
    • ENV_GET_SYSTEM_RESOURCES which returns operating system, CPU, memory, and other information that is related to members on the system
    • ENV_GET_DB2_SYSTEM_RESOURCES which returns CPU usage and DB2 process information for specified members in the current instance.
    • ENV_GET_NETWORK_RESOURCES which returns information for all active network adaptors on the host machines running the DB2 product.

DB2 9.7 Fix Pack 7

Monitoring Enhancements

  • Additional client identification fields have been added to these interfaces
    • MON_GET_CONNECTION
    • MON_GET_UNIT_OF_WORK
0 Comments
Recent Stories
Verson 3.0.4 of IBM Graphical WLM tool now available in developerWorks

A quick summary of available Db2 controls for system resources

Managing resource consumption for multiple databases under the same DB2 instance