[DB2 LUW] Sample administrative task schduler ADMIN_TASK_ADD and ADMIN_TASK_REMOVE usage

Body

QUESTION:

Could you guide us how to use administrative task scheduler ADMIN_TASK_ADD and ADMIN_TASK_REMOVE?
We found each usage at KC but could not locate any sample usage case at the internet.

ANSWER:

Before using it, it is suggested to review below KC page for finding out the outline.
The administrative task scheduler
https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.gui.doc/doc/c0054380.html
...
The administrative task scheduler enables DB2R database servers to automate the execution
of tasks. It also provides a programmable SQL interface, which allows you to build
applications that can take advantage of the administrative task scheduler.
...

Roughly speaking, we could create TASKs and find out STATUS of the TASKs.  Once it is
not needed any more, we could delete the STATUS of TASKs and/or the TASKs.

Here is a sample using scenario.

------------------------------
1. Add three TASKs
------------------------------
$ date
  ==> find out current time. let us say 2017-06-20-23:00:00
  ==> set more than 10 min later for admin_task_add
  ==> such as 10, 11 and 12

$ db2set DB2_ATS_ENABLE=YES
$ db2start
$ db2sampl
$ db2 -v "connect to sample"

# for V10.1, need below too
# db2 -v "CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4"

# must use "messages on server" instead get an error with admin_task_add + admin_cmd
# check the path permission.  May need to run chmod 777 /home/hidehy to write staffX.del
# TEST1, TEST2 and TEST3 tasks will be added
$ db2 -v "CALL SYSPROC.ADMIN_TASK_ADD ('TEST1', CURRENT_TIMESTAMP, NULL, NULL, '10 * * * *', 'SYSPROC', 'ADMIN_CMD', 'VALUES(''export to /home/hidehy/staff1.del of del messages on server select * from staff'')', NULL, NULL )"
$ db2 -v "CALL SYSPROC.ADMIN_TASK_ADD ('TEST2', CURRENT_TIMESTAMP, NULL, NULL, '11 * * * *', 'SYSPROC', 'ADMIN_CMD', 'VALUES(''export to /home/hidehy/staff2.del of del messages on server select * from staff'')', NULL, NULL )"
$ db2 -v "CALL SYSPROC.ADMIN_TASK_ADD ('TEST3', CURRENT_TIMESTAMP, NULL, NULL, '12 * * * *', 'SYSPROC', 'ADMIN_CMD', 'VALUES(''export to /home/hidehy/staff3.del of del messages on server select * from staff'')', NULL, NULL )"

$ db2 -v "terminate"
------------------------------

------------------------------
2. Find out TASKs and its status
------------------------------
# before 23:10, run below to list up three TASKs only by admin_task_list and no result by admin_task_status.
# after 23:12, run below to list up three TASKs by admin_task_list and also those status by admin_task_status.
$ db2 -v "connect to sample"
$ db2 -v "select char(name, 20) as Name, begin_time, end_time, char(schedule, 20) as Schedule from systools.admin_task_list"
$ db2 -v "select char(name, 20) as Name, begin_time, end_time, status, agent_id from systools.admin_task_status"
$ db2 -v "terminate"
------------------------------

------------------------------
3. Modify the TASKs for another run
------------------------------
# after 23:12, modify and update current TEST1, TEST2 and TEST3 tasks
# originally configured every 10, 11 and 12.  Change those to 30, 31 and 32.
$ db2 -v "CALL SYSPROC.ADMIN_TASK_UPDATE('TEST1', NULL, NULL, NULL, '30 * * * *', NULL, NULL)"
$ db2 -v "CALL SYSPROC.ADMIN_TASK_UPDATE('TEST2', NULL, NULL, NULL, '31 * * * *', NULL, NULL)"
$ db2 -v "CALL SYSPROC.ADMIN_TASK_UPDATE('TEST3', NULL, NULL, NULL, '32 * * * *', NULL, NULL)"

# at this point, run step 2 to list up three TASKs and three status
# after 23:32, run step 2 to list up three TASKs and six status
------------------------------

------------------------------
4. Delete TASKs and STATUS
------------------------------
# delete all STATUS less than below timestamp and keep all TASKs
# may need to specify six nano digits as 000000.
db2 -v "CALL SYSPROC.ADMIN_TASK_REMOVE(NULL,'2017-07-01-00.00.00.000000')"  

# delete all STATUS less than now and keep all TASKs
db2 -v "CALL SYSPROC.ADMIN_TASK_REMOVE(NULL, current_timestamp)"  

# detete all TEST2 TASK's STATUS less than now and keep all TASKs
db2 -v "CALL SYSPROC.ADMIN_TASK_REMOVE('TEST2', current_timestamp)"

# delete all TASKs and delete all STATUS. Please take a backup before do this.
db2 -v "CALL SYSPROC.ADMIN_TASK_REMOVE(NULL, NULL)"
------------------------------

* References:

ADMIN_TASK_ADD procedure - Schedule a new task
ADMIN_TASK_LIST administrative view - Retrieve information about tasks in the scheduler
ADMIN_TASK_REMOVE procedure - Remove scheduled tasks or task status records
ADMIN_TASK_STATUS administrative view - Retrieve task status information
ADMIN_TASK_UPDATE procedure - Update an existing task
ADMIN_CMD procedure - Run administrative commands
EXPORT command using the ADMIN_CMD procedure