This chapter describes how to use Oracle Enterprise Scheduler subrequests to process data in parallel, particularly in a dynamic context, where the number of parallel requests can vary.
This chapter includes the following sections:
Oracle Enterprise Scheduler subrequests are useful when you want to process data in parallel. A request submitted from a running job is called a subrequest. You can submit multiple subrequests from a single parent request. The customary method of parallel execution in Oracle Enterprise Scheduler is the job set concept but there might be cases where the number of parallel processes may not be fixed in number. For example, when you want to allocate one request per million rows and in the last week 9.7 million rows have accumulated to process. In this case, you would allocate ten requests as opposed to 5 for a week that accumulated 4.6 million rows.
Oracle Enterprise Scheduler supports subrequest functionality so that a given running request (Job Request) can submit a subrequest and wait for the completion of such a request before it continues.
Oracle Enterprise Scheduler supports subrequests by exposing an overloaded subrequest method submitRequest(). An application that submits a job request can invoke this API to submit a subrequest.
The following restrictions apply to subrequests:
A subrequest can be submitted only for onetime execution. No schedule can be specified. The subrequest is always treated as a "run now" request.
Ad hoc subrequests are not supported. A subrequest must be submitted for an existing JobDefinition object in the application.
Job sets are not supported for subrequests. A subrequest can only be submitted to a JobDefinition object. However, any running job (which may be part of a job set) can submit a subrequest.
These restrictions simplify the execution of subrequests and avoid any complications and delays in the execution of the submitting request itself.
There are different kinds of parent requests in Oracle Enterprise Scheduler, for the description in this chapter, a parent request refers to the request that is submitting a subrequest.
A subrequest follows the normal flow of a regular one-time request. However the processing of a subrequest starts only when the parent request pauses its execution. To indicate this, Oracle Enterprise Scheduler uses the PAUSED state. This state implies that the parent request is paused and waiting for the subrequest to finish.
After a parent request submits a subrequest, that parent must return control back to Oracle Enterprise Scheduler, in the manner appropriate for its job type, indicating that it has paused execution. Oracle Enterprise Scheduler then sets the parent state to PAUSED and starts processing the subrequest. After the subrequest finishes, Oracle Enterprise Scheduler places the parent request on the ready queue, where it remains PAUSED, until it is picked up by an appropriate request processor. The parent is then set to RUNNING state and re-run as a resumed request.
A subrequest can be submitted by calling the submitRequest API. The subrequest is set to WAIT state, but Oracle Enterprise Scheduler does not process the request while the parent request is running. A subrequest can be processed only after the parent request has paused.
There are two main ways a subrequest can be canceled, either by the user cancelling the subrequest directly or as a result of the parent request being canceled. For either method, the cancellation process of the subrequest is handled in the same manner as any other executable request. The difference lies in how Oracle Enterprise Scheduler treats the parent request after all pending subrequests have completed and reached a terminal state.
Oracle Enterprise Scheduler sets a subrequest that is in WAIT or READY state directly to CANCELLED. If a subrequest is currently running, then the subrequest is set to CANCELLING and Oracle Enterprise Scheduler then attempts to cancel the running executable in the manner appropriate for its job type. Usually, the subrequest ends up in CANCELLED state, but it may end in some other terminal state depending on the life cycle stage where the subrequest was at. The parent request remains in PAUSED or CANCELLING state until all subrequests have reached a terminal state.
If the user cancels a subrequest, then Oracle Enterprise Scheduler cancels that subrequest, as described previously. The parent request remains in PAUSED state until all subrequests are complete, at which point Oracle Enterprise Scheduler resumes or restarts the parent request. This enables the parent request to handle the completion of the subrequest, possibly as canceled, in an appropriate fashion. Cancellation of subrequests is thus not propagated upwards.
If the user cancels the parent request, Oracle Enterprise Scheduler sets the parent request to CANCELLING state, and then initiates a cancellation for all pending subrequests in the manner described previously. After all subrequests have completed, Oracle Enterprise Scheduler sets the parent request to CANCELLED, and the parent request does not resume. Cancellation of a parent request is propagated down to its subrequests.
A subrequest has the same life cycle as an ordinary request, and can be held when it is in WAIT or READY state. The parent request remains in PAUSED state while the subrequest is on hold.
Oracle Enterprise Scheduler allows requests to submit multiple subrequests. A running request may submit more than one subrequest. All of these subrequests are processed by Oracle Enterprise Scheduler when the parent request pauses and goes to PAUSED state.
In case of multiple such subrequests, the parent request is resumed only when all the subrequests finish.
Also it is possible to submit subrequests up to any depth. This creates nested subrequests. As such there are no restrictions on the depth of such subrequest submissions. This is kind of similar to stack push and pop operations.
A Java executable can submit subrequests using RuntimeService.submitRequest. After the subrequest has been submitted, the parent request must indicate to Oracle Enterprise Scheduler that it is pausing to allow the subrequest to be processed. This is accomplished by the parent throwing an ExecutionPausedExcpetion which causes the request to transition to PAUSED state. After the subrequests have completed, the parent request is run again as a resumed request. The RequestExecutionContext can be used to determine if the executable is being run as a resumed request.
When a job execution pauses after submitting a subrequest, Oracle Enterprise Scheduler regards its execution as complete, for all intents and purposes, as implementation-wise there is no notion of pausing an execution thread. Therefore, to resume such a paused job, Oracle Enterprise Scheduler must restart the job. In such cases, the job execution restarts from the beginning, whereas the desired behavior is to continue from the point at which execution was paused. This requires the job execution to store some kind of execution state that would represent the paused point. On resuming, the job can retrieve such a state and jump to the paused point to continue from there.
In general, it is incumbent on individual jobs to define an execution state that would allow it to resume in a deterministic way from each pause point throughout the business logic (jobs can have multiple pause points). In some cases, it can be as simple as storing the step number and jumping to that particular step on resuming, while in other cases it can be a huge data set that stores critical state for the business logic when it pauses. Oracle Enterprise Scheduler cannot provide a complete solution or framework to store the entire state.
Oracle Enterprise Scheduler provides a simplistic means for jobs to store their pause point in the form of a string that can be specified when the parent job pauses its execution. Upon resuming the parent job, the paused state value can be obtained by the parent to use as required.
Java jobs can specify a paused state string using a special ExecutionPausedException constructor. The state parameter represents the paused state string saved by Oracle Enterprise Scheduler when it sets the parent request to PAUSED state.
public ExecutionPausedException(String message, String state)
The resumed parent can retrieve the paused state value by calling getPausedState() on the RequestExecutionContext passed to the parent executable.
In case a single string value is not sufficient, the parent job can write any number of properties back into Oracle Enterprise Scheduler using setRequestParameter(), and retrieve those properties on resuming using getRequestParameter().
When a subrequest is submitted, Oracle Enterprise Scheduler sets the request state to WAIT but in a deferred mode so it is not dispatched until the parent request pauses.
The parent request of a Java job indicates that it is ready for subrequests to be processed by throwing ExecutionPausedException. When the Oracle Enterprise Scheduler receives such an exception, it sets the parent request state to PAUSED, publishes a system event message that the parent has paused, and then dispatches all waiting subrequests for that parent to the ready queue.
Subrequest execution follows the normal life cycle within Oracle Enterprise Scheduler. After all subrequests for a given parent request are finished, the parent request can be resumed.
When a parent is ready to resume, Oracle Enterprise Scheduler places the parent request in the ready queue. The parent state remains as PAUSED while it is waiting to be picked up. After Oracle Enterprise Scheduler picks up the parent request from the ready queue, the request state is set to RUNNING and the request executable called as a resumed request.
If a request is paused without submitting any subrequests, it is treated as if all subrequests had finished. That is, it is placed in the ready queue, at PAUSED state, to be picked up for processing as a resumed request.
The final state of a subrequest does not influence how Oracle Enterprise Scheduler handles the parent request or the final state of the parent request after that parent executable has completed. When the parent request resumes, the parent request job logic can retrieve information about the subrequest, using this data as needed to determine subsequent actions. The final state of the parent request is based entirely on the state in which the parent request completed: succeeded, error, warning or canceled.
In Oracle Enterprise Scheduler, each request has a RequestType attribute. That attribute indicates whether the request is a singleton, part of a job set, a recurring request, a subrequest, and so on.
A subrequest has a RequestType of SUB_REQUEST or UNVALIDATED_SUB_REQUEST. An UNVALIDATED_SUB_REQUEST represents a subrequest that was submitted using the Oracle Enterprise Scheduler PL/SQL interface but has not yet been validated. The RequestType of the parent request is either SINGLETON, RECUR_CHILD, JOBSET_STEP, or SUBREQUEST. All other request types represent requests that can never be the parent of a subrequest.
The parent request ID attribute for a subrequest is the request that submitted the subrequest.
In general, a request acquires incompatibility locks when the request transition from READY to RUNNING state. Those locks are not released until the request finishes and is set to a terminal state; for example, SUCCEEDED, ERROR, WARNING, CANCELLED.
Incompatibility locks acquired by a subrequest parent remain in effect even while a parent request is in a PAUSED state. Any requests that were blocked by a subrequest parent remain blocked while the subrequests execute and until the parent request is resumed and finishes.
Subrequests follow all the rules of incompatibility. A subrequest therefore may get blocked if any incompatible requests are currently running when Oracle Enterprise Scheduler is ready to execute the subrequest. During such time windows, the parent request remains in PAUSED state while the subrequest transitions to BLOCKED state.
This is an example of the Java class for a Java job type that submits subrequests. The procedure submits two subrequests, pausing between each one. Each subrequest uses the same JobDefinition but specifies a different value for the request parameter named SubRequestData. The oracle.as.scheduler.Executable.execute method of the parent request is called a total of three times for a given Oracle Enterprise Scheduler request and the following summaries the expected conditions and actions for each.
In the first call to execute method as a non-resumed request:
Entry condition:
RequestExecutionContext.isResumed() is false
RequestExecutionContext.getPausedState() is null
Method Action:
Submit a subrequest with request parameter value of 'MyData1'
Throw ExecutionPausedException with pausedState of 'MyPausedState1"
Oracle Enterprise Scheduler transitions the request to PAUSED state, execute the subrequest, and then resume the request after the subrequest has completed.
First call to execute method as resumed request:
Entry condition:
RequestExecutionContext.isResumed() is true
RequestExecutionContext.getPausedState() is 'MyPausedState1'
Method Action:
Submit a subrequest with request parameter value of 'MyData2'
Throw ExecutionPausedException with pausedState of 'MyPausedState2"
Oracle Enterprise Scheduler transitions the request to PAUSED state, execute the subrequest, and then resume the request after the subrequest has completed.
Second call to execute method as resumed request:
Entry condition:
RequestExecutionContext.isResumed() is true
RequestExecutionContext.getPausedState() is 'MyPausedState2'
Method Action:
Exit normally, no exception.
Oracle Enterprise Scheduler transitions the request to SUCCEEDED state.
Example 20-1 shows a Java procedure with a subrequest.
Example 20-1 Java Procedure with Subrequest
// constants for the pausedState values
private final static String PAUSED_STATE_1 = "MyPausedState1";
private final static String PAUSED_STATE_2 = "MyPausedState2";
public class SubRequestSubmittor implements Executable {
// method called by Oracle Enterprise Scheduler when the request is executed
public void execute( RequestExecutionContext execCtx,
RequestParameters props )
throws ExecutionWarningException,
ExecutionErrorException,
ExecutionPausedException,
ExecutionCancelledException {
long requestId = execCtx.getRequestId();
boolean isResumed = execCtx.isResumed();
String pausedState = execCtx.getPausedState();
if (!isResumed) {
// Method being called for first time, as non-resumed request.
// Submit first subrequest.
submitSubRequest(execCtx, "MyData1");
throw new ExecutionPausedException("first subrequest", PAUSED_STATE_1);
} else if (PAUSED_STATE_1.equals(pausedState)) {
// Method being called for a resumed request.
// Submit next subrequest.
submitSubRequest(execCtx, "MyData2");
throw new
ExecutionPausedException("second subrequest", PAUSED_STATE_2);
} else if (PAUSED_STATE_2.equals(pausedState)) {
// Method being called for a resumed request.
// All done, just return.
} else {
// Method being called for a resumed request.
// Unknown paused state (should never happen).
String msg = "Request " + requestId +
" was resumed with unexpected pause state " + pausedState;
throw new ExecutionErrorException(msg);
}
}
// Submit subrequest with request parameter having the given value.
private void submitSubRequest( RequestExecutionContext execCtx,
String paramValue )
throws ExecutionErrorException{
RuntimeService rs = null;
RuntimeServiceHandle rh = null;
try {
rs = getRuntimeService();
// Retrieve MetadataObjectId of the subrequest job definition
String jobDef = "MySubRequestJobDef";
MetadataObjectId jobDefId = getJobDefinition(jobDef);
// Set value for the request parameter used by subrequest.
RequestParameters rp = new RequestParameters();
rp.add("SubRequestData", paramValue);
// Submit the subrequest
rh = rs.open();
long subReqId = rs.submitRequest(rh, execCtx,
"subrequest submitter",
jobDefId, rp);
} catch (Exception e) {
String msg = "Error while submitting subrequest for request " +
ExecCtx.getRequestId();
throw new ExecutionErrorException(msg, e);
} finally {
if (null != rh) {
try {
rs.close(rh);
} catch (Exception e) {
String msg = "Error while submitting subrequest for request "
+ ExecCtx.getRequestId();
throw new ExecutionErrorException(msg, e);
}
}
}
}
// Get RuntimeService.
private RuntimeService getRuntime()
throws ExecutionErrorException {
// implementation not shown
}
// Retrieve MetadataObjectId for a given job definition name.
private MetadataObjectId getJobDefinition( String jobDef )
throws ExecutionErrorException {
// implementation not shown
}
}
The ESS_RUNTIME PL/SQL package is used by an SQL job request to submit a subrequest. It also contains support to determine if the request procedure is being executed as a resumed request and retrieve the paused state string.
For a Java request, the parent request submits a subrequest using a RuntimeService.submitRequest method and then throws ExecutionPausedException when it is ready to be paused to allow the subrequest to execute.
For a SQL request, ess_runtime.submit_subrequest is used to submit the subrequest. The parent request must call ess_runtime.mark_paused when it is ready for the subrequest to run, commit the transaction and return successfully, without raising an exception. The mark_paused method informs Oracle Enterprise Scheduler that, upon successful return from the parent request procedure, the parent request should be set to PAUSED and the subrequest allowed to execute. The mark_paused method supports an optional argument by which the paused state string can be specified.
It is important to note that subrequest is executed until the parent request has called mark_paused, commits, and returns normally, without raising an exception. If an exception is raised, Oracle Enterprise Scheduler does not set parent request to PAUSED state, but instead, it the parent state is set to ERROR or WARNING depending on the SQL error code. Furthermore, the subrequests are automatically CANCELLED and are not executed.
After the subrequest has finished, PL/SQL procedure for the parent request is re-executed again as resumed request, similar to what occurs for a Java Executable.
For a Java executable, the RequestExecutionContext indicates if the request is being resumed and has the paused state string specified using the ExecutionPausedException thrown when the parent request paused.
For an SQL request, ess_runtime.is_resumed indicates whether the request procedure is being executed for a resumed request. The method ess_runtime.get_paused_state returns the paused state string specified using the ess_runtime.mark_paused procedure when the request was paused.
This is an example of the PL/SQL stored procedure for a SQL job type that submits subrequests using the ESS_RUNTIME package. The procedure submits two subrequests, pausing between each one. Each subrequest uses the same JobDefinition but specifies a different value for the request parameter named SubRequestData. The PL/SQL stored procedure would be called a total of three times for a given Oracle Enterprise Scheduler request and the following summaries the expected conditions and actions for each.
First call to procedure as non-resumed request:
Entry condition:
ess_runtime.is_resumed is false
ess_runtime.get_paused_state is null
Procedure Action:
Submit a subrequest with request parameter value of 'MyData1'
Mark request as paused using paused state of 'MyPausedState1'
Exit normally, no exception
Oracle Enterprise Scheduler transitions the request to PAUSED state, execute the subrequest, and then resume the request after the subrequest has completed.
First call to procedure as resumed request:
Entry condition:
ess_runtime.is_resumed is true
ess_runtime.get_paused_state is 'MyPausedState1'
Procedure Action:
Submit a subrequest with request parameter value of 'MyData2'
Mark request as paused using paused state of 'MyPausedState2'
Exit normally, no exception
Oracle Enterprise Scheduler transitions the request to PAUSED state, execute the subrequest, and then resume the request after the subrequest has completed.
Second call to procedure as resumed request:
Entry condition:
ess_runtime.is_resumed is true
ess_runtime.get_paused_state is 'MyPausedState2'
Procedure Action:
Exit normally, no exception.
Oracle Enterprise Scheduler transitions the request to SUCCEEDED state.
Example 20-2 shows a PL/SQL procedure with a subrequest.
Example 20-2 PL/SQL Procedure with Subrequest
-- Application stored procedure.
procedure plsql_subreq_sample
( request_handle in varchar2 )
is
v_reqid number;
v_is_resumed boolean;
v_paused_str varchar2(100);
v_paused_state1 varchar2(100) := 'MyPausedState1';
v_paused_state2 varchar2(100) := 'MyPausedState2';
begin
-- Request id of this subrequest parent.
v_reqid := ess_runtime.get_request_id(request_handle);
-- Check is this is a resumed request
v_is_resumed := ess_runtime.is_resumed(request_handle);
if (v_is_resumed = false) then
-- This parent request is being run for the first time.
-- Submit a subrequest and exit as success to allow ESS to pause this parent.
submit_subrequest(request_handle, v_paused_state1, 'MyData1');
else
-- Parent is being run as resumed request.
v_paused_str := ess_runtime.get_paused_state(v_reqid);
if (v_paused_state1 = v_paused_str) then
-- Request being resumed after first pause.
-- Submit a subrequest and exit as success to allow ESS to pause this parent.
submit_subrequest(request_handle, v_paused_state2, 'MyData2');
elsif (v_paused_state2 = v_paused_str) then
-- Request being resumed after second pause.
-- Parent is done. Just return as success.
null;
end if;
end if;
end;
-- Helper procedure to submit subrequest and call mark_paused.
-- Caller should exit normally, without an error, to allow
-- ESS mid-tier to pause the parent and execute the sub-request.
procedure submit_subrequest
( request_handle in varchar2,
paused_state_value in varchar2,
reqprop_value in varchar2 )
is
v_sub_reqid number := null;
v_req_props ess_runtime.request_prop_table_t := null;
v_idx pls_integer := 0;
begin
v_req_props := ess_runtime.request_prop_table_t();
v_idx := 0;
v_idx := v_idx + 1;
v_req_props.extend;
v_req_props(v_idx).prop_name := 'SubRequestData';
v_req_props(v_idx).prop_datatype := ess_runtime.STRING_DATATYPE;
v_req_props(v_idx).prop_value := reqprop_value;
-- Must commit or rollback work done in this block.
begin
-- Submit the subrequest.
v_sub_reqid := ess_runtime.submit_subrequest(
request_handle => request_handle,
definition_name => 'sampleJob',
definition_package => 'samplePkg',
props => v_req_props);
-- Indicate that the parent request should pause.
-- The actual state change does not occur until ESS is notififed
-- that this run of the parent executable finished.
ess_runtime.mark_paused(request_handle, paused_state_value);
-- This procedure is responsible for the txn commit.
commit;
exception
-- Rollback txn on failure and raise an error.
rollback;
raise_application_error(-20000,
'Error submitting sub-request. '||
'SQLCODE='||SQLCODE || ', SQLERRM=['||SQLERRM||']',
true);
end;
end;
end;