DB2 & IMS

You can schedule autonomic statistics in a data sharing environment. For the SYSAUTOTIMEWINDOWS table, specifying NULL for DB2_SSID would allow stored procedures ADMIN_UTL_MONITOR and ADMIN_UTL_EXECUTE to run on any member. Each stored procedure can run on different members. Keep in mind, ADMT’s TASKLIST data set and database DSNADMDB are shared by all members; therefore, all members know that one of the members ran the task. For a different take, let’s say you have a data sharing group with three members: DB2A, DB2B and DB2C. Let’s say you want ADMIN_UTL_MONITOR to run on DB2B only, but ADMIN_UTL_ EXECUTE to run on any member. For the SYSAUTOTIMEWINDOWS table, specifying DB2B for DB2_SSID would allow stored procedure ADMIN_UTL_MONITOR to run on member DB2B only, but ADMIN_UTL_ EXECUTE will run on any member. If you want both stored procedures ADMIN_UTL_ MONITOR and ADMIN_UTL_EXECUTE to run on DB2B only, invoke both with standalone= yes and use ADMIN_UTL_ADD to manually run ADMIN_UTL_EXECUTE. In this case, ADMIN_UTL_EXECUTE will require very low overhead to periodically check the window set in SYSAUTOTIMEWINDOWS.

Considerations for Autonomic Statistics

When looking at using autonomic statistics, it’s important to consider:

• On what schedule should RUNSTATS potentially execute?
• What happens if a RUNSTATS starts near the end of the time schedule and now has to run for an extensive period?
• How many RUNSTATS should run simultaneously?
• If running in a data sharing environment, on which member should RUNSTATS and the monitor execute?
• How often should the monitor stored procedure run?
• Are there objects that require special statistics?
• Which objects should be included or excluded for analysis?
• Should objects recommended for RUNSTATS execute or just be reported on?
• Which statistics scope should be run on which schedule?
• Will the ADMT scheduler be used or an operating system one?
• How often should the log file and alert history be cleaned up?
• How will the log file and alert history be reviewed for unsuccessful executions? What action should be taken?
• RUNSTATS of objects helped (or hurt) dynamic SQL, but what will you do about static and REBIND?
• Is DSNACCOX also run? If it is, how will it be used alongside autonomic statistics?
• How will I deal with auto stats running at the same time as my maintenance window for REORG then inline stats? How about scheduled RUNSTATS?

Summary

Clearly, autonomic statistics has many moving parts, but it does provide the answer to the often asked question: Why can’t DB2 be smart enough to automatically execute RUNSTATS when required? Now it can.

Resources

Although documentation is limited for setting up and using autonomic statistics, some helpful resources include:

• DB2 Installation and Migration Guide; review sections regarding the auto stats stored procedures.
• DB2 Managing Performance; see section “Automating statistics maintenance.”
• DB2 SQL Reference; see section “DB2 catalog tables.” Review SYSIBM. SYSAUTOALERTS, SYSIBM. SYSAUTORUNS_HIST and SYSIBM. SYSAUTOTIMEWINDOWS and table functions ADMIN_TASK_LIST and ADMIN_TASK_STATUS.
• DB2 Utilities manual; see section “Combining autonomic and manual statistics maintenance.”
• DB2 10 for z/OS Technical Overview Redbook; see sections on DB2-supplied stored procedures, administrative task scheduler, administration enablement, DB2 statistics routines, autonomic statistics, using RUNSTATS profiles, updating RUNSTATS profiles, deleting RUNSTATS profiles, and combining autonomic and manual statistics maintenance.
• DB2 Version 9.5 for Linux, UNIX and Windows, Database Administration; see section “Administrative Task Scheduler routines and views.”

4 Pages