Database administrators frequently encounter repetitive maintenance tasks such as routine data backups and statistical report generation. The DM8 job system addresses this need by providing a robust mechanism to define, schedule, and execute administrative tasks automatically. This system minimizes manual intervention, ensuring consistent database maintenance through automated workflows.

Core Components

The job system relies on several key entities:

• Operators: Personnel designated to receive system notifications regarding job execution status.
• Jobs: A collection of sequential steps executed by the DM agent, capable of running scripts, performing backups, or validating data integrity.
• Alerts: Triggered by specific system events or errors, alerts serve to notify defined operators of significant occurrences.
• Schedules: Temporal definitions that determine when specific jobs are triggered, supporting both one-time and recurring execution patterns.

Permission Management

By default, job management is restricted to DBA users. To grant specific job management capabilities to non-DBA users without full administrative privileges, the system provides the ADMIN JOB permission.

GRANT ADMIN JOB TO maintenance_user;

It is important to note that while ADMIN JOB permits creating and managing jobs, it does not authorize the creation or removal of the job environment itself.

Initializing the Job Environment

Before creating jobs, the system environment must be initialized. This process generates the SYSJOB schema, which stores necessary metadata, history logs, and system views. This is achieved via the system stored procedure SP_INIT_JOB_SYS.

-- Enable the job system environment
SP_INIT_JOB_SYS(1);

-- Disable and remove the job system environment
-- SP_INIT_JOB_SYS(0);

Once initialized, metadata can be queried within the SYSJOB schema. Key tables include SYSJOBS (job definitions), SYSJOBSCHEDULES (scheduling details), and SYSJOBHISTORIES2 (execution logs).

Job Configuration Workflow

Creating a functional job involves a specific transactional sequence:

1. Initiate the configuration session.
2. Define the job steps.
3. Define the execution schedule.
4. Commit the configuration.

1. Managing Operators

Operators are defined using SP_CREATE_OPERATOR. They can be modified or removed using corresponding alter and drop procedures.

-- Create an operator named 'DBA_ADMIN'
SP_CREATE_OPERATOR(
    'DBA_ADMIN', 
    1, 
    'dba_admin@example.com', 
    '192.168.1.100'
);

-- Modify operator details
SP_ALTER_OPERATOR('DBA_ADMIN', 1, 'new_admin@example.com', '192.168.1.101');

-- Remove an operator
SP_DROP_OPERATOR('DBA_ADMIN');

2. Defining Jobs

A job is created using SP_CREATE_JOB. This defines the job container and its notification settings.

-- Create a job named 'Weekly_Maintenance'
SP_CREATE_JOB(
    'Weekly_Maintenance', -- Job Name
    1,                    -- Enabled
    0,                    -- Email Notification Disabled
    '',                   -- Email Operator
    0,                    -- Email Type
    0,                    -- Net Send Disabled
    '',                   -- Net Send Operator
    0,                    -- Net Send Type
    'Performs weekly maintenance tasks' -- Description
);

3. Configuring Steps and Schedules

After creating the job shell, the configuration must be started before adding details.

-- Begin configuration
SP_JOB_CONFIG_START('Weekly_Maintenance');

Steps define the actual work. The following example adds a backup step (Type 6). The command string format for backups includes parameters for mode, compression, and paths.

-- Add a full backup step
-- Type 6 indicates a backup/restore command string
-- Format: [Mode][Compress][Log][Parallel]...[Path]
SP_ADD_JOB_STEP(
    'Weekly_Maintenance', 
    'Step_Full_Backup', 
    6, 
    '01000000/opt/dmdbms/backup/full', 
    1, -- Success Action: Stop with success
    2, -- Fail Action: Stop with failure
    0, -- Retry attempts
    0, -- Retry interval
    NULL, 
    0
);

Schedules determine when the job runs. The following schedule triggers every Sunday at 01:00 AM.

-- Add a weekly schedule (Type 2)
-- FREQ_INTERVAL: 1 represents Sunday
SP_ADD_JOB_SCHEDULE(
    'Weekly_Maintenance',
    'Schedule_Weekly_Sun',
    1, -- Enabled
    2, -- Frequency Type: Weekly
    1, -- FREQ_INTERVAL: Occurs on Sunday
    1, 
    0, 
    '01:00:00', -- Start Time
    NULL, 
    '2023-01-01 00:00:00', 
    NULL, 
    'Weekly Sunday Execution'
);

Finally, the configuration must be committed to save changes.

SP_JOB_CONFIG_COMMIT('Weekly_Maintenance');

Practical Implementation: Comprehensive Backup Strategy

The following scenario demonstrates a complete backup strategy:

• Weekly Full Backup: Executes every Sunday at 01:00.
• Daily Incremental Backup: Executes Monday through Saturday at 01:00.
• Backup Retention: Cleans up backup files older than 21 days daily at 03:00.

-- Initialize System
SP_INIT_JOB_SYS(1);

-- 1. Weekly Full Backup Job
SP_CREATE_JOB('job_full_bak', 1, 0, '', 0, 0, '', 0, 'Weekly Full Backup');
SP_JOB_CONFIG_START('job_full_bak');
SP_ADD_JOB_STEP('job_full_bak', 'step_full', 6, '01000000/opt/dmdata/backup/full', 1, 2, 0, 0, NULL, 0);
SP_ADD_JOB_SCHEDULE('job_full_bak', 'sched_sunday', 1, 2, 1, 1, 0, '01:00:00', NULL, '2023-01-01 00:00:00', NULL, 'Every Sunday');
SP_JOB_CONFIG_COMMIT('job_full_bak');

-- 2. Daily Incremental Backup Job
-- FREQ_INTERVAL 126 (binary 1111110) represents Mon-Sat
SP_CREATE_JOB('job_incr_bak', 1, 0, '', 0, 0, '', 0, 'Daily Incremental Backup');
SP_JOB_CONFIG_START('job_incr_bak');
SP_ADD_JOB_STEP('job_incr_bak', 'step_incr', 6, '10000000/opt/dmdata/backup/full|/opt/dmdata/backup/incr', 1, 2, 0, 0, NULL, 0);
SP_ADD_JOB_SCHEDULE('job_incr_bak', 'sched_daily', 1, 2, 1, 126, 0, '01:00:00', NULL, '2023-01-01 00:00:00', NULL, 'Mon-Sat');
SP_JOB_CONFIG_COMMIT('job_incr_bak');

-- 3. Backup Cleanup Job
-- Removes backups older than 21 days
SP_CREATE_JOB('job_cleanup', 1, 0, '', 0, 0, '', 0, 'Clean old backups');
SP_JOB_CONFIG_START('job_cleanup');
SP_ADD_JOB_STEP(
    'job_cleanup', 
    'step_delete', 
    0, 
    'SF_BAKSET_BACKUP_DIR_ADD(''DISK'', ''/opt/dmdata/backup/full''); SF_BAKSET_BACKUP_DIR_ADD(''DISK'', ''/opt/dmdata/backup/incr''); CALL SP_DB_BAKSET_REMOVE_BATCH(''DISK'', SYSDATE-21);', 
    1, 2, 0, 0, NULL, 0
);
SP_ADD_JOB_SCHEDULE('job_cleanup', 'sched_clean', 1, 1, 1, 0, 0, '03:00:00', NULL, '2023-01-01 00:00:00', NULL, 'Daily Cleanup');
SP_JOB_CONFIG_COMMIT('job_cleanup');

Monitoring and Maintenance

Job definitions and execution history can be monitored through system views within the SYSJOB schema.

-- View all defined jobs
SELECT NAME, ENABLE, DESCRIBE FROM SYSJOB.SYSJOBS;

-- View schedule details
SELECT NAME, STARTTIME, TYPE FROM SYSJOB.SYSJOBSCHEDULES;

-- View execution history
SELECT NAME, START_TIME, END_TIME, ERRCODE, ERRINFO FROM SYSJOB.SYSJOBHISTORIES2;

To manage disk space, job history logs can be cleared using SP_JOB_CLEAR_HISTORIES. Running jobs can be terminated using SP_STOP_RUNNING_JOB.