Job schedulers are computer programs controlling other program executions behind the scenes, or as part of  batch processes. This workload automation is typically coordinated so that traditional background data processing can be integrated with real-time business activities without depleting resources or interfering with other applications. The job scheduler automates interfaces in workflows, submits and monitors executions, and queues the execution order of unrelated jobs so operations are handled in the right order, and subject to specific conditions.

A popular tool for database process automation is Oracle’s built-in job scheduler. It can be used not only to automate SQL procedures and utility operations, but for third-party processes affecting Oracle efficiency. Oracle DBAs routinely use IRI FACT, IRI CoSort and SQL*Loader to remove the overhead of transformations from the database layer, and improve workflow throughout in high-volume ETL and offline reorg operations. These tools can now be combined and scheduled with Oracle’s Job Scheduler in the IRI Workbench, a GUI environment built on Eclipse™.

Whether in the command line, an Oracle front-end, or IRI’s in Eclipse™, Oracle DBAs can now schedule inter-dependent ETL steps, or set up unload/sort/reload (reorg) operations using IRI software. With the Oracle Job Scheduler, DBMS_SCHEDULER, DBAs and data warehouse architects can specify when to initiate FACT extracts, CoSort sorts/transforms/reports, and/or bulk loads, and specify dependencies between these processes.

The Oracle Job Scheduler helps the user manage and plan different tasks so manual intervention is not required. To use the job scheduler, three small scripts are needed:

• Procedure to assemble the ETL process

• Function to capture any messages from the Java program

• Java program to handle the command line calls to the ETL steps: FACT (E), CoSort SortCL (T),  and Oracle SQL*Loader (L)

Once the scripts are ready, the user can then create a procedure to automates the desired work. The user can specify the date and time the procedure will begin. The user can also specify how frequently they want the job to run (weekly, daily, hourly, or on a specific time and date down to every 30 seconds).

The scheduler will manage the steps so that they are performed in the correct order.  It is also possible to define custom checks to make sure the next step does not begin unless the previous one has succeeded or met certain criteria.

In the example, below the user has selected the procedure “runFlow” to initiate defined steps every Tuesday at 8:00 AM, and every Friday at 3:00 PM.

DECLARE
JobNo user_jobs.job%TYPE;
v_date1 date := to_date(‘20130312 080000′,’yyyymmdd hh24miss’);
v_date2 date := to_date(‘20130315 150000′,’yyyymmdd hh24miss’);
BEGIN
dbms_job.submit( JobNo,  –Job ID
‘begin runFlow; end;’, — Procedure to execute
v_date1, — start running at
‘SYSDATE + 7’ — interval of jobs
);
COMMIT;
dbms_job.submit(  JobNo,  –Job ID
‘begin runFlow; end;’, — Procedure to execute
v_date2, — start running at
‘SYSDATE + 7’ — interval of jobs
);
COMMIT;
END;

There are significant benefits to automating the ETL processes this way, particularly for Oracle DBAs already familiar with DBMS_SCHEDULER. The method can save the expense of costly ETL or job scheduling packages, minimize the chances of human error, and preclude the need for manual intervention. It’s also possible to use the integration to automate offline reorg and ELT-related steps.

If you have any questions on the implementation of IRI job steps in Oracle Job Scheduler, or would like the white paper example, contact us.