Blog
DB2utor

Categories

March 15, 2011

Preview Runstats Profile

Numerous options can be used when running a runstats utility. But one thing that customers have long asked for -- the capability to interrogate the current statistics to deterimine which parameters were used the previous time runstats was executed -- hasn't been an option. That is, until now, because IBM has developed a solution.

As of DB2 10 for z/OS, the runstats utility can store the parameters it uses for a given schema and table. Runstats can also now be used to determine when the profile was created and the last time it was used.

The profile information is stored in table SYSIBM.SYSTABLES_PROFILES. You can query the table directly to see the profiles that have been generated, or you can run runstats with the PREVIEW option. In preview mode, the gathering of new statistics is bypassed and the profile information is written out to SYSPRINT DD.

To give you an example of how this works, I excuted runstats using this parameter:

RUNSTATS TABLESPACE (COLTR05D.COLTR051)
         TABLE (COLTR05.ADDRESS_TABLE) 
         COLUMN (CITY,STATE)           
         INDEX (ALL)                   
         SHRLEVEL REFERENCE UPDATE ALL 
         SET PROFILE                   

To preview the information I just generated, I used these parameters:

OPTIONS PREVIEW
RUNSTATS TABLESPACE (COLTR05D.COLTR051)
         TABLE (COLTR05.ADDRESS_TABLE) 
         COLUMN (CITY,STATE)           
         INDEX (ALL)                   
         SHRLEVEL REFERENCE UPDATE ALL 
         SET PROFILE                   

In the following PDF you'll find the SYSPRINT DD output (Figure 1) and sample query output (Figure 2): Download Mfblog_coleman031511_figure1 and 2

I think it would have been nice if IBM made SET PROFILE a default to ensure you can view which parameters used for the runstats are available. However, it's up to you to ensure production JCL is changed to include the SET PROFILE parameter.