Example: Using DB2 workload management table functions

A large amount of data is available through DB2® workload management real-time monitoring. The example in this topic shows how you might start using the information.

In this situation, only the default workload and service class are in place. Use this example to understand how you can use the table functions to understand what, exactly, is running on the data server. Follow these steps:

1. Use the Service Superclass Statistics table function to show all of the service superclasses. After you install or upgrade to DB2 9.5 or later, three default superclasses are defined: one for maintenance activities, one for system activities, and one for user activities. SYSDEFAULTUSERCLASS is the service class of interest.

   SELECT VARCHAR(SERVICE_SUPERCLASS_NAME,30) AS SUPERCLASS
      FROM TABLE(WLM_GET_SERVICE_SUPERCLASS_STATS('',-1)) AS T
   
   SUPERCLASS
   ------------------------------
   SYSDEFAULTSYSTEMCLASS
   SYSDEFAULTMAINTENANCECLASS
   SYSDEFAULTUSERCLASS
   
     3 record(s) selected.

2. Use the Service Subclass Statistics table function to show statistics for all the service subclasses of the SYSDEFAULTUSERCLASS superclass. For each service subclass you can see the current volume of requests that are being processed, the number of activities that have completed execution, and the overall distribution of activities across members (possibly indicating a problem if the distribution is uneven). You can optionally obtain additional statistics including the average lifetime for activities, the average amount of time activities spend queued, and so on. You can obtain optional statistics for a service subclass by specifying the COLLECT AGGREGATE ACTIVITY DATA keyword on the ALTER SERVICE CLASS statement to enable aggregate activity statistics collection.

   SELECT VARCHAR(SERVICE_SUPERCLASS_NAME, 20) AS SUPERCLASS,
      VARCHAR(SERVICE_SUBCLASS_NAME, 20) AS SUBCLASS,
      COORD_ACT_COMPLETED_TOTAL,
      COORD_ACT_ABORTED_TOTAL,
      COORD_ACT_REJECTED_TOTAL,
      CONCURRENT_ACT_TOP
   FROM TABLE(WLM_GET_SERVICE_SUBCLASS_STATS(
      'SYSDEFAULTUSERCLASS', 'SYSDEFAULTSUBCLASS', -1))
      AS T

   SUPERCLASS           SUBCLASS             COORD_ACT_COMPLETED_TOTAL COORD_ACT_ABORTED_TOTAL COORD_ACT_REJECTED_TOTAL CONCURRENT_ACT_TOP
   -------------------- -------------------- ------------------------- ----------------------- ------------------------ ------------------
   SYSDEFAULTUSERCLASS  SYSDEFAULTSUBCLASS                           2                       0                        0                  1
   
     1 record(s) selected.

3. For a given service subclass, use the Workload Occurrence Information table function to list the occurrences of a workload that are mapped to the service subclass. The table function displays all of the connection attributes, which you can use to identify the source of the activities. This information can be quite useful in determining custom workload definitions in the future. For example, perhaps a specific workload occurrence listed here has a large volume of work from an application as shown by the activities completed counter.

   SELECT APPLICATION_HANDLE,
      VARCHAR(WORKLOAD_NAME, 30) AS WORKLOAD,
      VARCHAR(SESSION_AUTH_ID, 20) AS SESSION_AUTH_ID,
      VARCHAR(APPLICATION_NAME, 20) AS APPL_NAME
   FROM TABLE(WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES(
      'SYSDEFAULTUSERCLASS', 'SYSDEFAULTSUBCLASS', -1))
      AS T

   APPLICATION_HANDLE   WORKLOAD                       SESSION_AUTH_ID      APPL_NAME
   -------------------- ------------------------------ -------------------- --------------------
                    431 SYSDEFAULTUSERWORKLOAD         SWALKTY              db2bp
   
     1 record(s) selected.
     

   1. For that application, use the Workload Occurrence Activities Information table function to show the current activities across database members that were created from the application's connection. You can use this information for a number of purposes, including identifying activities that might be causing problems on the data server.

      SELECT APPLICATION_HANDLE,
         LOCAL_START_TIME,
         UOW_ID,
         ACTIVITY_ID,
         ACTIVITY_TYPE
      FROM TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(431,-1)) AS T

      APPLICATION_HANDLE   LOCAL_START_TIME           UOW_ID      ACTIVITY_ID ACTIVITY_TYPE
      -------------------- -------------------------- ----------- ----------- --------------------------------
                       431 2008-06-17-12.49.46.854259          11           1 READ_DML
      
        1 record(s) selected

   2. For each activity, retrieve more detailed information by using the Activity Details table function. The data might show that some SQL statements are returning huge numbers of rows, that some activities have been idle for a long time, or that some queries are running that have an extremely large estimated cost. In situations such as these, it might make sense to define some thresholds to identify and prevent potentially damaging behavior in the future.

      SELECT VARCHAR(NAME, 20) AS NAME,
         VARCHAR(VALUE, 40) AS VALUE
      FROM TABLE(WLM_GET_ACTIVITY_DETAILS(431,11,1,-1))
         AS T WHERE NAME IN ('UOW_ID', 'ACTIVITY_ID', 'STMT_TEXT')
      
      NAME                 VALUE
      -------------------- ----------------------------------------
      UOW_ID               1
      ACTIVITY_ID          1
      STMT_TEXT            select * from syscat.tables
      
        3 record(s) selected.