Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    17

    Unanswered: monitor these end queues and alert on the queues growing above a threshold

    Hi
    I want to club all the script in once and make a generic replication script can someone help me please?
    The sql shown provides the basis for the monitors. I need to set threshold conditions for the below scripts
    Below are the script details. It will be grateful.
    i. Heart Beat test – wsdb210 database Testing for missing heart beat records (Uses UTC time!)
    1. Warning - No heat beat in the last hour
    2. Alert – No heart beat in the last 2 hours
    SELECT
    adabas_store_tmstmp_utc
    , DB2_STORE_TMSTMP_UTC + current timezone
    FROM CSW_REP.CONTROL_FILE
    where
    adabas_store_tmstmp_utc > (current_timestamp - current timezone - 2 hours)
    and record_cd = 'HB'
    order by adabas_store_tmstmp_utc desc with ur;

    ii. Event_table test – wsdb210 database testing if queue growing (this table empties very quickly)
    1. Warning > 100 rows
    2. Alert > 1000 rows

    SELECT count(*) FROM CSW_REP.event_table with ur;
    NB: If over 1000 rows then to avoid false alarms this alert may require retesting after say 2 minutes… Will only know from implementing and observing
    iii. Replica_oldest_event.sql – wsdb210 database testing how long an event is in the queue
    1. Warning > 5 minutes
    2. Alert > 10 minutes

    SELECT count(*)
    FROM CSW_REP.event_table
    where event_raised_tstamp < current timestamp - 5 minutes
    with ur;
    iv. Fineos_event_table – fin_pat database testing if queue growing (this table empties very quickly)
    1. Warning > 100 rows
    2. Alert > 1000 rows
    SELECT count(*) FROM FOAPP.event_table with ur;
    NB: If over 1000 rows then to avoid false alarms this alert may require retesting after say 2 minutes… Will only know from implementing and observing
    v. Fineos_oldest_event - fin_pat database testing how long an event is in the queue
    1. Warning > 5 minutes
    2. Alert > 10 minutes
    SELECT count(*)
    FROM foapp.event_table
    where event_raised_tstamp < current timestamp - 5 minutes
    with ur;
    vi. Fineos_failed_events fin_pat database
    select
    EVENTID
    ,EVENTTYPE
    ,KEYTYPE
    , EVENRAISEDTIM
    ,COMPLETEDTIME
    ,STATUS
    from foapp.twsevtprc
    where
    completedtime > current timestamp - 1 hour
    and
    STATUS = 'FAILED'
    order by COMPLETEDTIME desc

  2. #2
    Join Date
    Jul 2011
    Posts
    17
    Can some one please help?

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Not clear what you want here? Are you facing a problem/error , please post.



    ===
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    And asking again in only 8 minutes is rather inconsiderate. . .

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I had some ideas.
    But, I don't know this is the answer to the OP's requirements.

    For example:
    i. Heart Beat test – wsdb210 database Testing for missing heart beat records (Uses UTC time!)
    1. Warning - No heat beat in the last hour
    2. Alert – No heart beat in the last 2 hours
    Code:
    SELECT CASE
           WHEN MAX(adabas_store_tmstmp_utc)
                < current_timestamp - current timezone - 2 hours THEN
                'Alert!!!'
           WHEN MAX(adabas_store_tmstmp_utc)
                < current_timestamp - current timezone - 1 hours THEN
                'Warning!'
           ELSE '-- OK --'
           END  AS adabas
         , CASE
           WHEN MAX(db2_store_tmstmp_utc)
                < current_timestamp - current timezone - 2 hours THEN
                'Alert!!!'
           WHEN MAX(db2_store_tmstmp_utc)
                < current_timestamp - current timezone - 1 hours THEN
                'Warning!'
           ELSE '-- OK --'
           END  AS db2
     FROM  csw_rep.control_file cf
     WHERE record_cd = 'HB'
     WITH UR
    ;
    Last edited by tonkuma; 07-11-12 at 17:05.

Posting Permissions

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