AWS Database Blog

Refactor admin task scheduler job schedules from IBM Db2 LUW to Amazon RDS for MySQL, Amazon RDS for MariaDB, or Amazon Aurora MySQL

The administrative task scheduler (ATS) in Db2 LUW is a component that allows you to automate and schedule administrative tasks within the database. It provides a convenient way to schedule recurring tasks, such as backups, maintenance activities, data imports, exports, and other administrative operations.

Some key features and capabilities of the DB2 LUW ATS include:

  • Task scheduling – ATS allows you to automate routine administrative tasks in Db2 LUW, such as backups, reorganizations, run stats, and maintenance activities. It helps simplify and streamline these tasks by automating their runs based on predefined schedules.
  • Flexible scheduling options – ATS provides a flexible scheduling framework that allows you to define the frequency and timing of task runs. You can schedule tasks to run at specific intervals (such as daily, weekly, or monthly) or specify a custom schedule using cron-like expressions.
  • Logging and monitoring – ATS maintains a log of task runs, including start time, end time, and status information. This log provides a historical record of task runs, allowing you to track and review the run history for auditing or troubleshooting purposes.

In this post, we show you the scheduling options using ATS in IBM Db2 LUW and how to migrate them to target Amazon Relational Database Service (Amazon RDS) for MySQL, Amazon RDS for MariaDB, or Amazon Aurora MySQL-Compatible Edition databases using event schedulers.

Administrative task scheduler in Db2 LUW

The administrative task scheduler is built into the DB2 LUW database and can be accessed through the command line interface (CLI). It enables database administrators (DBAs) to define tasks, set their scheduling parameters, and manage the runs of these tasks.

The scheduled tasks are run by the Db2 autonomic computing daemon (db2acd), which automatically polls every 5 minutes for new or updated tasks in an active database. This daemon maintains a list of the active tasks and invokes them when their respective scheduled run time arrives.

By default, ATS is disabled in Db2 LUW; you can enable it using the registry variable DB2_ATS_ENABLE. You also need to create the SYSTOOLSPACE table space if it doesn’t already exist because Db2 relies on this table space to store historical data and configuration information.

Db2 LUW provides the following SQL routines under the SYSPROC schema, which you can use to schedule and modify tasks:

  • ADMIN_TASK_ADD – This procedure adds new schedules that need to be run at predefined schedules.
  • ADMIN_TASK_REMOVE – This procedure removes scheduled tasks. It also removes task status records.
  • ADMIN_TASK_UPDATE – This procedure updates a task for a given task name as input.

Similarly, you can list all the scheduled tasks or monitor the status of the tasks using the following views under the SYSTOOLS schema:

  • ADMIN_TASK_LIST – This view retrieves information about each task defined in the administrative task scheduler.
  • ADMIN_TASK_STATUS – This view retrieves information about the status of task runs in the administrative task scheduler.

Add, update, or remove tasks using ATS

The task that needs to be run should be encapsulated within a SQL stored procedure, which should be provided as an input for these ATS procedures.

For example, the following command adds a new task to gather statistics for the SALES table every 6 hours:

CALL SYSPROC.ADMIN_TASK_ADD
  ('stats_sales',
    CURRENT_TIMESTAMP,
    NULL,
    NULL,
    '0 */6 * * *',
    'SYSPROC',
    'ADMIN_CMD',
    'VALUES(''RUNSTATS ON TABLE SALES WITH DISTRIBUTION TABLESAMPLE BERNOULLI(30)'')',
    NULL,
    NULL );

You use the ADMIN_TASK_LIST view to list all the tasks created in the database:

select * from systools.ADMIN_TASK_LIST;

Admin task list

List task status

You can use the ADMIN_TASK_STATUS view to list the task status for all the tasks created in the database:

select * from systools.ADMIN_TASK_STATUS;

Admin task status

Update tasks

You can use the ADMIN_TASK_UPDATE procedure to update or modify the task. For the previous sample tasks created, the following update statement modifies the schedule to run stats on the SALES table every 12 hours:

CALL SYSPROC.ADMIN_TASK_UPDATE
	('stats_sales',
	NULL,
	NULL,
	NULL,
	'0 */12 * * *',
	NULL,
	NULL);

Use the ADMIN_TASK_LIST view to verify the updated schedule information.

Delete tasks

To delete tasks, there are two options available. The first option is to delete only the task’s status and retain the actual task so that it continues to run for predefined schedules. The second option is to delete the task itself.

Use the following statement to delete all the task’s statuses that are less than the current timestamp:

CALL SYSPROC.ADMIN_TASK_REMOVE('stats_sales', current_timestamp);

You can validate the ADMIN_TASK_STATUS view to check if all the tasks status entries are removed but not from the ADMIN_TASK_LIST view.

The following statement removes the task itself from the schedule and ADMIN_TASK_LIST will not show this task anymore:

CALL SYSPROC.ADMIN_TASK_REMOVE('stats_sales', NULL);

Event scheduler in Aurora MySQL-Compatible edition, Amazon RDS for MySQL, or Amazon RDS for MariaDB

For use cases that are solved by UNIX/LINUX cron-like functionality, you can choose to port your scheduled jobs into MySQL or MariaDB using the event scheduler. Both MySQL and MariaDB allow you the freedom to run direct SQL statements or more complex business logic using procedural SQL that can be called by configured events in your database instance. By setting up these events to run at a fixed schedule, you can manage operations that you want to run in the database instance on tables residing in your application schema. The syntax for the various operations around the event scheduler are the same in Aurora MySQL-Compatible edition, Amazon RDS for MySQL, and Amazon RDS for MariaDB, which we discuss in this post.

Enable the event scheduler

Complete the following steps to enable the event scheduler in Amazon RDS for MySQL or Amazon RDS for MariaDB:

  1. On the Amazon RDS console, choose Parameter groups in the navigation pane.
  2. Select your parameter group and on the Actions menu, choose Edit.
  3. Choose the value ON for the parameter event_scheduler and choose Save changes.
    Edit parameter group

This is a dynamic parameter and you don’t have to reboot your instance. Now you should be able to configure the event scheduler on your database instance.

The following command shows the event_scheduler daemon status in the database server:

MariaDB [(none)]> use db2inst1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [db2inst1]> SHOW processlist;
+--------+-----------------+-------------------+----------+---------+--------+------------------------+------------------+----------+
| Id     | User            | Host              | db       | Command | Time   | State                  | Info             | Progress |
+--------+-----------------+-------------------+----------+---------+--------+------------------------+------------------+----------+
| 196491 | event_scheduler | localhost         | NULL     | Daemon  | 299234 | Waiting on empty queue | NULL             |    0.000 |
+--------+-----------------+-------------------+----------+---------+--------+------------------------+------------------+----------+

For Aurora MySQL-Compatible edition, there are parameter groups at the cluster as well at the instance level. Setting it at the cluster level is a good idea because all instances will inherit the setting.

Complete the following steps to enable the event scheduler at the cluster level using a DB cluster parameter group:

  1. On the Amazon RDS console, choose Parameter groups in the navigation pane.
  2. Select your parameter group and on the Actions menu, choose Edit.
  3. Choose the value ON for the parameter event_scheduler and choose Save changes.
    Edit Cluster parameter group

This is a dynamic parameter and you don’t have to reboot your DB cluster. Now you should be able to configure the event scheduler on your database instance.

The following command shows the event_scheduler daemon status in the database server:

SHOW processlist;
+--------+-----------------+-------------------+----------+---------+--------+------------------------+------------------+----------+
| Id     | User            | Host              | db       | Command | Time   | State                  | Info             | Progress |
+--------+-----------------+-------------------+----------+---------+--------+------------------------+------------------+----------+
| 196491 | event_scheduler | localhost         | NULL     | Daemon  | 299234 | Waiting on empty queue | NULL             |    0.000 |
+--------+-----------------+-------------------+----------+---------+--------+------------------------+------------------+----------+

Add, modify, or remove events using the event scheduler

You use create event statements to add new events in the database, which can be a one-time event or a recurring event. A one-time event is run once and deleted automatically unless the ON COMPLETION PRESERVE clause is included as part of the create event statement.

From the previous example of gathering stats on the SALES table, the following event is run one time and then deleted:

CREATE EVENT sales_stats
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 MINUTE
    COMMENT 'Analyzes the SALES Once'
    DO
    ANALYZE TABLE db2inst1.SALES;

MariaDB [db2inst1]> show events \G
*************************** 1. row ***************************
                  Db: db2inst1
                Name: sales_stats
             Definer: admin@%
           Time zone: UTC
                Type: ONE TIME
          Execute at: 2023-09-15 02:13:29
      Interval value: NULL
      Interval field: NULL
              Starts: NULL
                Ends: NULL
              Status: ENABLED
          Originator: 1405036644
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.001 sec)

In contrast, the following event is recurring, which runs for every 6 hours. Therefore, the event is persisted unless removed explicitly.

CREATE EVENT sales_stats
    ON SCHEDULE
      EVERY 6 HOUR
    COMMENT 'Analyzes the SALES table every 6 hours'
    DO
    ANALYZE TABLE db2inst1.SALES;

MariaDB [db2inst1]> show events \G
*************************** 1. row ***************************
                  Db: db2inst1
                Name: sales_stats
             Definer: admin@%
           Time zone: UTC
                Type: RECURRING
          Execute at: NULL
      Interval value: 6
      Interval field: HOUR
              Starts: 2023-09-15 02:12:19
                Ends: NULL
              Status: ENABLED
          Originator: 1405036644
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.001 sec)

List tasks

You can list the event details using the show events command (as shown earlier) or from the events table under INFORMATION_SCHEMA. For one-time events, the event details are removed from the events table and not listed using the show events command after they’re run.

The following code shows the sample event listed using the events table under the information_schema table.

MariaDB [db2inst1]> select * from information_schema.events where EVENT_NAME = 'sales_stats' \G
*************************** 1. row ***************************
       EVENT_CATALOG: def
        EVENT_SCHEMA: db2inst1
          EVENT_NAME: sales_stats
             DEFINER: admin@%
           TIME_ZONE: UTC
          EVENT_BODY: SQL
    EVENT_DEFINITION: ANALYZE TABLE db2inst1.SALES
          EVENT_TYPE: RECURRING
          EXECUTE_AT: NULL
      INTERVAL_VALUE: 6
      INTERVAL_FIELD: HOUR
            SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
              STARTS: 2023-09-15 02:12:19
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: NOT PRESERVE
             CREATED: 2023-09-15 02:12:19
        LAST_ALTERED: 2023-09-15 02:12:19
       LAST_EXECUTED: 2023-09-15 02:12:19
       EVENT_COMMENT: Analyzes the SALES table every 6 hours
          ORIGINATOR: 1405036644
CHARACTER_SET_CLIENT: utf8mb3
COLLATION_CONNECTION: utf8mb3_general_ci
  DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.001 sec)

Update tasks

Use the alter event statement to modify the characteristics of an event, such as the schedule or SQL statement under the DO clause or even all the characteristics at once. For the options that are omitted, the alter clause remains unmodified and retains their original values.

ALTER EVENT sales_stats
    ON SCHEDULE
      EVERY 12 HOUR
    COMMENT 'Analyzes the SALES table every 12 hours';

MariaDB [db2inst1]> show events \G
*************************** 1. row ***************************
                  Db: db2inst1
                Name: sales_stats
             Definer: admin@%
           Time zone: UTC
                Type: RECURRING
          Execute at: NULL
      Interval value: 12
      Interval field: HOUR
              Starts: 2023-09-15 02:13:14
                Ends: NULL
              Status: ENABLED
          Originator: 1405036644
character_set_client: utf8mb3
collation_connection: utf8mb3_general_ci
  Database Collation: latin1_swedish_ci
1 row in set (0.001 sec)

Delete tasks

You can use the drop event statement to drop the events that are no longer needed from the database. After the events are dropped, the events table or show events command no longer lists them.

drop event sales_stats;

MariaDB [db2inst1]> drop event sales_stats;
Query OK, 0 rows affected (0.003 sec)

MariaDB [db2inst1]> show events \G
Empty set (0.001 sec)

Common tasks to automate using the event scheduler

Event schedulers are a great way to automate most database-specific tasks. The following are some of the common tasks that can be performed using the event scheduler compared to the source Db2 LUW database:

  • Data maintenance – Schedule tasks like data purging, archiving, or data cleanup to keep your database tidy and optimize performance
  • Data aggregation – Automate the process of aggregating and summarizing data for reporting purposes, reducing the need for manual intervention
  • Table partition maintenance – Add new table partitions or remove old partitions periodically during off-peak hours without any manual intervention
  • Gathering table statistics – Gather explicit statistics on large tables using a predefined schedule to keep the statistics up to date and improve query performance
  • User permission management – Automate user access control tasks, such as granting or revoking permissions, based on predefined schedules.
  • Data archiving – Move historical data or less frequently accessed data to archival or history tables to optimize performance of your live tables

Similarly, certain tasks are applicable on an on-premises Db2 LUW database that aren’t relevant when migrating to AWS from on premises, such as the following:

  • Database backup and restore
  • Patch updates
  • Periodically running custom monitoring scripts

Comparison of Db2 ATS and the MySQL or MariaDB event scheduler

The following table compares the task schedule options between the source Db2 LUW and the target MySQL or MariaDB databases.

Description Db2 LUW MySQL or MariaDB
How to enable Set DB2_ATS_ENABLE registry variable to ON (or 1 or YES) Set EVENT_SCHEDULER DB parameter to ON
Add new tasks ADMIN_TASK_ADD procedure Create event command
Update existing tasks ADMIN_TASK_UPDATE procedure Alter event command
Remove existing tasks ADMIN_TASK_REMOVE procedure Drop event command
Schedule format Using CRON expression Using timestamp functions
Task format Can be encapsulated in procedures only Can be a SQL statement or compound statements or procedures
List tasks ADMIN_TASK_LIST view INFORMATION_SCHEMA.EVENT table or show events command or show create event command
Monitor task status ADMIN_TASK_STATUS view EVENTS.LAST_EXECUTED or error log

Limitations

The following are some limitations of using the event scheduler on Aurora MySQL-Compatible edition, Amazon RDS for MySQL, and Amazon RDS for MariaDB:

  • There is no protection on multiple concurrent runs of an event due to prolonged runtimes during a previous run schedule. However, this can be overcome using the GET_LOCK and RELEASE_LOCK functions within the event definition.
  • You are unable to view event run status historically. One way to overcome this is to use a history table or log table as part of your event logic that records the status of every event run.
  • If the event fails, the record of failure is present only in the error log of the database and is not readily available in any tables. To overcome this, you can publish your error logs to CloudWatch Logs and create a metric based on the error log. This metric can serve as the basis for a CloudWatch alarm that can notify you with details of the event failure.

Conclusion

In this post, we discussed how you can use event schedulers as a solution while migrating from Db2 LUW administrative task schedulers. We also compared the options between Db2 and MySQL or MariaDB databases, and discussed some limitations in the target database compared to the source. With event schedulers, you can automate most common DBA tasks or even business logic to run in a predefined recurring or one-time schedule in the form of SQL statements or procedures, or even compound statements.

Let us know if you have any comments or questions. We value your feedback!

About the Author

Sai Parthasaradhi is a Sr. Lead Database Migration Consultant with AWS Professional Services. He works closely with customers to help them migrate and modernize their databases on AWS.

Oliver Francis is a Sr. Lead Database Migration Consultant with AWS Professional Services. He loves to automate manual processes and enhance the user experience. He helps customers migrate and modernize workloads in the AWS Cloud, with a special focus on modern application architectures and development best practices.