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:
Section 15.4, "Creating a Java Procedure that Submits a Subrequest"
Section 15.5, "Creating a PL/SQL Procedure that Submits a Subrequest"
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.
Once 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. Once 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.
Example 15-1 is a sample PL/SQL job that submits five subrequests. The subrequests are submitted one at a time. Each time a subrequest is submitted, the parent exits to a paused state, so that it does not consume any resources while waiting for the child request to complete. When the child completes the parent is restarted.
Example 15-1 PL/SQL Procedure Subrequest
procedure fusion_plsql_subreq_sample(
errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2,
no_requests in varchar2 default '5',
) is
req_cnt number := 0;
sub_reqid number;
submitted_requests varchar2(100);
request_prop_table_t jobProp;
begin
-- Write log file content using FND_FILE API
FND_FILE.PUT_LINE(FND_FILE.LOG, "About to run the sample program with sub-request functionality");
-- Requesting the PAUSED_STATE property set by job identifies request as
-- having started for the first time or restarting after being paused.
if ( ess_runtime.get_reqprop_varchar(fnd_job.job_request_id, 'PAUSED_STATE') ) is null )
-- first time start
then
-- Implement the business logic of the job here.
FND_FILE.PUT_LINE(FND_FILE.OUT, " About to submit sub-requests : " || no_requests);
-- Loop through all the sub-requests.
for req_cnt 1..no_requests loop
-- Retrieve the request handle and submit the subrequest.
sub_reqid := ess_runtime.submit_subrequest(request_handle => fnd_job.request_handle,
definition_name => 'sampleJob',
definition_package => 'samplePkg',
props => jobProp);
submitted_requests := sub_reqid || ',';
end loop;
-- Pause the parent request.
ess_runtime.update_reqprop_varchar(fnd_job.request_id, 'STATE', ess_job.PAUSED_STATE);
-- Update the parent request with the state of the sub-request, enabling
-- the job to retrieve the status during restart.
ess_runtime.update_reqprop_int(fnd_job.request_id, 'PAUSED_STATE', submitted_requests);
else
-- Restart the request, retrieve job completion status and return the
-- status to Oracle Enterprise Scheduler.
errbuf := fnd_message.get("FND", "COMPLETED NORMAL");
retcode := 0;
end if;
end;
A subrequest can be submitted by calling the submitRequest API. The subrequest is set to WAIT state, but Oracle Enterprise Scheduler will not process the request while the parent request is running. A subrequest can be processed only once the parent request has paused.
There are two main ways a subrequest can be cancelled, either by the user cancelling the subrequest directly or as a result of the parent request being cancelled. 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 once 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 cancelled, 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. Once 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.
The delete operation will not be allowed on a subrequest, since it might lead to ambiguous data where the information about the subrequest will get lost. A subrequest is automatically purged when its parent request is purged.
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 will be 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.Once the subrequests have completed, the parent request is runs 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 will not be 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. Once 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. Once Oracle Enterprise Scheduler picks up the parent request from the ready queue, the request state will be set to RUNNING and the request executable called as a resumed request.
If a request is paused without submitting any subrequests, it will be treated as if all subrequests had finished. That is, it will be 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 once 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 cancelled.
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 via 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() will be false
RequestExecutionContext.getPausedState() will be null
Method Action:
Submit a subrequest with request parameter value of 'MyData1'
Throw ExecutionPausedException with pausedState of 'MyPausedState1"
Oracle Enterprise Scheduler will transition the request to PAUSED state, execute the subrequest, and then resume the request once the subrequest has completed.
First call to execute method as resumed request:
Entry condition:
RequestExecutionContext.isResumed() will be true
RequestExecutionContext.getPausedState() will be 'MyPausedState1'
Method Action:
Submit a subrequest with request parameter value of 'MyData2'
Throw ExecutionPausedException with pausedState of 'MyPausedState2"
Oracle Enterprise Scheduler will transition the request to PAUSED state, execute the subrequest, and then resume the request once the subrequest has completed.
Second call to execute method as resumed request:
Entry condition:
RequestExecutionContext.isResumed() will be true
RequestExecutionContext.getPausedState() will be 'MyPausedState2'
Method Action:
Exit normally, no exception.
Oracle Enterprise Scheduler will transition the request to SUCCEEDED state.
Example 15-2 shows a Java procedure with a subrequest.
Example 15-2 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 will not be 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 will not set parent request to PAUSED state, but instead, it the parent state will be set to ERROR or WARNING depending on the SQL error code. Furthermore, the subrequests will be automatically CANCELLED and will not be executed.
Once the subrequest has finished, PL/SQL procedure for the parent request will be 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 via 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 via 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 will be false
ess_runtime.get_paused_state will be 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 will transition the request to PAUSED state, execute the subrequest, and then resume the request once the subrequest has completed.
First call to procedure as resumed request:
Entry condition:
ess_runtime.is_resumed will be true
ess_runtime.get_paused_state will be '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 will transition the request to PAUSED state, execute the subrequest, and then resume the request once the subrequest has completed.
Second call to procedure as resumed request:
Entry condition:
ess_runtime.is_resumed will be true
ess_runtime.get_paused_state will be 'MyPausedState2'
Procedure Action:
Exit normally, no exception.
Oracle Enterprise Scheduler will transition the request to SUCCEEDED state.
Example 15-3 shows a PL/SQL procedure with a subrequest.
Example 15-3 PL/SQL Procedure with Subrequest
procedure fusion_plsql_subreq_sample(
errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2,
no_requests in varchar2 default '5',
) is
req_cnt number := 0;
sub_reqid number;
submitted_requests varchar2(100);
request_prop_table_t jobProp;
begin
-- Write log file content using FND_FILE API
FND_FILE.PUT_LINE(FND_FILE.LOG, "About to run the sample program with
sub-request functionality");
-- Requesting the PAUSED_STATE property set by job identifies request as
-- having started for the first time or restarting after being paused.
if ( ess_runtime.get_reqprop_varchar(fnd_job.job_request_id,
'PAUSED_STATE') ) is null )
-- first time start
then
-- Implement the business logic of the job here.
FND_FILE.PUT_LINE(FND_FILE.OUT, " About to submit sub-requests : " ||
no_requests);
-- Loop through all the sub-requests.
for req_cnt 1..no_requests loop
-- Retrieve the request handle and submit the subrequest.
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 := 'MyData1';
ess_runtime.set_submit_args(v_req_props, 'MyData1', 'MyData12',
'1998-11-29')
sub_reqid := ess_runtime.submit_subrequest(request_handle =>
fnd_job.request_handle,
definition_name => 'sampleJob',
definition_package => 'samplePkg',
props => jobProp);
submitted_requests := sub_reqid || ',';
end loop;
-- Pause the parent request.
ess_runtime.update_reqprop_varchar(fnd_job.request_id, 'STATE',
ess_job.PAUSED_STATE);
-- Update the parent request with the state of the sub-request, enabling
-- the job to retrieve the status during restart.
ess_runtime.update_reqprop_int(fnd_job.request_id, 'PAUSED_STATE',
submitted_requests);
else
-- Restart the request, retrieve job completion status and return the
-- status to Oracle Enterprise Scheduler.
errbuf := fnd_message.get("FND", "COMPLETED NORMAL");
retcode := 0;
end if;
end;