Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    94

    Unanswered: Threshold Violations Monitor not writing records to table!!

    I cant get the threshold violations to report a record to the thresholds violation monitor table .... killing me!!!

    I have ensured -

    1. that my query thats violating the threshold is mapping to a service class that has collect activity data ON
    2. the threshold is defined to this same service class
    3. the event monitors for threshold violations and activities are both enabled



    Please help! This is DB2 LUW 9.5 FP3 on a DPF platform. Thanks!

    Here's the DDL -

    CREATE SERVICE CLASS SC_SUB_AOI_USR_R
    UNDER SC_SUP_AOI_USR
    AGENT PRIORITY 7
    PREFETCH PRIORITY HIGH
    COLLECT ACTIVITY DATA ON ALL DATABASE PARTITIONS WITH DETAILS;

    CREATE WORKLOAD WL_AOI_USR_R
    SESSION_USER ( 'USWH9XE' )
    SERVICE CLASS SC_SUB_AOI_USR_R UNDER SC_SUP_AOI_USR
    POSITION AFTER WL_AOI_USR_U
    ;

    CREATE EVENT MONITOR MON_EVT_ACT
    FOR ACTIVITIES
    WRITE TO TABLE
    ACTIVITY (IN FDWTS2_16K),
    ACTIVITYSTMT (IN FDWTS2_16K),
    ACTIVITYVALS (IN FDWTS2_16K),
    CONTROL (IN FDWTS2_16K);

    SET EVENT MONITOR MON_EVT_THR STATE 1;

    CREATE THRESHOLD THR_AOI_USR_U_COST
    FOR SERVICE CLASS SC_SUB_AOI_USR_U UNDER SC_SUP_AOI_USR ACTIVITIES
    ENFORCEMENT DATABASE
    WHEN ESTIMATEDSQLCOST > 1000
    COLLECT ACTIVITY DATA WITH DETAILS
    CONTINUE;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think your service class names don't match.

    CREATE SERVICE CLASS SC_SUB_AOI_USR_R

    CREATE THRESHOLD THR_AOI_USR_U_COST
    FOR SERVICE CLASS SC_SUB_AOI_USR_U
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2010
    Posts
    94
    Sorry, I copied the wrong threshold definition. Here's the one I am testing -

    CREATE THRESHOLD THR_AOI_USR_R_ROWS
    FOR SERVICE CLASS SC_SUB_AOI_USR_R UNDER SC_SUP_AOI_USR ACTIVITIES
    ENFORCEMENT DATABASE
    WHEN SQLROWSRETURNED > 1000
    COLLECT ACTIVITY DATA WITH DETAILS
    STOP EXECUTION;

    This threshold stops my query if it tries to return more than 1000 rows - however, I dont see the data in THRESHOLDS event monitor for the violation, let alone the activity details requested by the 'COLLECT ACTIVITY DATA' clause on the threshold. I verified there is one on the service class too, and the activities monitor is active.

    Any ideas??

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The threshold will collect activity data after the activity finishes, which may not happen if you stop it...
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Oct 2010
    Posts
    94
    ok - how about violations record? doesnt it write about violation itself to the monitor?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •