Notice that ADMIN_TASK_LIST shows that ADMIN_UTL_MONITOR is scheduled. However, from ADMIN_TASK_STATUS, you find that pass tickets can’t be generated because DSNTIJRA wasn’t run. Another way of seeing if the monitor and execute stored procedures ran is to issue DB2 command DISPLAY PROCEDURE. Figure 5 shows an example after DSNTIJRA was run.

Another useful feature beginning with DB2 10 lets RUNSTATS use profiles that are stored in table SYSIBM.SYSTABLES_ PROFILES. RUNSTATS can use parameter SET PROFILE, USE PROFILE UPDATE PROFILE and DELETE PROFILE. Data Studio and Optim Query Tuner allow you to use the Stats Advisor, which provides more accurate statistics gathering for the optimizer to choose a better access path. The output from Stats Advisor can be a page or more based on some queries. Instead of executing RUNSTATS with all the subparameters required, you can execute it once, adding the SET PROFILE parameter, and then use parameter USE PROFILE. You can execute RUNSTATS with the PREVIEW option, whereby DB2 only prints the stats profile for each table to SYSPRINT, but normal utility execution doesn’t take place. An alternative is to execute SELECT * FROM SYSIBM. SYSTABLES_PROFILES. Autonomic statistics will use profiles if already set. If not, the process will add an entry into SYSIBM.SYSTABLES_PROFILES for an object requiring RUNSTATS. As part of this process, INDEX(ALL) will be added to the profile table even when an index doesn’t exist in case one is added in the future. The process doesn’t replace Stats Advisor, nor does it integrate with DB2 11 SYSIBM. SYSSTATFEEDBACK and DSN_STAT_ FEEDBACK. For example, you have a column on zip code where 99 percent of the rows are for 11234 and 1 percent is for 23190. Autonomic statistics doesn’t pick up the variation; you still need to run Stats Advisor or use SYSIBM.SYSSTATFEEDBACK and DSN_STAT_FEEDBACK.

Test Examples

Test to illustrate autonomic statistics—five table spaces with no indexes—JOHNITB4 was manually added to table SYSTABLES_ PROFILES via RUNSTATS SET PROFILE without INDEX(ALL), all others automatically added by autonomic statistics:

• JOHNITS1 inserted one row followed by regular RUNSTATS TABLE(ALL); the result: Auto RUNSTATS wasn’t executed.
• JOHNITS2 inserted many rows without any RUNSTATS; the result: Auto RUNSTATS executed.
• JOHNITS3 inserted many rows with a variation, 99 percent zip code 11234, 1 percent with 23190 followed by regular RUNSTATS TABLE(ALL); the result: Auto RUNSTATS didn’t execute. Auto stats doesn’t pick up the variation, use Stats Advisor or SYSIBM.SYSSTATFEEDBACK and DSN_STAT_FEEDBACK.
• JOHNITS4 inserted many rows with a variation, 99 percent zip code 11234, 1 percent with 23190 followed by special RUNSTATS with COLGROUP for the zip code and SET PROFILE, then double the size of the table with no RUNSTATS; the result: Auto RUNSTATS executed.
• JOHNITS5 inserted many rows followed by regular RUNSTATS TABLE(ALL), then DELETE from table (mass delete) with no RUNSTATS; the result: Auto RUNSTATS didn’t run. WARNING: RUNSTATS should have run because RTS shows mass deletes>0. See APAR PM95437. Without this fix, autonomic statistics is ignoring table spaces with an RTS datasize of zero.

Figure 6 shows partial results from SELECT *. Autonomic statistics and manual RUNSTATS can be run together, but special considerations must be made for the profile used. For more information, review the DB2 Utilities manual section titled “Combining autonomic and manual statistics maintenance.”

You can use your operating system’s scheduler instead of DB2’s built-in ADMT started task for autonomic statistics. In this case, pass the DB2 parameters to your operating system’s scheduler and schedule the ADMIN_UTL_MONITOR and the ADMIN_ UTL_EXECUTE stored procedures with the standalone parameter. Be aware that in this case, ADMIN_UTL_EXECUTE can’t be scheduled by ADMIN_UTL_MONITOR or ADMIN_UTL_EXECUTE to resolve the alerts. You must schedule ADMIN_UTL_ EXECUTE to run to resolve alerts.

4 Pages