Creating and Managing Job Chains

Chain Tasks and Their Procedures

Table 29-6 illustrates common tasks involving chains and the procedures associated with them.

Creating Chains

You create a chain by using the CREATE_CHAIN procedure. You must ensure that you have the required privileges first. See "Setting Chain Privileges" for details.

After creating the chain object with CREATE_CHAIN, you define chain steps and chain rules separately.

The following is an example of creating a chain:

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name          => 'my_chain1',
   rule_set_name       => NULL,
   evaluation_interval => NULL,
   comments            => 'My first chain');
END;
/

The rule_set_name and evaluation_interval arguments are typically left NULL. evaluation_interval can define a repeating interval at which chain rules get evaluated. rule_set_name refers to a rule set as defined within Oracle Streams.

Defining Chain Steps

After creating a chain object, you define one or more chain steps. Each step can point to one of the following:

• A Scheduler program object (program)

• Another chain (a nested chain)

• An event schedule, inline event, or file watcher

You define a step that points to a program or nested chain by using the DEFINE_CHAIN_STEP procedure. An example is the following, which adds two steps to my_chain1:

BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name      =>  'my_chain1',
   step_name       =>  'my_step1',
   program_name    =>  'my_program1');
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name      =>  'my_chain1',
   step_name       =>  'my_step2',
   program_name    =>  'my_chain2');
END;
/

The named program or chain does not have to exist when defining the step. However it must exist and be enabled when the chain runs, otherwise an error is generated.

You define a step that waits for an event to occur by using the DEFINE_CHAIN_EVENT_STEP procedure. Procedure arguments can point to an event schedule, can include an inline queue specification and event condition, or can include a file watcher name. This example creates a third chain step that waits for the event specified in the named event schedule:

BEGIN
  DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
   chain_name           =>  'my_chain1',
   step_name            =>  'my_step3',
   event_schedule_name  =>  'my_event_schedule');
END;
/

An event step does not wait for its event until the step is started.

Steps That Run Local External Executables

After defining a step that runs a local external executable, you must use the ALTER_CHAIN procedure to assign a credential to the step, as shown in the following example:

BEGIN
  DBMS_SCHEDULER.ALTER_CHAIN('chain1','step1','credential_name','MY_CREDENTIAL');
END;
/

Steps That Run on Remote Destinations

After defining a step that is to run an external executable on a remote host or a database program unit on a remote database, you must use the ALTER_CHAIN procedure to assign both a credential and a destination to the step, as shown in the following example:

BEGIN
 DBMS_SCHEDULER.ALTER_CHAIN('chain1','step2','credential_name','DW_CREDENTIAL');
 DBMS_SCHEDULER.ALTER_CHAIN('chain1','step2','destination_name','DBHOST1_ORCLDW');
END;
/

Making Steps Restartable

After a database recovery, by default steps that were running are marked as STOPPED and the chain continues. You can specify the chain steps to restart automatically after a database recovery by using ALTER_CHAIN to set the restart_on_recovery attribute to TRUE for those steps.

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DEFINE_CHAIN_STEP, DEFINE_CHAIN_EVENT_STEP, and ALTER_CHAIN procedures.

Adding Rules to a Chain

You add a rule to a chain with the DEFINE_CHAIN_RULE procedure. You call this procedure once for each rule that you want to add to the chain.

Chain rules define when steps run, and define dependencies between steps. Each rule has a condition and an action. Whenever rules are evaluated, if a rule's condition evaluates to TRUE, its action is performed. The condition can contain Scheduler chain condition syntax or any syntax that is valid in a SQL WHERE clause. The syntax can include references to attributes of any chain step, including step completion status. A typical action is to run a specified step or to run a list of steps.

All chain rules work together to define the overall action of the chain. When the chain job starts and at the end of each step, all rules are evaluated to see what action or actions occur next. If more than one rule has a TRUE condition, multiple actions can occur. You can cause rules to also be evaluated at regular intervals by setting the evaluation_interval attribute of a chain.

Conditions are usually based on the outcome of one or more previous steps. For example, you might want one step to run if the two previous steps succeeded, and another to run if either of the two previous steps failed.

Scheduler chain condition syntax takes one of the following two forms:

stepname [NOT] {SUCCEEDED|FAILED|STOPPED|COMPLETED}
stepname ERROR_CODE {comparision_operator|[NOT] IN} {integer|list_of_integers}

You can combine conditions with boolean operators AND, OR, and NOT() to create conditional expressions. You can employ parentheses in your expressions to determine order of evaluation.

ERROR_CODE can be set with the RAISE_APPLICATION_ERROR PL/SQL statement within the program assigned to the step. Although the error codes that your program sets in this way are negative numbers, when testing ERROR_CODE in a chain rule, you test for positive numbers. For example, if your program contains the following statement:

RAISE_APPLICATION_ERROR(-20100, errmsg);

your chain rule condition must be the following:

stepname ERROR_CODE=20100

Step Attributes

The following is a list of step attributes that you can include in conditions when using SQL WHERE clause syntax:

The completed attribute is boolean and is TRUE when the state attribute is either SUCCEEDED, FAILED, or STOPPED.

Table 29-7 shows the possible values for the state attribute. These values are visible in the STATE column of the *_SCHEDULER_RUNNING_CHAINS views.

See the DEFINE_CHAIN_RULE procedure in Oracle Database PL/SQL Packages and Types Reference for rules and examples for SQL WHERE clause syntax.

Condition Examples Using Scheduler Chain Condition Syntax

These examples use Scheduler chain condition syntax.

Steps started by rules containing the following condition are started when the step named form_validation_step is completed (SUCCEEDED, FAILED, or STOPPED).

form_validation_step COMPLETED

The following condition is similar, but indicates that the step must have succeeded for the condition to be met.

form_validation_step SUCCEEDED

The next condition tests for an error. It is TRUE if the step form_validation_step failed with any error code other than 20001.

form_validation_step FAILED AND form_validation_step ERROR_CODE != 20001

See the DEFINE_CHAIN_RULE procedure in Oracle Database PL/SQL Packages and Types Reference for more examples.

Condition Examples Using SQL WHERE Syntax

':step1.state=''SUCCEEDED'''

Starting the Chain

At least one rule must have a condition that always evaluates to TRUE so that the chain can start when the chain job starts. The easiest way to accomplish this is to just set the condition to 'TRUE' if you are using Schedule chain condition syntax, or '1=1' if you are using SQL syntax.

Ending the Chain

At least one chain rule must contain an action of 'END'. A chain job does not complete until one of the rules containing the END action evaluates to TRUE. Several different rules with different END actions are common, some with error codes, and some without.

If a chain has no more running steps or it is not waiting for an event to occur, and no rules containing the END action evaluate to TRUE (or there are no rules with the END action), the chain job enters the CHAIN_STALLED state. See "Handling Stalled Chains" for more information.

Example of Defining Rules

The following example defines a rule that starts the chain at step step1 and a rule that starts step step2 when step1 completes. rule_name and comments are optional and default to NULL. If you do use rule_name, you can later redefine that rule with another call to DEFINE_CHAIN_RULE. The new definition overwrites the previous one.

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   condition    =>   'TRUE',
   action       =>   'START step1',
   rule_name    =>   'my_rule1',
   comments     =>   'start the chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   condition    =>   'step1 completed',
   action       =>   'START step2',
   rule_name    =>   'my_rule2');
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name          => 'my_chain1',
   rule_set_name       => NULL,
   evaluation_interval => INTERVAL '30' MINUTE,
   comments            => 'Chain with 30 minute evaluation interval');
END;
/

Enabling Chains

You enable a chain with the ENABLE procedure. A chain must be enabled before it can be run by a job. Enabling an already enabled chain does not return an error.

The following example enables chain my_chain1:

BEGIN
  DBMS_SCHEDULER.ENABLE ('my_chain1');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the ENABLE procedure.

Creating Jobs for Chains

To run a chain, you must either use the RUN_CHAIN procedure or create and schedule a job of type 'CHAIN' (a chain job). The job action must refer to the chain name, as shown in the following example:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name        => 'chain_job_1',
   job_type        => 'CHAIN',
   job_action      => 'my_chain1',
   repeat_interval => 'freq=daily;byhour=13;byminute=0;bysecond=0',
   enabled         => TRUE);
END;
/

For every step of a chain job that is running, the Scheduler creates a step job with the same job name and owner as the chain job. Each step job additionally has a job subname to uniquely identify it. The job subname is included as a column in the views *_SCHEDULER_RUNNING_JOBS, *_SCHEDULER_JOB_LOG, and *_SCHEDULER_JOB_RUN_DETAILS. The job subname is normally the same as the step name except in the following cases:

• For nested chains, the current step name may have already been used as a job subname. In this case, the Scheduler appends '_N' to the step name, where N is an integer that results in a unique job subname.

• If there is a failure when creating a step job, the Scheduler logs a FAILED entry in the job log views (*_SCHEDULER_JOB_LOG and *_SCHEDULER_JOB_RUN_DETAILS) with the job subname set to 'step_name_0'.

Dropping Chains

You drop a chain, including its steps and rules, by using the DROP_CHAIN procedure. An example of dropping a chain is the following, which drops my_chain1:

BEGIN
  DBMS_SCHEDULER.DROP_CHAIN (
   chain_name   => 'my_chain1',
   force        => TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN procedure.

Running Chains

You can use the following two procedures to run a chain immediately:

• RUN_JOB

• RUN_CHAIN

If you already created a chain job for a chain, you can use the RUN_JOB procedure to run that job (and thus run the chain), but you must set the use_current_session argument of RUN_JOB to FALSE.

You can use the RUN_CHAIN procedure to run a chain without having to first create a chain job for the chain. You can also use RUN_CHAIN to run only part of a chain.

RUN_CHAIN creates a temporary job to run the specified chain. If you supply a job name, the job is created with that name, otherwise a default job name is assigned.

If you supply a list of start steps, only those steps are started when the chain begins running. (Steps that would normally have started do not run if they are not in the list.) If no list of start steps is given, the chain starts normally—that is, an initial evaluation is done to see which steps to start running. An example is the following, which immediately runs the chain my_chain1:

BEGIN
  DBMS_SCHEDULER.RUN_CHAIN (
   chain_name    =>  'my_chain1',
   job_name      =>  'partial_chain_job',
   start_steps   =>  'my_step2, my_step4');
END;
/

Dropping Chain Rules

You drop a rule from a chain by using the DROP_CHAIN_RULE procedure. An example is the following, which drops my_rule1:

BEGIN
  DBMS_SCHEDULER.DROP_CHAIN_RULE (
   chain_name   =>   'my_chain1',
   rule_name    =>   'my_rule1',
   force        =>   TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN_RULE procedure.

Disabling Chains

You disable a chain by using the DISABLE procedure. An example is the following, which disables my_chain1:

BEGIN
  DBMS_SCHEDULER.DISABLE ('my_chain1');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DISABLE procedure.

Dropping Chain Steps

You drop a step from a chain by using the DROP_CHAIN_STEP procedure. An example is the following, which drops my_step2 from my_chain2:

BEGIN
  DBMS_SCHEDULER.DROP_CHAIN_STEP (
   chain_name   =>   'my_chain2',
   step_name    =>   'my_step2',
   force        =>    TRUE);
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the DROP_CHAIN_STEP procedure.

Stopping Chains

To stop a running chain, you call DBMS_SCHEDULER.STOP_JOB, passing the name of the chain job (the job that started the chain). When you stop a chain job, all steps of the chain that are running are stopped and the chain ends.

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the STOP_JOB procedure.

Stopping Individual Chain Steps

There are two ways to stop individual chain steps:

• By creating a chain rule that stops one or more steps when the rule condition is met.

• By calling the STOP_JOB procedure.

  For each step to stop, you must specify the schema name, chain job name, and step job subname.

  BEGIN
    DBMS_SCHEDULER.STOP_JOB('oe.chainrunjob.stepa');
  END;
  /
  

  In this example, chainrunjob is the chain job name and stepa is the step job subname. The step job subname is typically the same as the step name, but not always. You can obtain the step job subname from the STEP_JOB_SUBNAME column of the *_SCHEDULER_RUNNING_CHAINS views.

When you stop a chain step, its state is set to STOPPED and the chain rules are evaluated to determine the steps to run next.

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the STOP_JOB procedure.

Pausing Chains

You can pause an entire chain or individual branches of a chain. You do so by setting the PAUSE attribute of one or more steps to TRUE with DBMS_SCHEDULER.ALTER_CHAIN or ALTER_RUNNING_CHAIN. Pausing chain steps enables you to suspend the running of the chain after those steps run.

When you pause a step, after the step runs, its state attribute changes to PAUSED, and its completed attribute remains FALSE. Steps that depend on the completion of the paused step are therefore not run. If you reset the PAUSE attribute to FALSE for a paused step, its state attribute is set to its completion state (SUCCEEDED, FAILED, or STOPPED), and steps that are awaiting the completion of the paused step can then run.

Figure 29-1 Chain with Step 3 Paused

Description of "Figure 29-1 Chain with Step 3 Paused"

In Figure 29-1, Step 3 is paused. Until Step 3 is unpaused, Step 5 will not run. If you were to pause only Step 2, then Steps 4, 6, and 7 would not run. However Steps 1, 3, and 5 could run. In either case, you are suspending only one branch of the chain.

To pause an entire chain, you pause all steps of the chain. To unpause a chain, you unpause one, many, or all of the chain steps. With the chain in Figure 29-1, pausing Step 1 would also achieve the pausing of the entire chain after Step 1 runs.

Skipping Chain Steps

You can skip one or more steps in a chain. You do so by setting the SKIP attribute of one or more steps to TRUE with DBMS_SCHEDULER.ALTER_CHAIN or ALTER_RUNNING_CHAIN. If a step's SKIP attribute is TRUE, then when a chain condition to run that step is met, instead of being run, the step is treated as if it has immediately succeeded. Setting SKIP to TRUE has no effect on a step that is running, that is scheduled to run after a delay, or that has already run.

Skipping steps is especially useful when testing chains. For example, when testing the chain shown in Figure 29-1, skipping Step 7 could shorten testing time considerably, because this step is a nested chain.

Running Part of a Chain

There are two ways to run only a part of a chain:

• Use the ALTER_CHAIN procedure to set the PAUSE attribute to TRUE for one or more steps, and then either start the chain job with RUN_JOB or start the chain with RUN_CHAIN. Any steps that depend on the paused steps do not run. (However, the paused steps do run.)

  The disadvantage of this method is that you must set the PAUSE attribute back to FALSE for the affected steps for future runs of the chain.

• Use the RUN_CHAIN procedure to start only certain steps of the chain, skipping those steps that you do not want to run.

  This is a more straightforward approach and also enables you to set the initial state of steps before starting them.

You may have to use both of these methods to skip steps both at the beginning and end of a chain.

See the discussion of the RUN_CHAIN procedure in Oracle Database PL/SQL Packages and Types Reference for more information.

Monitoring Running Chains

You can view the status of running chains with the following two views:

The *_SCHEDULER_RUNNING_JOBS views contain one row for the chain job and one row for each running step. The *_SCHEDULER_RUNNING_CHAINS views contain one row for each chain step (including any nested chains) and include run status for each step (NOT_STARTED, RUNNING, STOPPED, SUCCEEDED, and so on).

See Oracle Database Reference for details on these views.

Handling Stalled Chains

At the completion of a step, the chain rules are always evaluated to determine the next steps to run. If none of the rules cause another step to start, none cause the chain to end, and the evaluation_interval for the chain is NULL, the chain enters the stalled state. When a chain is stalled, no steps are running, no steps are scheduled to run (after waiting a designated time interval), and no event steps are waiting for an event. The chain can make no further progress unless you manually intervene. In this case, the state of the job that is running the chain is set to CHAIN_STALLED. (However, the job is still listed in the *_SCHEDULER_RUNNING_JOBS views.)

You can troubleshoot a stalled chain with the views ALL_SCHEDULER_RUNNING_CHAINS, which shows the state of all steps in the chain (including any nested chains), and ALL_SCHEDULER_CHAIN_RULES, which contains all the chain rules.

You can enable the chain to continue by altering the state of one of its steps with the ALTER_RUNNING_CHAIN procedure. For example, if step 11 is waiting for step 9 to succeed before it can start, and if it makes sense to do so, you can set the state of step 9 to 'SUCCEEDED'.

Alternatively, if one or more rules are incorrect, you can use the DEFINE_CHAIN_RULE procedure to replace them (using the same rule names), or to create new rules. The new and updated rules apply to the running chain and all future chain runs. After adding or updating rules, you must run EVALUATE_RUNNING_CHAIN on the stalled chain job to trigger any required actions.