You can also schedule the monitor stored procedure to execute at different times for different objects. For example, let’s say we want all eligible objects to execute RUNSTATS 24 hours a day, seven days a week, with two exceptions: Eligible objects for database ABC should only execute from 1 a.m. to 3 a.m., and from 7 p.m. to 9 p.m. for database XYZ. One way of accomplishing this is to have three monitor stored procedures using the restrict-ts parameter: one that executes at periodic intervals for all objects, excluding databases ABC and XYZ; one that’s used for database ABC only with a specific timeframe; and one that’s used for database XYZ only with a specific timeframe.
You must use stored procedure ADMIN_ TASK_ADD to first add ADMIN_UTL_ MONITOR to execute. In this case, you’re calling one stored procedure to add another stored procedure. Let’s say, for example, you want to run ADMIN_UTL_MONITOR_2 daily at 6 a.m. instead of at 1 a.m. In this case, you need to call a different stored procedure, ADMIN_UTL_UPDATE. You will notice when looking up the syntax that unlike ADMIN_UTL_ADD, ADMIN_UTL_ UPDATE doesn’t have parameters for userid and password. The admin scheduler checks the password at ADMIN_TASK_ADD time. If it’s valid, it stores the task in the task list without the password. Internally, a different approach is used to switch to the context of the user (it runs APF-authorized and in key 0), so there’s no need to know the password of the user. Scheduled jobs shouldn’t be affected when a user changes his or her password.
You’re now running two monitor stored procedures daily: ADMIN_UTL_ MONITOR_1 runs every 30 minutes on the hour with a basic scope and ADMIN_UTL_ MONITOR_2 runs once a day with a detailed scope. Let’s say you decide you no longer need to run the more detailed monitor. In this case, you call a different stored procedure: CALL sysproc.admin_task_remove(‘ADMIN_UTL_ MONITOR_2’,0,’’). Two UDFs ADMIN_ TASK_LIST and ADMIN_TASK_STATUS are provided to view the status of the stored procedures. After the removal of ADMIN_ UTL_MONITOR_2, you will see the partial output in Figure 2.
Notice that ADMIN_UTL_MONITOR_2 isn’t listed, but ADMIN_UTL_MONITOR_1 and ADMIN_UTL_EXECUTE are. The full output will include such things as the last execution date and time. If you were to remove ADMIN_UTL_MONITOR_1 as well, you would still see a listing in both SELECTs for ADMIN_UTL_EXECUTE even though it’s no longer running. The last run date and time will show the time just prior to the removal of ADMIN_UTL_MONITOR_1 or the last time it was invoked to run based on the monitor results.
Alerts from the ADMIN_UTL_MONITOR stored procedure are written to table SYSIBM. SYSAUTOALERTS, which feeds the input for stored procedure ADMIN_UTL_EXECUTE. You don’t use ADMIN_UTL_ADD to add ADMIN_UTL_EXECUTE unless you’re running with the stand-alone=yes option, which will be discussed later. The specifics of the RUNSTATS execution output from stored procedure ADMIN_UTL_EXECUTE are written to table SYSIBM.SYSAUTORUN_HIST.
The third step (optional) is to add a stored procedure to clean up these two tables based on a date criteria. For example, Figure 3 shows you how to remove rows on the first day of the month that are more than 30 days old.
When using the ADMT started task to schedule autonomic statistics, just the fact the STC is running isn’t enough; you must also run the optional install job DSNTIJRA to provide the RACF security for pass tickets; otherwise, you will see the partial output in Figure 4.