Contents


Use the SQL-based Administrative Task Scheduler in DB2 9.5 for Linux, UNIX, and Windows

Comments

Automating routine tasks allows database administrators to spend more time on higher value projects. DB2 Universal Database Version 8 introduced the Task Center. Using a graphical user interface, the Task Center allowed customers to schedule and automate a variety of tasks. Unfortunately, the Task Center did not provide any programmable interface for third-party or in-house software application developers. Since then, other IBM data server products, like DB2 for z/OS and Informix® Dynamic Server® (IDS), have provided an SQL interface to their task schedulers.

ATS enables the DB2 server to execute administrative tasks at a predefined schedule. Since ATS is built into the DB2 server, it can operate independently of the Task Center and DAS.

Unlike the Task Center, DB2 provides an SQL interface to control ATS. There are built-in procedures that add, update, and remove tasks from the scheduler's task list. DB2 also provides a set of views to display the scheduled tasks and their execution status.

Setup

ATS is disabled by default. Users can still use the ATS procedures and views to define and modify existing tasks. However, the scheduler does not execute any tasks until the DB2_ATS_ENABLE registry variable is set. To set the registry variable, use the following command:

db2set DB2_ATS_ENABLE=YES

Like other DB2 administration tools, ATS depends on the SYSTOOLSPACE tablespace to store historical data and configuration information. Most users already have this tablespace defined in their database. You can check if the tablespace exists in your system with the following query.

db2 select TBSPACE from SYSCAT.TABLESPACES where TBSPACE = 'SYSTOOLSPACE'

If your database does not have this tablespace, you must create it before you can use ATS. Any user that belongs to the SYSADM or SYSCTRL group has authority to create this tablespace. There are instructions available in the DB2 9.5 Information Center. The following demonstrates a sample CREATE TABLESPACE statement:

db2 create tablespace SYSTOOLSPACE managed by automatic storage

If you forget to create the tablespace, you will encounter an SQLCODE -204 error, like the one below, when you add your first task.

SQL0204N  "SYSTOOLSPACE" is an undefined name.  SQLSTATE=42704

Finally, to ensure your tasks execute on time, your database must be active. The best way to do this is to use the ACTIVATE DATABASE command. Alternatively, you can keep a database active if you maintain at least one database connection at all times.

What is an administrative task?

ATS allows you to automate the execution of administrative tasks. An administrative task is any piece of work that can be encapsulated inside a catalogued stored procedure.

For example, imagine you have a defined a refresh-deferred materialized query table (MQT). You would like it to automatically refresh the MQT every hour. To do this, first create a procedure that refreshes your MQT. then schedule the execution of this procedure for every hour.

To illustrate, assume you have the following base table and MQT defined.

create table t1 (col1 int)
create table mqt1 (col1) as (select col1 from t1) data initially deferred refresh deferred

You can create an SQL procedure that refreshes your MQT, similar to the following code snippet.

create procedure refresh1() language SQL specific refresh1
begin 
     declare stmt varchar(30); 
     set stmt = 'refresh table mqt1'; 
     execute immediate stmt; 
end

For reference, here's a more generic version of the MQT refresh procedure. With this version, you pass in the table name.

create procedure refresh2(in tbname varchar(128)) language SQL specific refresh2
begin 
     declare stmt varchar(30); 
     set stmt = concat('refresh table ',tbname); 
     execute immediate stmt; 
end

Many database administration tasks, such as online backup, can be performed by calling the built-in ADMIN_CMD procedure. ADMIN_CMD allows administrative CLP commands to be performed using the SQL CALL statement.

Here is a is a list of CLP commands that are supported by the ADMIN_CMD procedure:

  • ADD CONTACT
  • ADD CONTACTGROUP
  • AUTOCONFIGURE
  • BACKUP - online only
  • DESCRIBE
  • DROP CONTACT
  • DROP CONTACTGROUP
  • EXPORT
  • FORCE APPLICATION
  • IMPORT
  • INITIALIZE TAPE
  • LOAD
  • PRUNE HISTORY/LOGFILE
  • QUIESCE DATABASE
  • QUIESCE TABLESPACES FOR TABLE
  • REDISTRIBUTE
  • REORG INDEXES/TABLE
  • RESET ALERT CONFIGURATION
  • RESET DATABASE CONFIGURATION
  • RESET DATABASE MANAGER CONFIGURATION
  • REWIND TAPE
  • RUNSTATS
  • SET TAPE POSITION
  • UNQUIESCE DATABASE
  • UPDATE ALERT CONFIGURATION
  • UPDATE CONTACT
  • UPDATE CONTACTGROUP
  • UPDATE DATABASE CONFIGURATION
  • UPDATE DATABASE MANAGER CONFIGURATION
  • UPDATE HEALTH NOTIFICATION CONTACT LIST
  • UPDATE HISTORY

The ADMIN_CMD procedure also supports the following commands not supported by CLP:

  • GET STMM TUNING DBPARTITIONNUM
  • UPDATE STMM TUNING DBPARTITIONNUM

Usage scenario

This scenario describes how a fictional software company, ProTechCo, can use ATS to protect their customer's data.

ProTechCo's flagship product is ProjectPro. ProjectPro is a project management system for Linux. Internally, ProjectPro stores all configuration and project information inside a DB2 database called PROJ. To ensure customers don't lose their valuable data, the ProjectPro installation package includes a shell script that performs a backup of the PROJ database. The script looks like this:

db2 backup database PROJ online to /home/projectpro/backup

Today, the ProjectPro instruction manual encourages customers to execute this script on a regular basis. To improve the usability of their product, ProTechCo wants to eliminate this script entirely. Instead, they want to provide a transparent, weekly backup of the database on Sunday morning at 1 a.m. To do this, the developers at ProTechCo can take advantage of ATS.

Create a task

SYSPROC.ADMIN_TASK_ADD

The built-in SYSPROC.ADMIN_TASK_ADD procedure is used to schedule a new ATS task. Each task executes a catalogued stored procedure. The syntax is provided below.

Listing 1. Syntax for SYSPROC.ADMIN_TASK_ADD
>>-CALL-SYSPROC.ADMIN_TASK_ADD--(--name-,------------------------->

>--+-begin-timestamp-+-,--+-end-timestamp-+-,--------------------->
   '-NULL------------'    '-NULL----------'

>--+-max-invocations-+-,--+-schedule-+-,--+-procedure-schema-+-,-->
   '-NULL------------'    '-NULL-----'

>--procedure-name-,--+-procedure-input-+-,--+-options-+-,--------->
                     '-NULL------------'    '-NULL----'

>--+-remarks-+---------------------------------------------------><
   '-NULL----'

When a task is created, DB2 records the authorization ID of the current session user. The scheduler uses this authorization ID to perform the task. To ensure the procedure can execute successfully, the current session user must have the execute privilege on the task's procedure. If not, the task will fail with an authorization error (SQL0551N).

To perform the backup, ProTechCo uses the ADMIN_CMD procedure in the SYSPROC schema. The procedure input is an SQL statement that is executed and then passed as an input parameter to the ADMIN_CMD procedure. For ProTechCo, the parameter is the following:

'values(''backup database PROJ online to /home/ProjectPro/backup'')'

The begin-timestamp, end-timestamp, max-invocations, and schedule parameters deserve some attention. Together they define the task's execution schedule.

It is easy to understand the first two parameters. Begin-timestamp specifies the earliest time the task can execute. If the begin-timestamp is NULL, the task can execute immediately. Similarly, any recurring task does not start execution once the current time exceeds the specified end-timestamp time. If the task is already running, ATS allows it to continue even if the end-timestamp has passed. If the end-timestamp is NULL, the task has no end time.

The begin-timestamp, end-timestamp, and schedule are based on the server's time zone. Special attention is required when scheduling a task during the transition period of daylight savings time (DST). If the task is scheduled to run 2:01 a.m. and it is the time of year when the time springs forward, the task will not run as the clock skips from 2:00 a.m. to 3:00 a.m. On the other hand, when the time falls back an hour, tasks that were originally scheduled between 2:00 a.m. and 3:00 a.m. will execute twice.

The max_invocations parameter limits the number of times a recurring task can be executed. Regardless of the end-timestamp, all recurring tasks stop executing after they have reached their maximum invocations count. If the parameter is NULL, there is no limit to the number of invocations.

Schedule parameter

The schedule parameter specifies the task's execution schedule. This parameter uses the familiar UNIX cron format. The cron format consists of five fields separated by at least one blank.

Table 1. The allowed values for the time and date fields
FieldAllowed values
Minute0-59
Hour0-23
Day of month1-31
Month1-12, where 1 is January, 2 is February, and so on

Upper-, lower-, or mixed-case three-character strings, based on the English name of the month: jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, or dec
Day of week0-7, where 0 or 7 is Sunday, 1 is Monday, and so on

Upper-, lower-, or mixed-case three-character strings, based on the English name of the day: mon, tue, wed, thu, fri, sat, or sun

The scheduler executes the task when the current date and time match the schedule defined in the scheduler parameter. A range or list of numbers can be used in each of the five fields. Step values can be used in conjunction with ranges. An asterisk can be used to specify all possible field values.

ProTechCo wants to perform the backup task every week on Sunday at 1:00 a.m. In this case, the schedule parameter becomes:

'0 1 * * sun'

Putting it all together, ProTechCo can schedule their weekly backup task with the following commands:

db2 connect to PROJ USER xxxx using xxxx
db2 call SYSPROC.ADMIN_TASK_ADD ( 'PROJ BACKUP', 
                                  NULL,
                                  NULL,
                                  NULL,
                                  '0 1 * * sun',
                                  'SYSPROC',
                                  'ADMIN_CMD',
                                  'values(''backup database PROJ online to 
/home/ProjectPro/backup'') ',
                                  NULL,
                                  NULL )
db2 commit
db2 connect reset

A task cannot be scheduled for execution unless the unit of work is committed. Always remember to issue a COMMIT statement after using ADMIN_TASK_ADD.

The scheduler

Scheduled tasks are executed by DB2's Autonomic Computing Daemon. This daemon process also hosts the Health Monitor and Automatic Maintenance utilities. The daemon starts and stops in conjunction with the database manager. This daemon appears in the process list as db2acd.

Every five minutes the daemon checks for new or updated tasks. To do this, it briefly connects to each active databases and retrieves the new and updated task definitions. If the database uses the database partitioning feature (DPF), the daemon restricts connections to the catalog database partition. The daemon does not connect to inactive databases. To ensure scheduled tasks are executed as expected, the database must remain activate and the task's earliest begin time should be at least five minutes after it is created (or updated).

Internally, the daemon maintains all the active tasks. When a task's scheduled execution time arrives, the daemon connects to the appropriate database and calls the stored procedure associated with the task. If the database is not active, the daemon does not execute the task.

The task list

The SYSTOOLS.ADMIN_TASK_LIST view lists all scheduled tasks.

To see the weekly backup task, the engineers at ProTechCo can use the following query:

select NAME, OWNER, BEGIN_TIME, END_TIME, MAX_INVOCATIONS, 
       SCHEDULE, PROCEDURE_SCHEMA, PROCEDURE_NAME, 
       PROCEDURE_INPUT
from SYSTOOLS.ADMIN_TASK_LIST

The SELECT privilege is required to access the ADMIN_TASK_LIST view. Unless the database was created with the RESTRICTIVE option, the SELECT privilege is granted to PUBLIC by default. Users with SYSADM, SYSCTRL, SYSMAINT, or DBADM privileges, see records for all scheduled tasks. All other users are limited to seeing records for the tasks they added.

Task execution status

As part of their testing effort, the ProTechCo quality assurance team needs to verify that the weekly backup task is performing as expected. This can be done using the SYSTOOLS.ADMIN_TASK_STATUS view. Every time a task is executed, ATS records useful status information about the task. This information is accessible in this view.

To check the status of the PROJ BACKUP task, the ProTechCo team uses the following SQL query.

select NAME, BEGIN_TIME, END_TIME, INVOCATION, STATUS from SYSTOOLS.ADMIN_TASK_STATUS

The BEGIN_TIME and END_TIME columns indicate when the task started and ended, respectively. If there are multiple invocations of the same task, the INVOCATION column can be used to uniquely identify a specific invocation.

The STATUS column indicates the execution status of a particular task invocation. The column contains one of the following values listed below:

  • RUNNING: The task is currently running.
  • COMPLETED: The task has finished running.
  • NOTRUN: The task did not run at the scheduled invocation time.
  • UNKNOWN: The task started running but an unexpected condition prevented the scheduler from recording the outcome. This can occur if a system ends abnormally or power failure happens while the task is running.

The ADMIN_TASK_STATUS view also provides a number of columns with useful diagnostic information.

select TASKID, STATUS, SQLCODE, SQLSTATE, RC, SQLERRMC
from SYSTOOLS.ADMINTASKSTATUS 
where TASKID=34

TASKID STATUS  SQLCODE SQLSTATE RC SQLERRMC 
------ ------- ------- -------- -- --------------------------------------------------- 
34     NOTRUN  -104    42601    0  x'2CFF4E554C4C20415320564152434841522831323829FF29'

The SQLCODE and SQLSTATE columns store the SQLCODE and SQLSTATE values returned by the CALL statement that invoked the task's procedure. If the procedure returned a return code, it appears in the RC column. The SQLERRMC column may contain error description tokens in hexadecimal format. The SQLERRMC column can be difficult to read. To produce a meaningful result, you can use the built-in SQLERRM scalar function to retrieve the SQL message.

select TASKID, STATUS, varchar( sqlerrm( 'SQL' || char( abs(SQLCODE) ),
                                SQLERRMC, x'FF', 'en_US', 1 ), 256) 
                       as MSG_TXT 
from SYSTOOLS.ADMINTASKSTATUS
where TASKID=34

TASKID STATUS MSG_TXT
------ ------ ---------------------------------------------------------------------------
34     NOTRUN SQL0104N  An unexpected token "," was found following "NULL AS VARCHAR(128)"

Users with SYSADM, SYSCTRL, SYSMAINT, or DBADM privileges have access to all status records. Other users only see the status records that belong to the tasks that they added. There will be no records in the view until the task has executed once.

There are other useful columns in the ADMIN_TASK_STATUS view. You can find them documented in the DB2 9.5 Information Center

Updating and removing a task

The engineers at ProTechCo expect a few of their customers won't be satisfied with the default weekly backup schedule. As a result, they intend to enhance their product's graphical user interface to allow their users to define their own the schedule.

Behind the scenes, the ProjectPro software will use DB2's built-in SYSPROC.ADMIN_TASK_UPDATE procedure. This procedure makes it easy to modify the schedule of any existing ATS task. The syntax is very similar to the ADMIN_TASK_ADD procedure:

>>-CALL-SYSPROC.ADMIN_TASK_UPDATE--(--name-,---------------------->

>--+-begin-timestamp-+-,--+-end-timestamp-+-,--------------------->
   '-NULL------------'    '-NULL----------'

>--+-max-invocations-+-,--+-schedule-+-,--+-options-+-,----------->
   '-NULL------------'    '-NULL-----'    '-NULL----'

>--+-remarks-+---------------------------------------------------><
   '-NULL----'

The following statements demonstrate how to change the schedule of ProTechCo's PROJ BACKUP task. After the unit of work is committed, the task executes at 7:30 a.m. on the first and 15th day of every month.

db2 connect to PROJ user xxxx using xxxx
db2 call SYSPROC.ADMIN_TASK_UPDATE ( 'PROJ BACKUP', 
                                     NULL,
                                     NULL,
                                     NULL,
                                     '30 7 1,15 * *',
                                     NULL,
                                     NULL )
db2 commit

The ADMIN_TASK_UPDATE procedure cannot change procedure settings. If you need to change the procedure schema, name, or input parameters, you must remove the task and recreate it.

A task can be removed using the built-in SYSPROC.ADMIN_TASK_REMOVE procedure. This procedure can also remove task status records. The procedure's syntax is provided below:

>>--CALL--SYSPROC.ADMIN_TASK_REMOVE--(--+-name-+-,--->
                                        '-NULL-'

>--+-end-timestamp-+---------------------><
   '-NULL----------'

The following statements remove the PROJ BACKUP and all associated status records:

db2 connect to PROJ user xxxx using xxxx
db2 call SYSPROC.ADMIN_TASK_REMOVE ( 'PROJ BACKUP', NULL )
db2 commit

For more information on the ADMIN_TASK_UPDATE and ADMIN_TASK_REMOVE procedures, see the reference documentation in the DB2 9.5 Information Center.

Schedule examples

As mentioned earlier, the begin_time, end_time, max_invocation, and schedule parameters of ADMIN_TASK_ADD and ADMIN_TASK_UPDATE can be used individually or in combination. When used together, the number of invocations and execution time are bounded. For example:

db2 call SYSPROC.ADMIN_TASK_ADD ( 'MY TASK',
                                  '2029-06-01 9:00:00.000000',
                                  '2030-06-01 8:59:00.000000',
                                  5,
                                  '0 12 1,2,30 6,7 *',
                                  'NEWTON',
                                  'ATS404',
                                  'values(1)',
                                  NULL,
                                  NULL )

Here MY TASK is scheduled to execute at noon on the first, second, and the 30th day of June and July (the sixth and seventh months). Task execution won't begin until 9:00 a.m., June 1, 2029. The task's end-time is one year later on June 1, 2030 at 8:59 a.m. Note that the task has a maximum of five invocations. As a result, the task executes at noon on June 1, 2, and 30, and July 1 and 2. It will never execute on July 30.

The following table contains additional examples of the schedule parameter values.

Table 2. Examples of commonly used schedule
Desired scheduleSchedule Parameter
2:10 p.m. on every Monday10 14 * * 1
Every day at midnight0 0 * * *
Every weekday at midnight0 0 * * 1-5
Midnight on first and 15th day of the month0 0 1,15 * *
6.32 p.m. on the November 17, 21, and 29, plus each Monday and Wednesday in November each year32 18 17,21,29 11 mon,wed

Troubleshooting

If your task does not execute as expected, the first thing you should do is look for a execution status record in the SYSTOOLS.ADMIN_TASK_STATUS view. If there is a record, examine the various values. In particular, pay attention to the STATUS, INVOCATION, SQLCODE, SQLSTATE, SQLERRMC, and RC columns. The values often identify the root cause of the problem.

If there is no execution status record in the view, the task did not execute. There are a number of possible explanations for this:

  • ATS is disabled: Tasks will not execute if ATS is disabled. To enable the scheduler, set the DB2_ATS_ENABLE registry variable.
  • The task was removed: Someone may have removed the task. Confirm the task exists by querying the SYSTOOLS.ADMIN_TASK_LIST view.
  • The scheduler is unaware of the task: ATS looks for new and updated tasks by connecting to each active databases every five minutes. Until this period has elapsed, the scheduler is unaware of your task. Wait at least five minutes.
  • The database is inactive: The scheduler cannot retrieve or execute tasks unless the database is active. Activate the database.
  • The transaction is uncommitted: The scheduler ignores uncommited tasks. Be sure to commit after adding, updating, or removing a task.
  • The schedule is invalid: The task's schedule may prevent the task from running. For example, the task may have already reached the maximum number of invocations. Review the task's schedule in the SYSTOOLS.ADMIN_TASK_LIST view and update if necessary.

Security considerations

For security, the ATS interface restricts task operations to owners and privileged users -- such as those with SYSADM, SYSCTRL, SYSMAINT, and DBABM authority. Other users cannot view or manipulate tasks. DB2's security model also prevents ordinary users from directly tampering with the underlying control tables used by ATS.

Unfortunately, humans are not infallible. History shows that privileged users have committed malicious acts. To safeguard your ATS tasks against this type of abuse, DB2's integrated audit facility can be used to identify tampering. To enable this, a security administrator must define and apply an audit policy to track all SQL executions against the internal tables used by ATS. An example is provided below.

Listing 4. Sample audit command to audit the access of ATS internal tables by privileged user
db2 create audit policy ATSPOLICY categories execute 
           with data status both error type audit
db2 commit
db2 audit table SYSPROC.ADMINTASKS using policy ATSPOLICY
db2 commit
db2 audit table SYSPROC.ADMINTASKSTATUS using policy ATSPOLICY
db2 commit

ATS is tightly integrated with DB2's audit facility. Whenever a task is executed, ATS does the following things:

  • Sets the client application name to "DB2ATS"
  • Sets the client user ID information to the external user ID of the user that created the task. This ID is recorded by the SYSPROC.ADMIN_TASK_ADD stored procedure.
  • Sets the internal user name field to "DB2 Administrative Task Scheduler"

For customers that require stricter controls, Label-Based Access Control (LBAC) can be used to restrict all administrator access to the ATS tables.

Conclusion

ProTechCo can protect their customer's data by transparently performing scheduled backups using DB2's ATS.

ATS enables administrators to automate tasks by scheduling execution of stored procedures. Unlike the Task Center, ATS uses a straightforward SQL interface and operates without the DAS.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=338839
ArticleTitle=Use the SQL-based Administrative Task Scheduler in DB2 9.5 for Linux, UNIX, and Windows
publish-date=09182008