Scheduling Jobs in the Database
How to define the job
The programs in DBMS_JOB share a set of parameters that define jobs, their execution times, and frequency of execution. All of the DBMS_JOB procedures manipulate one or more of these parameters:
- Job parameter: The job parameter is an integer that uniquely identifies the job. It can be selected either by the user or automatically assigned by the system. The DBMS_JOB.SUBMIT procedure automatically assigns the job number by obtaining the next value from the sequence SYS.JOBSEQ. It is returned as an OUT parameter so the caller can subsequently identify the job submitted. DBMS_JOB.ISUBMIT allows the user to assign a specific unique integer identifier to the job.
Job numbers cannot be changed other than by removing and resubmitting the job. The job number is retained even when the database is exported and imported.
- What parameter: The what parameter is a character string that evaluates to a valid PL/SQL call to be executed automatically by the job queue. You must enclose the what parameter in single quotes if you are using a string literal. Alternatively, you can use a VARCHAR2 variable containing the desired string value. The actual PL/SQL call must be terminated with a semicolon.
what => 'begin proc1 (param1); end;'
- next_date parameter: next_date specifies the next execution date of the job. This parameter defaults to SYSDATE in both the DBMS_JOB.SUBMIT and BROKEN procedures, indicating that the job should be run immediately. Whenever a NULL value is passed for the next_date parameter, the next execution date for the job is set to January 1, 4000. This effectively keeps the job from being executed without removing it from the job queue.
- interval parameter: The interval parameter is a character string representing a valid Oracle date expression. This date expression is evaluated each time the job begins execution. When a job completes successfully, this date becomes the next execution date for the job.
declare v_repeat_interval number; ---- interval=>'sysdate+'||to_char(v_repeat_interval) ||'*(1/24/60/60)')
- broken parameter: Flags job as broken and not to be executed. If the job fails 16 times, the job is marked as broken.
For example, suppose that a procedure called proc1 has the following specification:
Suppose also that you submit proc1 to be executed by the job queue as follows:
DECLARE jobno INTEGER; BEGIN DBMS_JOB.SUBMIT (job =>jobno, What => 'begin proc1; end ;', next_date => SYSDATE, interval => 'SYSDATE+1/24'); COMMIT; END; /
The above job will be started immediately after submission and repeats at every one hour.
you can find out the job number from the USER_JOBS dictionary view by executing the following query.
SQL> Select job from user_jobs where what='begin proc1; end ;' ;
In telecom infrastructure applications, service is invoked by thousands of subscribers every second. In such cases, the telecom operator wants to monitor the performance and raise alerts based on some threshold settings. Assume that the application populates the service invocation details in a table. The access gateway calculates the start_time and end_time for each request to the service operation. Also, assume that the database table is populated as shown in the following table:
|Broker_IP_ Address||application_ name||service_ name||operation_ name||total_ request||success_ count||err_ count||start_ time||end_ time|
|10.14.88.22||App1||LocationSvc||getLocation||100||50||50||2007-9-18.14.25. 30. 149000000||2007-9-18.14.26. 30. 153000000|
|10.14.88.23||App2||SMSService||sendSMS||50||35||15||2007-9-18.14.26. 30. 153000000||2007-9-18.14.27. 30. 146000000|
|10.14.88.24||App2||SMSService||sendSMS||100||100||0||2007-9-18.14.26. 30. 153000000||2007-9-18.14.27. 30. 146000000|
You need to aggregate all one minute data from the above mentioned table and put this information into another table, PM_MEDIATION_TB. This mediated data will be used for further comparison with the threshold and raise appropriate alerts when the metrics exceed threshold values.
Page 2 of 4