Help Center> GaussDB(DWS)> Developer Guide> Defining Database Objects> Creating and Managing Scheduled Tasks
Updated on 2023-10-31 GMT+08:00
View PDF

Creating and Managing Scheduled Tasks

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.JOB 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).

The hybrid data warehouse (standalone) does not support scheduled tasks.

Periodic Task Management

  1. Creates a test table.

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

  2. Create the customized storage procedure.

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

  3. Create a task.

    • Create a task with unspecified job_id and execute the PRC_JOB_1 storage procedure every two minutes.
    • Create task with specified job_id.

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

    USER_JOBS displays all jobs owned by the user. It is accessible only to users with system administrator rights. For details about the fields, see Table 1.

  5. Stop a task.

  6. Start a task.

  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.
    • Modify the Interval parameter information of a task.
      -- Set Interval of Job1 to 1.
    • Modify the What parameter information of a JOB.
      -- Change What to the SQL statement insert into public.test values(333, sysdate+5); for Job1.
    • Modify Next_date, Interval, and What parameter information of JOB.

  8. Delete a JOB.

  9. Set JOB permissions.

    • During the creation of a job, the job is bound to the user and database that created the job. Accordingly, the user and database are added to dbname and log_user columns in the pg_job system view, respectively.
    • If the current user is a DBA user, system administrator, or the user who created the job (log_user in pg_job), the user has the permissions to delete or modify parameter settings of the job 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 one that created a job, (that is, dbname in pg_job), you can delete or modify parameter settings of the job using the remove, change, next_data, what, or interval interface.
    • When deleting the database that created a job, (that is, dbname in pg_job), the system associatively deletes the job records of the database.
    • When deleting the user who created a job, (that is, log_user in pg_job), the system associatively deletes the job records of the user.