Use Case to Schedule and Run a Build Workflow

This use case demonstrates how to run the WF_RUN_API in a lineage of a workflow, poll the status and print node failure from the event log.

You can run the WF_RUN_API in a lineage of a workflow, using the run modes RERUN_NODE_ONLY or RERUN_NODE_PARENTS.

The methods to run the WF_RUN_API using the two run modes generate the same result, where all the four nodes in the lineage are run. The methods are:

• Select all nodes in the lineage and use the RERUN_NODE_ONLY run mode.

• Select the MODEL_COEFFICIENTS node and use the RERUN_NODE_PARENTS run mode.

The use case, as demonstrated in the example, does the following:

• Schedules the workflow to run monthly on the last day of the month (BYMONTHDAY=-1) starting at mid night from 12/31/2014 to 12/31/2015 in EST zone.

• Executes the WF_RUN API. In this use case, the API WF_RUN with Project Name, Workflow Name Node Name and Time Interval schedules the workflow to run.

• Polls the status of the workflow from the ODMR_USER_PROJECT_WORKFLOW view to determine whether the workflow run is complete.

• Prints out any node failure from the event log along with error message.

Example 8-2 Schedule and Run a Workflow, Poll Status and Print Node Failures

CONNECT DMUSER/DMUSER
SET SERVEROUTPUT ON 
DECLARE
   v_jobId VARCHAR2(30) := NULL;
   v_status VARCHAR2(30) := NULL;
   v_projectName VARCHAR2(30) := 'Project';
   v_workflow_name VARCHAR2(30) := 'build_workflow';
   v_node VARCHAR2(30) := 'MODEL_COEFFCIENTS';
   v_run_mode VARCHAR2(30) := ODMRSYS.ODMR_WORKFLOW.RERUN_NODE_PARENTS;
   v_failure NUMBER := 0;
   v_nodes ODMRSYS.ODMR_OBJECT_NAMES := ODMRSYS.ODMR_OBJECT_NAMES();
BEGIN
   v_nodes.extend();
   v_nodes(v_nodes.count) := v_node;
   v_jobId := ODMRSYS.ODMR_WORKFLOW.WF_RUN(p_project_name => v_projectName,
           p_workflow_name => v_workflow_name,
           p_node_names => v_nodes,
           p_run_mode => v_run_mode,
           p_start_date => '31-DEC-14 12.00.00 AM AMERICA/NEW_YORK',
           p_repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=-1',
           p_end_date => '31-DEC-15 12.00.00 AM AMERICA/NEW_YORK');
   DBMS_OUTPUT.PUT_LINE('Job: '||v_jobId);
   -- wait for workflow to run to completion
LOOP
   SELECT STATUS INTO v_status FROM ODMR_USER_PROJECT_WORKFLOW
   WHERE WORKFLOW_NAME = v_workflow_name;
   IF (v_status IN ('SCHEDULED', 'ACTIVE')) THEN
      DBMS_LOCK.SLEEP(10); -- wait for 10 secs
   ELSE
      EXIT; -- workflow run completes
   END IF;
END LOOP;
-- print all failed nodes from the event log
FOR wf_log IN (
   SELECT node_id, node_name, subnode_id, subnode_name, log_message, log_message_ details
   FROM ODMR_USER_WORKFLOW_LOG
   WHERE job_name=v_jobId and log_type='ERR' and log_message IS NOT NULL)
LOOP
   DBMS_OUTPUT.PUT_LINE('Node Id: '||wf_log.node_id||', '||'Node Name: '||wf_log.node_name);
   IF (wf_log.subnode_id IS NOT NULL) THEN
      DBMS_OUTPUT.PUT_LINE( 
         'Subnode Id: '||wf_log.subnode_id||', '||'Subnode Name: '||wf_log.subnode_name);
   END IF;
   DBMS_OUTPUT.PUT_LINE('Message: '||wf_log.log_message);
   v_failure := v_failure + 1;
 END LOOP;
IF (v_failure = 0) THEN
   DBMS_OUTPUT.PUT_LINE('Workflow Status: SUCCEEDED');
 ELSE
   DBMS_OUTPUT.PUT_LINE('Workflow Status: FAILURE');
 END IF;
EXCEPTION WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('Error: '||SUBSTR(DBMS_UTILITY.FORMAT_ERROR_STACK(), 1, 4000));
END;

• Querying the MODEL_COEFFICIENT Table
  You can fetch data from the MODEL_COEFFICIENTS table by running a query.
• Querying Named Objects
  After the workflow run completes successfully, you can query all named objects generated by the workflow, such as table or view in the Create Table node, models in the Build nodes, and so on.
• Querying Test Results
  You can query test results for Confusion Matrix and Test Metrics from ODMR_USER_WF_CLAS_TEST_RESULTS and ODMR_USER_WF_REGR_TEST_RESULTS repository views.