Overview


This document describes how to create and execute Job objects, and how to monitor them.

 

Version


Altibase version 6.3.1 or later

 

How to use


To create a Job object and operate normally, the user must proceed in the following order.

  1. Enable the task scheduler function
  2. Create the procedure to register in the Job object
  3. Create the Job object
  4. Enable the Job object (only performed on Altibase 6.5.1 or later. Altibase 6.3.1 is not applicable)
  5. Check the Job object and execution result

Enabling the task scheduler function (Altibase Server Property Settings)


If the user is using the Job object for the first time, the job scheduler function must be enabled by changing the following Altibase server properties.

The job scheduler performs a procedure according to the settings registered in the job object.

Both of the above properties must be set to 1 to use the task scheduler. The default value is 0, so be sure to check if the user is using Task Scheduler for the first time.

The JOB_SCHEDULER_ENABLE property can be changed even when the Altibase server is running, but to change the value of the JOB_THREAD_COUNT property, the Altibase server must be restarted.

Therefore, when using the task scheduler for the first time, change the properties according to the procedure below.

1. Stop the Altibase server

$ server stop

2. Find the JOB_SCHEDULER_ENABLE and JOB_THREAD_COUNT properties in the altibase.properties file, change the value to 1, and save.

$ cd $ALTIBASE_HOME/conf
$ vi altibase.properties

3. Start the Altibase server

$ server start

4. Check the property setting value

SELECT NAME, MEMORY_VALUE1 FROM X$PROPERTY WHERE NAME IN ('JOB_SCHEDULER_ENABLE', 'JOB_THREAD_COUNT');

Creating the procedure


Create a procedure to be registered in the Job object and check if the procedure is executed normally.

The reason for checking whether the procedure is normally executed is to exclude the possibility of a procedure problem if the Job object does not operate as set by the job scheduler.

 

Creating the Job object


Create a JOB object.

The stored procedure to be executed, execution time, and execution cycle can be set in the JOB object. For the statement of creating a JOB object, refer to the SQL Reference manual. (Manual download page: http://support.altibase.com/en/manual or https://github.com/ALTIBASE/Documents/tree/master/Manuals/)

CREATE JOB job1
EXEC proc1
START sysdate
END sysdate + 3
INTERVAL 1 HOUR; 

 

Enabling the job object


This operation is an added procedure with the addition of a function to enable/disable a specific JOB in Altibase version 6.5.1.

In Altibase version 6.3.1, when creating a JOB object, it becomes 'enabled' immediately, but from Altibase 6.5.1, if the ENABLE option is not used in the CREATE JOB statement, it is 'disabled'.

Therefore, starting from Altibase version 6.5.1, after creating a Job object, in order to make the Job run, it must be changed to the enabled state.

The change method is as follows.

ALTER JOB job_name SET ENABLE;  

Starting from Altibase version 6.5.1, it is also possible to set it to "Enabled" immediately when creating a Job object.

CREATE JOB job1
EXEC proc1
START sysdate
END sysdate + 3
INTERVAL 1 HOUR
ENABLE;                  

 

Check the Job object and execution result


The user can check the job object information and job execution result with the query below.

-- JOB_NAME  : Name of Job object
-- PROC_NAME : Name of the procedure registered in the Job object
-- INTERVAL, INTERVAL_TYPE : Performance cycle
-- STATE : Check whether Job object is executed. If ING, the user can see that the procedure registered in the Job object is being executed.
-- EXEC_COUNT : Number of times the job object was executed after creation
-- ERROR_CODE : Error code when the procedure registered in the Job object fails
-- START_TIME, END_TIME : Time when the Job object was first executed / Time when it was finished
-- LAST_EXEC_TIME : Last time the Job object was performed
SELECT JOB_NAME
     , DECODE(IS_ENABLE, 'T', 'ENABLE', 'F', 'DISABLE') IS_ENABLE                  -- Delete then use it in Altibase 6.3.1
     , EXEC_QUERY PROC_NAME
     , INTERVAL
     , RPAD(DECODE(INTERVAL_TYPE, 'YY', 'YEARLY', 'MM', 'MONTHLY', 'DD', 'DAILY', 'HH', 'HOURLY', 'MI', 'MINUTELY'), 13) INTERVAL_TYPE
     , RPAD(DECODE(STATE, 0, '-', 1, 'ING'), 5) STATE
     , RPAD(EXEC_COUNT, 10) EXEC_COUNT
     , RPAD(ERROR_CODE, 10) ERROR_CODE
     , TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') SYSDATE
     , TO_CHAR(START_TIME, 'YYYY-MM-DD HH:MI:SS') START_TIME
     , TO_CHAR(END_TIME, 'YYYY-MM-DD HH:MI:SS') END_TIME 
     , TO_CHAR(LAST_EXEC_TIME, 'YYYY-MM-DD HH:MI:SS') LAST_EXEC_TIME
  FROM SYSTEM_.SYS_JOBS_; 
JOB_NAME              IS_ENABLE  PROC_NAME             INTERVAL    INTERVAL_TYPE         STATE       EXEC_COUNT            ERROR_CODE            SYSDATE               START_TIME            END_TIME              LAST_EXEC_TIME        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
JOB2                  DISABLE  PROC                  1           MINUTELY              -           0                                           2016-08-30 14:15:06   2016-08-30 10:08:22   2016-09-02 10:08:22                         
JOB1                  ENABLE   PROC                  1           MINUTELY              -           43                                          2016-08-30 14:15:06   2016-08-30 10:11:00                         2016-08-30 14:15:00   
2 rows selected. 

The error message corresponding to the error code can be checked by using the altierr utility.


$altierr 0x31129

0x31129 (201001) qpERR_ABORT_QSV_NOT_EXIST_PROC_SQLTEXT Procedure or function not found : <0%s>.
# *Cause: The specified procedure or function name was not found in the database.
# *Action: Verify that the procedure or function exists.

 

Related Properties


JOB_SCHEDULER_ENABLE


Property description
How to change the set value
How to check the set value

JOB_THREAD_COUNT


Property description
How to change the set value
How to check the set value

JOB_THREAD_QUEUE_SIZE


Property description
How to change the set value
How to check the set value

Reference


The following are manuals for the description of the task scheduler.