1. Function introduction

Using Dameng database job management tool, you can complete fixed daily work such as regularly backing up the database and regularly generating data statistical reports. These repetitive tasks can be completed automatically, which is convenient for database administrators to improve work efficiency.
DM's operating system provides users with the function of creating jobs and scheduling jobs to complete corresponding management tasks. The operating system roughly includes three parts: operation, alarm and operator. Users need to configure steps and schedules for jobs. You can also create an alarm and notify the operator of the alarm information when an alarm occurs so that the operator can respond in time. By creating a flexible scheduling scheme for jobs, the requirements of running jobs at different times can be met.

Generally, job management is maintained by DBA. Ordinary users do not have permission to operate jobs. In order for ordinary users to create, configure and schedule jobs, ordinary users need to be given permission to manage jobs: ADMIN JOB.
For example, authorize ADMIN JOB to user NORMAL_USER.

GRANT ADMIN JOB TO NORMAL_USER; 

The default DBA has all operation permissions; ADMIN JOB permission can add, configure, schedule and delete jobs, but there is no job environment to initialize the SP_INIT_JOB_SYS(1) and work environment destroy sp_ INIT_ JOB_ Permissions for sys (0).

2. Create a work environment

For job management, you need to create a job environment first, that is, create some system tables to store job related objects, history and other information.
These system tables include SYSJOBS, SYSJOBSTEPS, SYSJOBSCHEDULES, SYSMAILINFO
SYSJOBHISTORIES2,SYSSTEPHISTORIES2,SYSALERTHISTORIES,SYSOPERATORS,
There are ten SYSALERTS and sysalert notifications, all of which are in SYSJOB mode.

3. Configure job management

Refer to relevant documents of Damon database for specific process parameters.
1) Data preparation:
A table MYJOB_TEST, column A is the primary key.

DROP TABLE MYJOB_TEST; 
CREATE TABLE MYJOB(
A INT PRIMARY KEY,
B VARCHAR(8188)
);

2) Create a job environment:
Generate job related system tables.

SP_INIT_JOB_SYS(1); 

3) Create operator TOM.
You can view the relevant information of the created operator through the table SYSOPERATORS.

SP_CREATE_OPERATOR('TOM', 1, 'test_dba@163.com', '\*.\*.\*.\*'); 

4) Create and configure jobs
a. Create job TEST. You can see job related information through the table SYSJOBS.

SP_CREATE_JOB('TEST', 1, 1, 'TOM', 2, 1, 'TOM', 2, 'A test job'); 

b. Start configuration job.

SP_JOB_CONFIG_START('TEST'); 

c. Add a step to the job.
Add myjob to table_ Insert data into test. Because column A is the primary key, an error will be reported if the value of column a of the third data is repeated
Bit error - 6602. You can view step related information through the table SYSJOBSTEPS.

SP_ADD_JOB_STEP('TEST', 'STEP', 0, 'insert into myjob_test values(1000, ''step 1000'');
insert into myjob_test values(1001, ''step 1001'');
insert into myjob_test values(1001, ''step 1001'');', 1, 2, 0, 0, NULL, 0);

d. Add a schedule for the job. You can view scheduling related information through the table SYSJOBSCHEDULES.

SP_ADD_JOB_SCHEDULE('TEST', 'SCHEDULE', 1, 1, 1, 0, 0, '13:40:40', NULL, '2021-10-26 13:40:40', NULL, 'A test schedule');

e. Submit the configuration.

SP_JOB_CONFIG_COMMIT('TEST'); 

5) Create and associate alerts
a. Create alert ALERT1 and specify error code - 6602. Information about alerts can be viewed in the table SYSALERTS.

SP_CREATE_ALERT('ALERT1', 1, 0,12, -6602, 1, 'DDL Alarm test'); 

b. Associate the alarm and send the alarm ALERT1 to the associated operator TOM. Can be in the table
The associated information between the alarm and the operator can be viewed in SYSALERTNOTIFICATIONS.

SP_ALERT_ADD_OPERATOR('ALERT1','TOM',1,1); 

4. View monitoring results

1) View the execution of the job through the table SYSJOBHISTORIES2.

select * from sysjob.SYSJOBHISTORIES2;

LINEID     EXEC_ID     NAME START_TIME          END_TIME            ERRCODE    
---------- ----------- ---- ------------------- ------------------- -----------
           ERRINFO                                                     HAS_NOTIFIED
           ----------------------------------------------------------- ------------
1          991275118   TEST 2021-10-26 13:41:11 2021-10-26 13:41:11 -6602
           [JOBTESTSCHEDULE] Violate unique constraint on [MYJOB_TEST] 0


used time: 0.849(ms). Execute id is 74.

2) View the history of alarm occurrence through the table sysalertstories.
For example, when the user inserts "INSERT INTO MYJOB VALUES(1000, 'STEP 1000');"
The query results of the table sysalertstores are as follows:

select * from sysjob.SYSALERTHISTORIES;

LINEID     ID          ALERTNAME EVENT_TYPE  SUB_TYPE    USERNAME DB_NAME OPTIME              OPUSER SCH_NAME
---------- ----------- --------- ----------- ----------- -------- ------- ------------------- ------ --------
           OBJ_NAME                                  OBJ_TYPE GRANTEE_NAME ERRCODE     HAS_NOTIFIED
           ----------------------------------------- -------- ------------ ----------- ------------
1          1           ALERT1    0           0           SYSDBA   DAMENG  2021-10-26 13:45:05        
           Violate unique constraint on [MYJOB_TEST]                       -6602       0


used time: 0.208(ms). Execute id is 85.