JOBS(Tareas) de Base de Datos

Objetivos:

• Conocer que es un JOB de Base de Datos.
• Familiarizarnos con el Paquete: DBMS_SCHEDULER.
• Conocer sus usos y las ventajas que Ofrece.
• Ver ejemplos prácticos.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

________________________________________________________________________________

Los JOBS

Para llevar a cabo ciertos procesos de negocio específicos, las Bases de Datos deben realizar tareas automáticas que analizan y procesan grandes cantidades de datos. Dichas tareas en la mayoría de los casos representan una parte vital para el funcionamiento satisfactorio del negocio en el Mundo Real.

Para tener una idea mas clara podemos mencionar procesos como el cambio de estatus de las facturas pagadas en el mes, la generación mensual de estados de cuenta de las tarjetas de crédito o el proceso de generación de dividendos de las cuentas de ahorro de un banco X, entre otros, son tareas que comúnmente se realizan con la ayuda de JOBs de Base de Datos.

Como es natural, Oracle provee algunas herramientas que nos permiten crear JOBs los cuales pueden ejecutarse automáticamente en una determinada fecha y hora.

________________________________________________________________________________

Paquete DBMS_SCHEDULER
DBMS_SCHEDULER es un paquete que hace la función de Programador de Tareas (Job Scheduler) el cual contiene un conjunto de funciones y procedimientos que permiten crear, desarrollar y mantener Jobs. El mismo fue introducido en la Versión 10g de Oracle con el fin de eventualmente reemplazar el antiguo DBMS_JOB, este último aun vigente en las nuevas Versiones Oracle.

Características de DBMS_SCHEDULER.
• Lleva un registro en forma de auditoria de todos los Jobs ejecutados. (Job History)
• Ofrece una Sintaxis de programación simple y potente.
• No solo permite ejecutar Programas o Bloques de PL/SQL, sino también puede ejecutar programas del propio Sistema Operativo.
• Administra y gestiona recursos entre diferentes clases de Jobs.
• Ofrece un modelo de seguridad basado en privilegios de Jobs.
• Permite asignar nombres y comentarios a los Jobs.
• Permite expresar intervalos de planificación de forma natural.

Procedimientos y Funciones.
A continuación una lista de los procedimientos y funciones mas usados del paquete:

• Procedimiento CREATE_JOB: Lo usamos para crear el JOB. Debido a que este cuenta con varias sobrecargas, la cantidad de parámetros puede diferir a la hora de ejecutarlo.

Para que un usuario pueda crear un JOB en su esquema, necesita el privilegio CREATE JOB. De igual forma un usuario con el privilegio CREATE ANY JOB puede crearlo en cualquier esquema.

De forma predeterminada los JOB se crean inhabilitados, para activarlos debe habilitarlos explícitamente.

Parámetros:

Parámetro	Descripción
job_name	Especifica el nombre del JOB. Si el JOB a ser creado residirá en otro esquema, debe calificarse con el nombre del esquema.
job_type	Este atributo especifica el tipo de JOB. Los valores admitidos son: 'PLSQL_BLOCK', ' STORED_PROCEDURE', 'EXECUTABLE'  y 'CHAIN'.
job_action	Este atributo especifica la acción del JOB.
number_of_arguments	Este atributo especifica el número de argumentos que espera el JOB. El rango es 0-255, siendo 0 el valor predeterminado.
program_name	El nombre del programa asociado al JOB. Si el programa es del tipo EXECUTABLE, el propietario del JOB debe tener el privilegio de sistema CREATE EXTERNAL JOB antes de que se pueda habilitar o ejecutar el JOB.
start_date	Este atributo especifica la fecha en la que se desea ejecutar el JOB por primera vez. Si start_date y repeat_interval se dejan NULL, el JOB se programa para ejecutarse tan pronto que sea habilitado.
event_condition	Se trata de una expresión condicional basada en las columnas de la tabla de cola de origen de eventos.
queue_spec	Este argumento especifica la cola en la que los sucesos que inician este JOB en particular se encadenarán (la cola de origen).
repeat_interval	Este atributo especifica la frecuencia con la que el JOB debe repetirse. Puede especificar el intervalo de repetición utilizando expresiones de calendario o de PL/SQL. La expresión especificada se evalúa para determinar la próxima vez que se ejecutara el JOB. Si no se especifica el repeat_interval, el JOB se ejecutará sólo una vez en la fecha de inicio especificada.
schedule_name	El nombre del programa, ventana o grupo de ventanas asociado con este JOB.
end_date	Este atributo especifica la fecha en la cual el JOB expirará y ya no se ejecutará más. Cuando se alcanza end_date, el JOB se desactiva. El ESTADO del JOB cambia a COMPLETED y el indicador enabled se establece en FALSE. Si no se especifica ningún valor para end_date, el JOB siempre se repetirá, a menos que se configure max_runs o max_failures, en cuyo caso el JOB se detendrá cuando se alcance cualquiera de los dos valores. El valor de end_date debe ser mayor/posterior al valor de start_date. Si no es así, se genera un error cuando se habilita el JOB.
job_priority	Este atributo designa la prioridad de un JOB en relación con otros de la misma clase. Si dos JOBs en la misma clase están programados para iniciarse al mismo tiempo, el que tiene la prioridad más alta toma precedencia. Los valores aceptables son de 1 a 5, donde 1 es la prioridad más alta. El valor predeterminado es 3.
comments	Este atributo especifica un comentario sobre el JOB. De forma predeterminada, este atributo es NULL.
enabled	Este atributo especifica si el JOB se ha creado habilitado o no. Los posibles valores son TRUE o FALSE. De forma predeterminada, este atributo se establece en FALSE y, por lo tanto, el JOB se crea como inhabilitado.
auto_drop	Este indicador, si es TRUE, hace que el JOB se elimine automáticamente después de haber finalizado o se haya inhabilitado.

• Procedimiento SET_ATTRIBUTE: Sirve para cambiar los atributos de un JOB. El mismo está sobrecargado para aceptar diferentes tipos de datos como VARCHAR2, TIMESTAMP WITH TIMEZONE, BOOLEAN, PLS_INTEGER y INTERVAL DAY TO SECOND.

Tenga en cuenta que si alteramos un JOB habilitado, el SCHEDULER primero lo inhabilita, aplica el cambio y luego lo rehabilita. Por el contrario, si está previamente inhabilitado, mantiene su estado luego de sufrir el cambio.

Parámetros:

Parámetro	Descripción
name	Es el nombre del JOB.
attribute	Es el nombre del atributo.
value	Establece el nuevo valor para el atributo. El mismo no puede ser NULL. Para asignar un valor NULL, utilice el procedimiento SET_ATTRIBUTE_NULL.
value2	La mayoría de los atributos tienen sólo un valor asociado con ellos, pero algunos pueden tener dos. El argumento value2 es para este segundo valor opcional.

• Procedimiento ENABLE: Lo usamos para habilitar el JOB, el cual se crea inhabilitado por defecto.

Tenga en cuenta que antes de habilitar un JOB, se realizan algunos controles de validez. Si la comprobación falla, el JOB no se habita y un error es lanzado. Por el contrario, si se trata de habilitar un JOB previamente habilitado, no se produce error alguno.

Parámetros:

Parámetro	Descripción
name	Es el nombre del JOB.

• Procedimiento DISABLE: Inhabilita el JOB. Cuando se Inhabilita un JOB, todos los metadatos del mismo son conservados pero su estado en la Cola de JOBS se cambia a DISABLED.

Parámetros:

Parámetro	Descripción
name	Es el nombre del JOB.
force	Para indicar si se quiere ignorar las dependencias. Si force es FALSE y el JOB está en ejecución, se devuelve un error. Si force es TRUE, el JOB se inhabilita, pero permite que finalice la instancia en ejecución.

• Procedimiento RUN_JOB: Ejecuta el JOB inmediatamente. Tenga en cuenta que el JOB no tiene que estar habilitado. En caso de no estar habilitado, se realizan algunos controles de validez, de resultar exitosos, el JOB es habilitado, se ejecuta y luego mantiene el estado habilitado.

Parámetros:

Parámetro	Descripción
job_name	Es el nombre del JOB.
use_current_session	Especifica si la ejecución del JOB debe producirse en la misma sesión en la que se invocó el procedimiento.

• Procedimiento STOP_JOB: Detiene todos los JOBS que en ese momento se encuentran en ejecución.  Si se detiene un JOB repetitivo, se establecería el estado SCHEDULED o  COMPLETED dependiendo de si su siguiente corrida ya ha sido programada, si el JOB no es repetitivo (ONE-TIME) su estado seria STOPPED.

Parámetros:

Parámetro	Descripción
job_name	Es el nombre del JOB.
force	Si forcé es FALSE, se intenta detener el JOB de manera armónica utilizando un mecanismo de interrupción. Si forcé es TRUE, el JOB es detenido inmediatamente.

• Procedimiento DROP_JOB: Elimina el JOB. Al eliminarlo, se eliminan sus metadatos y el mismo desaparece de la cola de JOBS.

Parámetros:

Parámetro	Descripción
job_name	Es el nombre del JOB.
force	Si la force es FALSE y una instancia del JOB se está ejecutando, la llamada genera un error. Si force es TRUE, se intenta detener la instancia del JOB en ejecución y luego se elimina el JOB.

________________________________________________________________________________
Ejemplos:

CREATE TABLE hr.wrong_salary

(

    id              NUMBER

                        CONSTRAINT pk_id_wrong_sal PRIMARY KEY,

    employee_id     NUMBER(6)

                        CONSTRAINT fk_emp_id

                            REFERENCES hr.employees (employee_id),

    salary          NUMBER(8,2),

    amount_outrage  NUMBER(10,4)

);

/*Para mostrar un ejemplo aplicable, creamos esta tabla para insertar a los empleados con un salario fuera del rango permitido para su empleo: campos min_salary y max_salary de la tabla jobs.*/
---

CREATE SEQUENCE hr.seq_id_wrong_sal

INCREMENT BY 1

START WITH 1

NOCYCLE;

/*Secuencia que usaremos para insertar el Primary Key dela tabla: wrong_salary.*/
---

CREATE OR REPLACE PROCEDURE hr.proc_chk_sal_range IS

    CURSOR cur_chk_emp_sal  IS

        SELECT

                e.employee_id,

                e.salary,

                j.min_salary,

                j.max_salary

        FROM    hr.employees e, jobs j

        WHERE   e.job_id = j.job_id

        AND     e.salary NOT BETWEEN j.min_salary AND j.max_salary;

    v_amount    NUMBER;

BEGIN

    FOR i IN cur_chk_emp_sal LOOP

        IF i.salary < i.min_salary THEN

            v_amount    :=  i.min_salary;

        ELSE

            v_amount    :=  i.max_salary;

        END IF;

        INSERT INTO hr.wrong_salary(id, employee_id, salary, amount_outrage)

        VALUES(hr.seq_id_wrong_sal.NEXTVAL,i.employee_id, i.salary, i.salary-v_amount);

    END LOOP;

    COMMIT;

END proc_chk_sal_range;

/

SHOW ERROR

/*El Procedimiento: proc_chk_sal_range es el que se encargara de hacer todo el proceso: él selecciona a los empleados con un salario invalido y posteriormente lo inserta en la tabla en cuestión..*/
---

SELECT

        e.employee_id,

        e.salary,

        j.min_salary,

        j.max_salary

FROM    hr.employees e, jobs j

WHERE   e.job_id = j.job_id;

/*Con esta consulta podemos ver todos los empleados, sus salarios y el rango valido de su empleo.*/
---

UPDATE hr.employees e

SET     e.salary =  (

                     SELECT j.min_salary - 500

                     FROM    jobs j

                     WHERE   j.job_id = e.job_id

                    )

WHERE   e.employee_id IN (113,108,145,206,183);

---

UPDATE hr.employees e

SET     e.salary =  (

                     SELECT j.max_salary + 310

                     FROM    jobs j

                     WHERE   j.job_id = e.job_id

                    )

WHERE   e.employee_id IN (121,135,131,182,118);

/*Los UPDATES anteriores actualizan los salarios de algunos empleados para así ponerles un salario fuera del rango permitido.*/
---

BEGIN

  SYS.DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'hr.job_chk_emp_sal'

      ,job_type        => 'PLSQL_BLOCK'

      ,start_date      => TO_TIMESTAMP_TZ('1/27/2017 2:23:00.000 PM -08:00','mm/dd/yyyy hh12:mi:ss.ff AM tzr')

      ,repeat_interval => 'FREQ=MONTHLY'

      ,end_date        => TO_TIMESTAMP_TZ('1/30/2018 12:00:00.000 AM -08:00','mm/dd/yyyy hh12:mi:ss.ff AM tzr')

      ,auto_drop       => TRUE

      ,job_action      => '

                            BEGIN

                                hr.proc_chk_sal_range;

                            END;

                          '

      ,comments        => 'Job que valida el salario de los empleados de acuerdo al rango permitido para su puesto.'

    );

END;

/

/*Yaqui creamos el JOB: job_chk_emp_sal para que se ejecute cada mes a partir de la fecha: 1/27/2017. Notar que el parámetro auto_drop lo establecimos en TRUE y que también establecimos una fecha limite en 1/30/2018.*/
---OUTPUT:

BEGIN

    DBMS_SCHEDULER.ENABLE

                        (

                           name    =>  'hr.job_chk_emp_sal'

                        );

END;

/

/*Aquí sencillamente habilitamos el JOB antes creado.*/
---OUTPUT:

---OUTPUT Luego de la primera Ejecución:

BEGIN
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
                                (
                                  name      => 'hr.job_chk_emp_sal'
                                 ,attribute => 'MAX_FAILURES'
                                 ,value     => 3
                                );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
                                (
                                  name      => 'hr.job_chk_emp_sal'
                                 ,attribute => 'JOB_PRIORITY'
                                 ,value     => 2
                                );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
                                (
                                  name      => 'hr.job_chk_emp_sal'
                                 ,attribute => 'AUTO_DROP'
                                 ,value     => FALSE
                                );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL 
                                (
                                  name      => 'hr.job_chk_emp_sal'
                                 ,attribute => 'END_DATE'
                                );

END;
/

/*En este ejemplo le cambias algunos atributos al JOB: job_chk_emp_sal. Notar las modificaciones en el siguiente OUTPUT.*/
---OUTPUT:

--OUTPUT de una consulta a la tabla: wrong_salary

BEGIN

  SYS.DBMS_SCHEDULER.DROP_JOB

    (job_name  => 'hr.job_chk_emp_sal');

END;

/

/*De esta manera eliminamos el JOB.*/

________________________________________________________________________________

Fuentes: https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#CIHHBGGI
https://docs.oracle.com/cd/B28359_01/server.111/b28310/schedadmin006.htm#ADMIN12062