DM Operating System

Catalog

Preface

1. Introduction to Operating System

2. Permissions and System Tables

1. Job Permissions

2. System tables

1.SYSJOBSTEPS

2.SYSJOBSCHEDULES

3.SYSJOBHISTORIES2

4.SYSSTEPHISTORIES2

5.SYSOPERATORS

6.SYSALERTS

7.SYSALERTNOTIFICATIONS

8.SYSALERTHISTORIES

9.SYSMAILINFO

3. Creation and deletion through system processes

1. Command line operations

2. GUI operation

3. Operators

1. Create Operators

2. Modify Operators

3. Delete Operator

4. Operations

1. Create Jobs

2. Modify Job

3. Delete jobs

4. Configuration job

1.Start job configuration;

2.Specify that you want to start configuring a job;

3.Add steps to the specified job;

4.Increase scheduling for specified jobs;

5.End Job Configuration

5. Configure Timing Jobs

1. Full-time jobs

2. Additional jobs

3. Delete backup jobs

4. Archiving Cleanup Jobs

5. Statistical Information Jobs

6. Manually execute the job

summary


Preface

DM operating system.

1. Introduction to Operating System

The job system of DM provides users with the ability to create jobs and schedule the execution of jobs to complete corresponding management tasks. These duplicate database tasks can be automated to achieve daily work automation. The job system roughly consists of jobs, alerts and operators. Users need to configure steps and scheduling for jobs.


2. Permissions and System Tables


1. Job Permissions

GRANT ADMIN JOB TO NORMAL_USER;

The default DBA has full job permissions; ADMIN JOB permissions can add, configure, schedule, delete jobs, and so on, but there is no permission for the job environment to initialize SP_INIT_JOB_SYS(1) and the job environment to destroy SP_INIT_JOB_SYS(0).


2. System tables

The SYSJOBS table stores user-defined job information. Each job corresponds to a record in this table. Each record has a self-increasing ID that uniquely represents the job.

select * from sysjob.sysjobs;

1.SYSJOBSTEPS

SYSJOBSTEPS stores all step information included in a job

2.SYSJOBSCHEDULES

A job can have multiple schedules, which specify the execution of a job, the execution mode of the job, and the time range.

3.SYSJOBHISTORIES2

SYSJOBHISTORIES2 stores a log of the execution of the job.

4.SYSSTEPHISTORIES2

SYSSTEPHISTORIES2 stores a log of the execution of job steps.

5.SYSOPERATORS

SYSOPERATORS stores information about all defined operators in the job management system, aggregated by NAME, meaning that operators with the same name cannot be present.

6.SYSALERTS

SYSALERTS stores all the defined alert information in the Job Management System with a clustered index of NAME, meaning that an alert with the same name cannot be defined.

7.SYSALERTNOTIFICATIONS

SYSALERTNOTIFICATIONS stores information about the operator to be notified of an alert, that is, the alert and the operator's associated information.

8.SYSALERTHISTORIES

SYSALERTHISTORIES stores a log of the history of alert occurrences.

9.SYSMAILINFO

SYSMAILINFO stores information from the job management system administrator.

3. Creation and deletion through system processes

1. Command line operations

These tables can be created by calling the system procedure SP_INIT_JOB_SYS(1). These tables are built in SYS JOB mode.

Create SYSJOB schema and system tables:

SP_INIT_JOB_SYS(1);

Delete SYSJOB schema and system tables:

SP_INIT_JOB_SYS(0);

2. GUI operation

3. Operators

The creation, modification and deletion of operators are accomplished by SP_CREATE_OPERATOR, SP_ALTER_OPERATOR, and SP_DROP_OPERATOR, respectively.

1. Create Operators

SP_CREATE_OPERATOR (

OPR_NAME VARCHAR(128),

ENABLED INT,

EMAILADDR VARCHAR(128),

NETSEND_IP VARCHAR(128)

)

2. Modify Operators

SP_ALTER_OPERATOR (

OPR_NAME VARCHAR(128),

ENABLED INT,

EMAILADDR VARCHAR(128),

NETSEND_IP VARCHAR(128)

)

3. Delete Operator

SP_DROP_OPERATOR (

OPR_NAME VARCHAR(128)

)

4. Operations

1. Create Jobs

SP_CREATE_JOB (

JOB_NAME VARCHAR(128),

ENABLED INT,

ENABLE_EMAIL INT,

EMAIL_OPTR_NAME VARCHAR(128),

EMAIL_TYPE INT,

ENABLED_NETSEND INT,

NETSEND_OPTR_NAME VARCHAR(128),

NETSEND_TYPE INT,

DESCRIBE VARCHAR(8187)

)

For example, create a job named TEST.

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

Once the job is created, a corresponding record is inserted in SYSJOBS, but the job does not

Anything you do is just an empty job that you need to configure if you want it to be executed.

2. Modify Job

SP_ALTER_JOB (

JOB_NAME VARCHAR(128),

ENABLED INT,

ENABLE_EMAIL INT,

EMAIL_OPTR_NAME VARCHAR(128),

EMAIL_TYPE INT,

ENABLED_NETSEND INT,

NETSEND_OPTR_NAME VARCHAR(128),

NETSEND_TYPE INT,

DESCRIBE VARCHAR(8187)

)

The parameters of the function SP_ALTER_JOB are identical to those of the SP_CREATE_JOB, except that the JOB_NAME can be modified. For modifiable parameters, specify a new value if you want to modify them, and continue to specify the original value if you do not. After the job property is modified, you need to reconfigure the job for the modification to take effect.

For example, the following statement modifies some information about the job TEST.

SP_ALTER_JOB('TEST', 0, 1, 'DBA', 2, 1, 'DBA', 2, 'Modify a job');

SP_JOB_CONFIG_START('TEST');

SP_JOB_CONFIG_COMMIT('TEST');

3. Delete jobs

SP_DROP_JOB (

JOB_NAME VARCHAR(128)

)

For example, delete the job TEST.

SP_DROP_JOB('TEST');

4. Configuration job

Configuring a job consists of the following steps:

1.Start job configuration;

2.Specify that you want to start configuring a job;

3.Add steps to the specified job;

4.Increase scheduling for specified jobs;

5.End Job Configuration

1. Use the system procedure SP_JOB_CONFIG_START to specify the start of a job configuration.

SP_JOB_CONFIG_START (

JOB_NAME VARCHAR(128)

)

For example, start configuring the job TEST.

SP_JOB_CONFIG_START('TEST');

2. Set Job Execution Node

SP_JOB_SET_EP_SEQNO (

JOB_NAME VARCHAR(128),

EP_SEQNO INT

)

For example, the following example sets the job to execute on a specified node number.

SP_JOB_SET_EP_SEQNO('TEST',2);

3. The steps for adding jobs are implemented through the system process SP_ADD_JOB_STEP.

SP_ADD_JOB_STEP (

JOB_NAME VARCHAR(128),

STEP_NAME VARCHAR(128),

TYPE INT,

COMMAND VARCHAR(8187),

SUCC_ACTION INT,

FAIL_ACTION INT,

RETRY_ATTEMPTS INT,

RETRY_INTERVAL INT,

OUTPUT_FILE_PATH VARCHAR(256),

APPEND_FLAG INT

)

For example, the following statement adds TEP1 to the job TEST.

SP_ADD_JOB_STEP('TEST', 'STEP1', 0, 'INSERT INTO MYINFO VALUES(1000, ''HELLO

WORLD''); ', 0, 0, 2, 1, NULL, 0);

4. Steps to modify jobs are implemented through the system process SP_ALTER_JOB_STEP

SP_ALTER_JOB_STEP (

JOB_NAME VARCHAR(128),

STEP_NAME VARCHAR(128),

TYPE INT,

COMMAND VARCHAR(8187),

SUCC_ACTION INT,

FAIL_ACTION INT,

RETRY_ATTEMPTS INT,

RETRY_INTERVAL INT,

OUTPUT_FILE_PATH VARCHAR(256),

APPEND_FLAG INT

)

All parameters are the same as those of SP_ADD_JOB_STEP

5. This step can be removed through the system process SP_DROP_JOB_STEP.

SP_DROP_JOB_STEP (

JOB_NAME VARCHAR(128),

STEP_NAME VARCHAR(128)

)

Example: Delete STEP1

SP_DROP_JOB_STEP('TEST', 'STEP1');

6. Increase scheduling is achieved by dispatching the system process SP_ADD_JOB_SCHEDULE.

SP_ADD_JOB_SCHEDULE (

JOB_NAME VARCHAR(128),

SCHEDULE_NAME VARCHAR(128),

ENABLE INT,

TYPE INT,

FREQ_INTERVAL INT,

FREQ_SUB_INTERVAL INT,

FREQ_MINUTE_INTERVAL INT,

STARTTIME VARCHAR(128),

ENDTIME VARCHAR(128),

DURING_START_DATE VARCHAR(128),

DURING_END_DATE VARCHAR(128),

DESCRIBE VARCHAR(500)

)

For example, the following statement adds a schedule named SCHEDULE3 to the job TEST.

SP_ADD_JOB_SCHEDULE('TEST', 'SCHEDULE3', 1, 1, 1, 0, 1, CURTIME, '23:59:59',

CURDATE, NULL, 'A test schedule');

7. Modify the dispatch by dispatching the system process SP_ALTER_JOB_SCHEDULE.

SP_ALTER_JOB_SCHEDULE (

JOB_NAME VARCHAR(128),

SCHEDULE_NAME VARCHAR(128),

ENABLE INT,

TYPE INT,

FREQ_INTERVAL INT,

FREQ_SUB_INTERVAL INT,

FREQ_MINUTE_INTERVAL INT,

STARTTIME VARCHAR(128),

ENDTIME VARCHAR(128),

DURING_START_DATE VARCHAR(128),

DURING_END_DATE VARCHAR(128),

DESCRIBE VARCHAR(500)

)

All parameters are the same as those of SP_ADD_JOB_SCHEDULE

8. The function called is SP_DROP_JOB_SCHEDULE.

SP_DROP_JOB_SCHEDULE (

JOB_NAME VARCHAR(128),

SCHEDULE_NAME VARCHAR(128)

)

For example, delete the schedule named SCHEDULE3 in the job TEST.

SP_DROP_JOB_SCHEDULE('TEST', 'SCHEDULE3');

9. End Job Configuration

When the configuration is complete, the user needs to submit the previous configuration, indicating that the configuration of the job has been completed and adding the job to the running queue. This can be done through the system process SP_JOB_CONFIG_COMMIT.

SP_JOB_CONFIG_COMMIT (

JOB_NAME VARCHAR(128)

)

10. Clearing job log records clears all log records for a job to date through the system process SP_JOB_CLEAR_HISTORIES, that is, deleting related records in tables SYSJOBHISTORIES2 and SYSSTEPHISTORIES2.

SP_JOB_CLEAR_HISTORIES (

JOB_NAME VARCHAR(128)

)

Example: Delete job TEST

SP_JOB_CLEAR_HISTORIES ('TEST');

5. Configure Timing Jobs

Open Job:

SP_INIT_JOB_SYS(1);

1. Full-time jobs

call SP_CREATE_JOB('bakfull',1,0,'',0,0,'',0,'Saturday 23:00 be completely ready');

call SP_JOB_CONFIG_START('bakfull');

call SP_ADD_JOB_STEP('bakfull', 'bak01', 6, '01000000/dmdata1/dmbak', 1, 2, 0, 0, NULL,0);

call SP_ADD_JOB_SCHEDULE('bakfull', 'bak01', 1, 2, 1, 32, 0, '23:00:00', NULL, '2020-11-02 14:42:15', NULL, '');

call SP_JOB_CONFIG_COMMIT('bakfull');

2. Additional jobs

call SP_CREATE_JOB('bakincr',1,0,'',0,0,'',0,'23 per day:00 Supplementary');

call SP_JOB_CONFIG_START('bakincr');

call SP_ADD_JOB_STEP('bakincr', 'bak02', 6, '11000000/dmdata1/dmbak|/dmdata1/dmbak', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('bakincr', 'bak2', 1, 2, 1, 95, 0, '23:00:00', NULL, '2020-11-02 14:44:30', NULL, '');

call SP_JOB_CONFIG_COMMIT('bakincr');

3. Delete backup jobs

call SP_CREATE_JOB('delbak',1,0,'',0,0,'',0,'Sunday 02:00 Backup 14 days before deletion');

call SP_JOB_CONFIG_START('delbak');

call SP_ADD_JOB_STEP('delbak', 'bak1', 0, 'SF_BAKSET_BACKUP_DIR_ADD(''DISK'',''/dmdata1/dmbak'');call sp_db_bakset_remove_batch(''DISK'',now()-14);', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('delbak', 'del01', 1, 1, 1, 0, 0, '23:30:00', NULL, '2020-11-02 14:48:41', NULL, '');

call SP_JOB_CONFIG_COMMIT('delbak');

4. Archiving Cleanup Jobs

Deleting archived logs up to 30 days old will be performed every Sunday at 02:00, October 10, 2020 at 00:00 Start execution

call SP_CREATE_JOB('JOB_DEL_ARCH_TIMELY',1,0,'',0,0,'',0,'Delete archive logs regularly');

call SP_JOB_CONFIG_START('JOB_DEL_ARCH_TIMELY');

call SP_ADD_JOB_STEP('JOB_DEL_ARCH_TIMELY', 'STEP_DEL_ARCH', 0,'SF_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE - 30);', 1, 2, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('JOB_DEL_ARCH_TIMELY', 'SCHEDULE_DEL_ARCH',1, 2, 1, 1, 0, '02:00:00', NULL, '2020-10-10 00:00:00', NULL, '');

call SP_JOB_CONFIG_COMMIT('JOB_DEL_ARCH_TIMELY');

5. Statistical Information Jobs

--Automatically collect statistics every day

call SP_CREATE_JOB('STAT_GATHER',1,0,'',0,0,'',0,'');

call SP_JOB_CONFIG_START('STAT_GATHER');

call SP_ADD_JOB_STEP('STAT_GATHER', 'STAT_GATHER', 3, '', 0, 0, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('STAT_GATHER', 'STAT_GATHER', 1, 1, 1, 0, 0, '23:38:35', NULL, '2021-04-29 13:38:35',NULL, '');

call SP_JOB_CONFIG_COMMIT('STAT_GATHER');

6. Manually execute the job

Query job table

select sysjob.sysjobs;

Execute jobs according to job number:

call dbms_job.run(1626857481);

summary

For more information, please visit the Dream Technology Community:https://eco.dameng.com

Tags: Python

Posted on Sat, 11 Sep 2021 13:25:27 -0400 by Tremour