Populating a database with messages via msg-consume2db
(in case of ATLAS Job Monitoring)


  • Сommon schema of an interaction between msg-consume2oracle and db :

broker_db_interaction.jpg

Schema description:
Msg-consume2db performs inserting of a message into a view (some_topic_view at the schema above) in the database which is described in the consumer's configuration file. Then trigger "instead of insert" on that view inserts the message into a table which is supposed to store "raw" messages. This is some_topic_msg table at the schema.Some validation of the data is being performed before the insert action. "After insert" trigger on that table populates different tables with the information sent with the message.
The data populating logic is located in different triggers. There are several "groups" of them.

  • Triggers for managing messages coming from consume2db. They perform data validation and insert the messages into special tables like JOB_STATUS_MSG, TASK_META_MSG, etc.

Here is the list of the triggers.

Trigger name Trigger Type Action
ins_job_meta_msg instead of insert on v_job_meta (view) Inserts messages coming from jobMeta topic into JOB_META_MSG table
ins_job_proc_attrs_msg instead of insert on v_job_proc_attributes (view) Inserts messages coming from jobProcessingAttributes topic into JOB_PROC_ATTRIBUTES_MSG table
ins_job_status_msg instead of insert on v_job_status (view) Inserts messages coming from jobStatus topic into JOB_STATUS_MSG table
ins_task_meta_msg instead of insert on v_task_meta (view) Inserts messages coming from taskMeta topic into TASK_META_MSG table

  • Data distributing triggers. The main goal of them is to populate db tables with the data sent.

Trigger name Trigger Type Action
ins_job_meta_info after insert on JOB_META_MSG Distributes the data across db tables like INFO_SOURCE, SE, INPUT_COLLECTION, OUTPUT_COLLECTION and JOB
ins_job_proc_attrs_info after insert on JOB_PROC_ATTRIBUTES_MSG Inserts EventRange, NEvProc, NFilesProc, WrapCPU, WrapWC to JOB table
ins_to_job_and_task after insert on JOB_STATUS_MSG Inserts new task, user, job status info, etc. into the appropriate tables (TASK, JOB, USERS, ... ). Actually if the record for a job already exists in JOB table, then trigger makes an update of the corresponding record.
ins_task_meta_info after insert on TASK_META_MSG Inserts taskMeta attributes to TASK, APPLICATION, SUBMISSION_TOOL, etc.

  • Additional triggers which correct the values sent to be consistent with the business logic of Dashboard Job Monitoring.

Trigger name Trigger Type Action
created_ts_update_tr before update of "CreatedTimeStamp" on JOB Prevents update of the CreatedTimeStamp column in JOB table. The rule is "Do not update the column with the later timestamp if it already has the timestamp not equal 01-JAN-70"
dboard_grid_end_id_update before update of "DboardGridEndId" on JOB Manages DboardGridEndId column update. If DboardGridEndId of the updated job is not unknown and we don't have new attempt for the current job then we do not update DboardGridEndId, GridEndStatusId, GridEndStatusReasonId, GridEndStatusTimeStamp, GridFinishedTimeStamp.
dboard_status_enter_ts_update before update of "DboardStatusEnterTimeStamp" on JOB Update DboardStatusEnterTimeStamp if DboardStatusId is not changed while we get new attempt of execution of a job.
finished_ts_update before update of "FinishedTimeStamp" on JOB This trigger prevents update of the FinishedTimeStamp column. The rule is "Do not update the column with the later timestamp if it already has the timestamp not equal 01-JAN-70"
first_info_ts_update_tr before update of "DboardFirstInfoTimeStamp" on JOB This trigger manages update of the DboardFirstInfoTimeStamp column. The rule is "Do not update the column with the later timestamp if it already has the timestamp not equal 01-JAN-70"
grid_status_update before update of "GridStatusId" on JOB The trigger includes the main logic. It makes a decision whether we should update JOB columns with the new info coming from a topic or not. For example, if we have a new attempt of processing of a job then we should update all the columns of its record in JOB table. But if we get a stale or inconsistent info then we should leave all the values as they are.
job_exit_code_update before update of "JobExecExitCode" on JOB Manages update of JobExecExitCode. The logic is "if it's not a new attempt and the old JobExecExitCode was already defined then we leave "JobExecExitCode", "JobExecExitTimeStamp", "DboardJobEndId" columns as they are."
latest_ts_update_tr before update of "DboardLatestInfoTimeStamp" on JOB Manages update of DboardLatestInfoTimeStamp column. The rule is "Do not update boardLatestInfoTimeStamp with the later ts if it's already defined and not equal 01-JAN-70"
scheduled_ts_update before update of "ScheduledTimeStamp" on JOB Manages update of ScheduledTimeStamp column. The logic is "Do not update ScheduledTimeStamp if it's not earlier then StartedRunningTS, not equal 01-JAN-70 and job is not in pending or unknown state"
started_run_ts_update before update of "StartedRunningTimeStamp" on JOB It prevents updating StartedRunningTimeStamp in case it's not equal 01-JAN-70 and it's not a new attempt of the job
submitted_ts_update_tr before update of "SubmittedTimeStamp" on JOB Manages update of SubmittedTimeStamp column. "Don't update SubmittedTimeStamp with the later timestamp if the old value is not equal 01-JAN-70".
task_created_ts_update before update of "TaskCreatedTimeStamp" on TASK Prevents update of the following column with a later timestamp.
wn_host_update before update of "WNHostName" on JOB Prevents update of WNHostName column in case when old WNHostName is defined and job is already in RUNNING or COMPLETED state and new StartedRunningTimeStamp is not later then old StartedRunningTimeStamp.


All the sources can be found in Dashboard SVN in arda.dashboard.dao-oracle-job module. Please, read README carefully to learn the structure of the scripts and then find easily what you need.

-- IrinaSidorova - 22-Jun-2010

Topic attachments
I Attachment History Action Size Date Who Comment
JPEGjpg broker_db_interaction.jpg r1 manage 53.0 K 2010-06-23 - 15:49 UnknownUser Сommon schema of interaction between msg-consume2oracle and db
Edit | Attach | Watch | Print version | History: r4 < r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r4 - 2010-06-25 - unknown
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    ArdaGrid All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2022 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback