Question: How do I chain together job
in the database? Can you provide a example script showing an
example Oracle job chain?
Answer: A
Oracle job chain is a named series of programs that are
linked together for a combined objective. Each position
within a chain of interdependent programs is referred to as
a step. Each step can point to one of the following: a
program, another chain (a nested chain), an event.
Note: This feature introduced in Oracle
10g release 2.
To create and use a chain:
1. Create a chain object
DBMS_SCHEDULER.CREATE_CHAIN (
CHAIN_NAME => 'bulk_load_chain',
RULE_SET_NAME => NULL,
EVALUATION_INTERVAL => NULL,
COMMENTS => 'Load data and run reports')
2. Define one or more chain steps. You
define a step that points to a program or nested chain.
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'do_bulk_load',
PROGRAM_NAME => 'hr.load_data_prog)
Also you can 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 or can include an in-line queue specification and
event condition.
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'stop_when_disk_full_evt'
EVENT_SCHEDULE_NAME => 'disk_full_sched')
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'load_data_evt',
EVENT_CONDITION =>
'tab.user_data.object_owner=''HR''
and
tab.user_data.object_name = ''DATA.TXT'' and
tab.user_data.event_type =''FILE_ARRIVAL'' ',
QUEUE_SPEC
=> 'HR.LOAD_JOB_EVENT_Q')
3. Define chain rules. Each rule has a
condition and an action.
If the condition evaluates to TRUE, the
action is performed. Conditions are usually based on the
outcome of one or more previous steps. A condition accepts
Boolean and numeric integer values in an expression.
The entire expression must evaluate to a
Boolean value.
The simplified syntax of a chain condition
is as follows:
'factor|NOT(factor)[AND|OR
factor]'
factor:
stepname
ERROR_CODE number|[NOT]step_condition
When creating a rule condition using the
simplified syntax:
• You specify one or more factors, and a
Boolean operator (AND, OR, or NOT).
• A factor can be either a simple Boolean
value (TRUE or FALSE) or a chain condition. A chain
condition describes the condition of another step in the job
chain. You can use the following to describe the chain
condition:
o The current state of the chain step:
SUCCEEDED
FAILED
STOPPED
COMPLETED
o The error code returned by the chain
step. The error is a numeric value, and can be:
Evaluated within a
numeric clause
Compared to a list of
values using an IN clause
You can use negative factors, by enclosing
the factor in parentheses and prefixing the factor with the
NOT operator.
Examples:
'step1
SUCCEEDED AND step2 ERROR_CODE = 3'
'TRUE'
'step3 NOT
COMPLETED AND NOT (step1 SUCCEEDED)'
'step2 ERROR_CODE
NOT IN (1,2,3)'
You can also refer to attributes of chain
steps of the chain (this is called bind-variable syntax).
The syntax is as follows:
STEP_NAME.ATTRIBUTE
• Possible attributes are: completed, state
start_date, end_date, error_code, and duration.
• Possible values for the state attribute
include:
'NOT_STARTED',
'SCHEDULED', 'RUNNING',
'PAUSED', 'SUCCEEDED',
'FAILED', and
'STOPPED'.
• If a step is in the state 'SUCCEEDED',
'FAILED', or 'STOPPED', its completed attribute is set to
'TRUE'; otherwise, completed is 'FALSE'.
Some examples of the bind variable syntax
are:
':step1.state=''SUCCEEDED''
and
:step2.error_code=3'
'1=1'
':step3.state !=
''COMPLETED'''
':step2.error_code not in (1,2,3)'
':step1.state
= ''NOT_STARTED'''
The rule action specifies what is to be done
as a result of the rule being triggered. A typical action is
to run a specified step. Possible actions include:
o START step_1[,step_2...]
o STOP
step_1[,step_2...]
o END [{end_value |
step_name.error_code}]
When the job starts and at the end of each
step, all rules are evaluated to see what action or actions
occur next. You can also configure rules to be evaluated at
regular intervals by using the EVALUATION_INTERVAL attribute
of the chain.
You add a rule to a chain with the
DEFINE_CHAIN_RULE procedure:
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'TRUE', -- starting step
ACTION =>
'START load_data_evt,
stop_when_disk_full_evt',
Rule_Name => 'dataload_rule1',
COMMENTS => 'start the
chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'load_data_evt
COMPLETED',
ACTION => 'START do_bulk_load',
RULE_NAME => 'dataload_rule2');
END;
4. Enable a chain with the ENABLE procedure
(A chain is always created disabled). Enabling an already
enabled chain does not return an error.
DBMS_SCHEDULER.ENABLE ('bulk_load_chain');
5. To run a chain, you must create a job of
type 'CHAIN'. The job action must refer to the chain name.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'bulk_load_chain_job',
job_type => 'CHAIN',
job_action => 'bulk_load_chain',
repeat_interval => 'freq=daily;byhour=7',
enabled =>
TRUE);
END;
Oracle job chaining example script
The job_chain_aq.sql script cbelow creates a package
specification and body that will do all the work for the
example job chain.
job_chain_aq.sql
CREATE OR REPLACE
PACKAGE job_chain_aq AS
PROCEDURE task_1;
PROCEDURE task_2;
PROCEDURE task_3;
PROCEDURE enqueue_message (p_queue_name
IN VARCHAR2);
PROCEDURE dequeue_message (p_queue_name
IN VARCHAR2);
END job_chain_aq;
/
SHOW ERRORS
CREATE OR REPLACE
PACKAGE BODY job_chain_aq AS
--
-----------------------------------------------------------------
PROCEDURE task_1 AS
--
-----------------------------------------------------------------
BEGIN
DELETE FROM job_chain;
INSERT INTO
job_chain (created_timestamp, task_name)
VALUES (systimestamp, 'TASK_1');
COMMIT;
-- Uncomment
the following line to force a failure.
--RAISE_APPLICATION_ERROR(-20000,
-- 'This is a fake error
to prevent task_2 being executed');
-- The work
has comleted successfully so signal task_2
enqueue_message (p_queue_name =>
'task_2_queue');
EXCEPTION
WHEN OTHERS THEN
-- Don't signal
task_2.
NULL;
END
task_1;
--
-----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE task_2 AS
--
-----------------------------------------------------------------
BEGIN
dequeue_message (p_queue_name =>
'task_2_queue');
INSERT INTO job_chain (created_timestamp,
task_name)
VALUES (systimestamp, 'TASK_2');
COMMIT;
-- Uncomment
the following line to force a failure.
--RAISE_APPLICATION_ERROR(-20000,
-- 'This is a fake error
to prevent task_3 being executed');
-- The work
has comleted successfully so signal task_3
enqueue_message (p_queue_name =>
'task_3_queue');
EXCEPTION
WHEN OTHERS THEN
-- Don't signal
task_3.
NULL;
END
task_2;
--
-----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE task_3 AS
--
-----------------------------------------------------------------
BEGIN
dequeue_message (p_queue_name =>
'task_3_queue');
INSERT INTO job_chain (created_timestamp,
task_name)
VALUES (systimestamp, 'TASK_3');
COMMIT;
END task_3;
--
-----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE enqueue_message (p_queue_name
IN VARCHAR2) AS
--
-----------------------------------------------------------------
l_enqueue_options
DBMS_AQ.enqueue_options_t;
l_message_properties
DBMS_AQ.message_properties_t;
l_message_handle
RAW(16);
l_job_chain_msg
job_chain_msg_type;
BEGIN
l_job_chain_msg :=
job_chain_msg_type('GO');
DBMS_AQ.enqueue(queue_name
=> 'job_user.' || p_queue_name,
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload
=> l_job_chain_msg,
msgid
=> l_message_handle);
END enqueue_message;
--
-----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE dequeue_message (p_queue_name
IN VARCHAR2) AS
--
-----------------------------------------------------------------
l_dequeue_options
DBMS_AQ.dequeue_options_t;
l_message_properties
DBMS_AQ.message_properties_t;
l_message_handle
RAW(16);
l_job_chain_msg
job_chain_msg_type;
BEGIN
DBMS_AQ.dequeue(queue_name
=> 'job_user.' || p_queue_name,
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload
=> l_job_chain_msg,
msgid
=> l_message_handle);
END dequeue_message;
--
-----------------------------------------------------------------
END job_chain_aq;
/
SHOW ERRORS
Next, the jobs associated with each task are scheduled.
Unlike the previous example, the job sequence is protected
by the queue, so all the jobs can be enabled.
job_chain_aq_jobs.sql
-- Oracle
BEGIN
DBMS_SCHEDULER.create_job (
job_name
=> 'job_chain_aq_task_1',
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'job_chain_aq.task_1',
start_date
=> SYSTIMESTAMP,
repeat_interval =>
'freq=daily; byhour=6; byminute=0; bysecond=0;',
end_date
=> NULL,
enabled =>
TRUE,
comments => 'First
task in the AQ chain.');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name
=> 'job_chain_aq_task_2',
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'job_chain_aq.task_2',
start_date
=> SYSTIMESTAMP,
repeat_interval =>
'freq=daily; byhour=12; byminute=0; bysecond=0;',
end_date
=> NULL,
enabled =>
TRUE,
comments =>
'Second task in the AQ chain.');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name
=> 'job_chain_aq_task_3',
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'job_chain_aq.task_3',
start_date
=> SYSTIMESTAMP,
repeat_interval =>
'freq=daily; byhour=18; byminute=0; bysecond=0;',
end_date
=> NULL,
enabled =>
TRUE,
comments => 'Third
task in the AQ chain.');
END;
/
EXEC
DBMS_SCHEDULER.run_job ('job_chain_aq_task_1');
-- Oracle
BEGIN
DBMS_SCHEDULER.drop_job ('job_chain_aq_task_3');
DBMS_SCHEDULER.drop_job ('job_chain_aq_task_2');
DBMS_SCHEDULER.drop_job ('job_chain_aq_task_1');
END;
/
At this point, the tasks are scheduled but have not
been executed; therefore, there are no results in the
job_chain table or the job_chain_queue_tab table.
Rather than waiting until 6:00, the first job is forced to
run immediately. The results below show that the first
task has run and there is a message waiting in the queue
table on the task_2_queue.
SQL> exec dbms_scheduler.run_job ('job_chain_aq_task_1');
PL/SQL procedure
successfully completed.
job_user@db10g> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
--------------------------- --------------------
07-AUG-2004 18:18:36.136000 TASK_1
SQL> @job_chain_aq_query.sql
QUEUE
MESSAGES
------------------------------ ----------
TASK_2_QUEUE
1
If the run of the second job is
forced, the second task reads a message from its queue,
completes its processing and places a message on the queue
for the third task.
SQL> exec dbms_scheduler.run_job ('job_chain_aq_task_2');
PL/SQL procedure
successfully completed.
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
--------------------------- --------------------
07-AUG-2004 18:18:36.136000 TASK_1
07-AUG-2004 18:23:08.771000 TASK_2
SQL> @job_chain_aq_query.sql
QUEUE
MESSAGES
------------------------------ ----------
TASK_3_QUEUE
1
If the run of the third job is
forced, the third task reads a message from its queue and
completes its processing.
SQL> exec dbms_scheduler.run_job ('job_chain_aq_task_3');
PL/SQL procedure
successfully completed.
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
--------------------------- --------------------
07-AUG-2004 18:18:36.136000 TASK_1
07-AUG-2004 18:23:08.771000 TASK_2
07-AUG-2004 18:26:04.972000 TASK_3
SQL> @job_chain_aq_query.sql
no rows selected
If manually attempting to start jobs out of sequence,
the sessions hang until the appropriate message is sent.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
