Data collection issues

1. List of errors upon data collection

USE CASE:

  • Retrieve the list of errors upon data collection
  • This basic query will return all elements with errors
SELECT * 
FROM (
    SELECT 'trigger' AS caller,  element_id, segment_value_id, start_time, success_ind, error_message
    FROM update_trigger_event_run_log_detail
    WHERE success_ind = 'N'

    UNION ALL

    SELECT 'editor' AS caller,  element_id, segment_value_id, start_time, success_ind, error_message
    FROM editor_data_collection_detail
    WHERE success_ind = 'N'
) it
WHERE error_message != 'No rows are returned'
2. Elements with data collection (>= 30)

USE CASE:

  • Retrieve the list of Elements with data collection exceeding 30 minutes
  • This basic query will return all elements with long-running data collection
SELECT * 
FROM (
    SELECT 'trigger' AS caller,  element_id, segment_value_id, TIMESTAMPDIFF(MINUTE, start_time, finish_time) AS _mins
    FROM update_trigger_event_run_log_detail
    WHERE success_ind = 'Y'
        AND TIMESTAMPDIFF(MINUTE, start_time, finish_time) >= 30
    UNION ALL
    SELECT 'editor' AS caller,  element_id, segment_value_id, TIMESTAMPDIFF(MINUTE, start_time, finish_time) AS _mins
    FROM editor_data_collection_detail
    WHERE success_ind = 'Y'
        AND TIMESTAMPDIFF(MINUTE, start_time, finish_time) >= 30
) it
ORDER BY _mins DESC
3. List of overdue Trigger runs

USE CASE:

  • Retrieve the list of Triggers with overdue runs
  • This basic query will return a list of overdue Trigger ids, the start time and reasons for overdue runs
SELECT ute.update_trigger_event_id _id, ute.name, rl.run_id, rl.run_start_time, IF (rl.event_aborted_ind = 'Y', 'Aborted', 'Timed out') _reason
FROM update_trigger_event ute
INNER JOIN update_trigger_event_run_log rl ON rl.update_trigger_event_id = ute.update_trigger_event_id
WHERE rl.run_timed_out_ind = 'Y' OR rl.event_aborted_ind = 'Y'
ORDER BY rl.run_start_time DESC