• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Database Quick Start
  5. Other Operations
  6. Creating and Managing Scheduled Tasks

Creating and Managing Scheduled Tasks

Context

When a customer executes some time-consuming tasks during the day time, (for example, statistics summary task or other database synchronization tasks), the service performance will be influenced. So customers execute tasks on database during night time, increasing the workload. The scheduled task function of the database is compatible with the Oracle database scheduled task function that customers can create scheduled tasks. When the scheduled task time arrives, the task will be triggered. Therefore, the workload of OM has been reduced.

Database complies with the Oracle scheduled task function using the DBMS.JOBS interface, which can be used to create scheduled tasks, execute tasks automatically, delete a task, and modify task attributes(including task ID, enable/disable a task, the task triggering time/interval and task contents).

Periodic Task Management

  1. Creates a test table.

    CREATE TABLE test(id int, time date);

    If the following information is displayed, the table has been created.

    CREATE TABLE

  2. Create the customized storage procedure.

    CREATE OR REPLACE PROCEDURE PRC_JOB_1()
    AS
    N_NUM integer :=1;
    BEGIN
    FOR I IN 1..1000 LOOP
    INSERT INTO test VALUES(I,SYSDATE);
    END LOOP;
    END;
    /

    If the following information is displayed, the procedure has been created.

    CREATE PROCEDURE

  3. Create a task.

    • Create a task with unspecified job_id and execute the PRC_JOB_1 storage procedure every two minutes.
      call dbms_job.submit('call public.prc_job_1(); ', sysdate, 'interval ''1 minute''', :a);
      job
      -----
      1
      (1 row)
    • Create task with specified job_id.
      call dbms_job.isubmit(2,'call public.prc_job_1(); ', sysdate, 'interval ''1 minute''');
      isubmit
      ---------
      
      (1 row)

  4. View the created task information about the current user.

    select job,dbname,start_date,last_date,this_date,next_date,broken,status,interval,failures,what from user_jobs;
    job | dbname |     start_date      |         last_date          |         this_date          |      next_date      | broken | status |      interval       | failures |           what
    -----+--------+---------------------+----------------------------+----------------------------+---------------------+--------+--------+---------------------+----------+---------------------------
    1 | postgres   | 2017-07-18 11:38:03 | 2017-07-18 13:53:03.607838 | 2017-07-18 13:53:03.607838 | 2017-07-18 13:54:03 | n      | s      | interval '1 minute' |        0 | call public.prc_job_1();
    (1 row)

  5. Stop a task.

    call dbms_job.broken(1,true);
    broken
    --------
    
    (1 row)

  6. Start a task.

    call dbms_job.broken(1,false);
    broken
    --------
    
    (1 row)

  7. Modify attributes of a task.

    • Modify the Next_date parameter information about a task.
      -- Specify the task of modifying Next_date of Job1 will be executed in one hour.
      call dbms_job.next_date(1, sysdate+1.0/24);
      next_date
      -----------
      
      (1 row)
    • Modify the Interval parameter information of a task.
      -- Specify the task of modifying Interval of Job1 will be executed every two hours.
      call dbms_job.interval(1,'sysdate + 1.0/24');
      interval
      ----------
      
      (1 row)
    • Modify the What parameter information of a JOB.
      -- Specify to modify What to the SQL statement insert into public.test values(333, sysdate+5); of Job1.
      call dbms_job.what(1,'insert into public.test values(333, sysdate+5);');
      what
      ------
      
      (1 row)
    • Modify Next_date, Interval, and What parameter information of JOB.
      call dbms_job.change(1, 'call public.prc_job_1();', sysdate, 'interval ''1 minute''');
      change
      --------
      
      (1 row)

  8. Delete a JOB.

    call dbms_job.remove(1);
    remove
    --------
    
    (1 row)

  9. View the JOB execution status.

    If a JOB fails to execute automatically, (the status of job_status is f), you can query the failure information by visiting the pg_log subdirectory of the CN data directory where the JOB is belongs to.

    From detail error msg, you can see the failure causes.

    LOG:  Execute Job Detail: 
            job_id: 1 
            what: call public.test();  
            start_date: 2017-07-19 23:30:47.401818 
            job_status: failed 
            detail error msg: relation "test" does not exist 
            end_date: 2017-07-19 23:30:47.401818 
            next_run_date: 2017-07-19 23:30:56.855827 

  10. Set JOB permissions.

    • When creating a JOB, the JOB is bound to the user who created the JOB database. (For example, the user who created the JOB database is the dbname and log_user in the pg_job table.)
    • If the current user is a DBA user, system administrator, or the user who create the JOB database, (log_user of pg_job), the user has permissions to delete or modify JOB parameter information using the remove, change, next_data, what, or interval interface. Otherwise, the system displays a message indicating that the current user has no permission to perform operations on the JOB.
    • If the current database is the subdatabase of JOB, (that is, dbname of pg_job), you can delete or modify the JOB parameter information using the remove, change, next_data, what, or interval interface.
    • When deleting a subdatabase of JOB, (that is, dbname of pg_job), the system automatically deletes the dependent JOB database.
    • When deleting a JOB user, (that is, log_user of pg_job), the system deletes the JOB records of the user.