Lunes, 08 Junio 2020 01:30

JOBS(Tareas) de Base de Datos

Rate this item
(0 votes)

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 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 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,BOOLEANPLS_INTEGER 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 elJOB, el cual se crea inhabilitado por defecto.Tenga en cuenta que antes de habilitar unJOB, 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 elJOB. Cuando se Inhabilita un JOB, todos losmetadatos 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 JOBes 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 unJOB repetitivo, se establecería el estadoSCHEDULED 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 elJOB. 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 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;

/*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 JOBjob_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:

job

 

BEGIN
    DBMS_SCHEDULER.ENABLE
                        (
                           name    =>  'hr.job_chk_emp_sal'
                        );
END;

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

job after enable

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

after run

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 JOBjob_chk_emp_sal. Notar las modificaciones en el siguiente OUTPUT.*/
---OUTPUT:

after SET ATTRIBUTE


--OUTPUT de una consulta a la tabla: wrong_salary

query

 

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

Read 3370 times Last modified on Martes, 11 Agosto 2020 15:54

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL / SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis de Manejo de Base de Datos. Aprende a programar, a crear aplicaciones para empresas y negocios y gana dinero. Hosting y Tecnología.