Over the past several years, autonomic statistics has been a much-discussed feature of DB2 10 and 11. The vast majority of customers we talk with are very excited about this feature because it solves the often-asked question: Why can’t DB2 be smart enough to automatically execute RUNSTATS when required? However, after revisiting with some of these customers, we were surprised to learn that once the excitement died down, none had implemented autonomic statistics. Most of the feedback pointed to the implementation being confusing and cumbersome.
So, unless you’re using a tool, such as the IBM DB2 Automation Tool, which makes the implementation easier, you will need to learn about stored procedures, new DB2 tables, user-defined functions (UDFs) and the UNIX cron date and time format. Your interaction with the process mostly involves four tables, two UDFs, three stored procedures and DB2’s ADMT started task or your operating system scheduler.
Let’s take a look at how to implement and monitor autonomic statistics.
Stored procedures can be challenging unless you already have experience with them or program in C, Java or some other language. As part of the implementation, you will run one stored procedure via a call to add another stored procedure. Based on the results, you may run a third stored procedure. Most of the examples in the DB2 manuals are in C and Java and aren’t direct calls to the stored procedure. Unfortunately, you can’t use a batch TMP to call a stored procedure, but there are alternatives: IBM Data Studio or IBM Optim Query Tuner. With these products, you can call a stored procedure using the Data perspective followed by executing Run SQL, which invokes a SQL script. The call statement and the results will be displayed on the same screen and can be saved.
Stored procedures can also be very finicky. You must be careful when coding quotes (‘ single vs. ‘’ double-single vs. “ double). Double-single and double quotes in most instances look the same. Parts of the stored procedures will only work with one type but not the other. You may get an error message that doesn’t explain the reason for the error. However, changing a double quote to a single quote or vice versa may help resolve errors. When you see userid and password in the stored procedure syntax, you must surround with quotes a valid TSO userid and password that has enough authority.
The Install Process
The first part of the process is to determine when RUNSTATS will execute using table SYSIBM. SYSAUTOTIMEWINDOWS. If you’re installing DB2 10 or a subsequent release, DSNTIJTC creates SYSAUTOTIMEWINDOWS. If you’re migrating to DB2 10, DSNTIJEN (ENFM) creates it in step ENFM0001 – ENFM start. It’s part of table space SYSTSATW with index DSNTWX01. If DB2 is running V10 NFM or higher and SYSIBM. SYSAUTOTIMEWINDOWS is empty, DSNTRIN in job DSNTIJRT will initialize it with seven rows of data. Rows inserted can be for day of the week or month with start and end times, number of parallel RUNSTATS tasks and SSID to execute on. If the rows exist, there’s no reason to manually add the rows; you’re ready to run unless you want to modify the schedule. By default, this table already has seven rows inserted that tell the scheduler RUNSTATS can execute any day of the week (one row for each day of the week) and at any time, with a maximum of one task (don’t run in parallel). The WINDOW_ID column is 1 through 7, one per row.
The next step is adding and setting up the stored procedures. The first stored procedure, ADMIN_UTL_MONITOR, determines which objects require RUNSTATS based on out-ofdate, missing and inconsistent statistics. Alerts are written to table SYSIBM. SYSAUTOALERTS and it will schedule another stored procedure, ADMIN_UTL_ EXECUTE, to execute RUNSTATS based on the alerts. Options for ADMIN_UTL_ MONITOR include a schedule when to run, any object restrictions in terms of include or exclude, the statistics scope, if it is to run standalone, RUNSTATS sampling and thresholds. Object restrictions, for example, can exclude for monitoring any database starting with DSNDB01 (DB2 Directory) and DSNDB06 (DB2 Catalog). Another example: Only include for monitoring any database with the name TESTTHIS having table spaces starting with ‘AUTOST’. Three scopes— BASIC, PROFILE or PROFILECONSISTENCY— tell the monitor how detailed it should be when looking to resolve problems. Set stand-alone=yes if you would like to review the alerts and not execute RUNSTATS or you’re using a different scheduler outside of the ADMT started task and manually want to schedule ADMIN_ UTL_EXECUTE. The thresholds deal with RTS as well as inconsistencies, depending on the scope used. For the RTS, only SYSTABLESPACESTATS is interrogated, not SYSINDEXSPACESTATS.
Autonomic statistics first made its debut in DB2 LUW, which is the reason the UNIX cron date and time format are used for the stored procedures. Table SYSIBM. SYSAUTOTIMEWINDOWS, on the other hand, tells the scheduler when to execute RUNSTATS and uses the regular date and time format. Additional information on autonomic statistics can be found in the DB2 LUW manuals. For example, for the UNIX cron format, review DB2 Version 9.5 for Linux, UNIX and Windows, Database Administration. More than one monitor can be set. Figure 1 shows two examples: ADMIN_UTL_MONITOR_1, which has a scope of BASIC and runs daily every 30 minutes on the hour, and ADMIN_UTL_ MONITOR_2, which has the more extensive scope of PROFILE-CONSISTENCY and runs daily only at 1 a.m.