Db2

 View Only

 PD_GET_DIAG_HIST OPTSTATS events

Mark Gillis's profile image
Mark Gillis IBM Champion posted 05/13/26 12:03 PM

I am trying to get some information on the Event Types (specifically the SECOND_EVENTQUALIFIER) passed back from a query against the PD_GET_DIAG_HIST table function using OPTSTATS. 

"User" and "Asynchronous" are pretty clear; the former is a manually submitted command and the latter is the result of the Auto_Runstats setting being invoked.

"Fabricate" and "Fabricate Partial" I'm not so clear on; I can see some information in the manual they are initiated at SQL compile time, use data from the data and index manager and do NOT store the results in the system catalogues. But I can't find out why and when specifically they are initiated (surely not every SQL compile or we'd see millions).

"Synchronous" and "Synchronous Sampled" similarly not entirely clear. The main difference with the previous Fabricate options is that the results ARE stored in the system catalogues.

The reason I am trying to understand these at the moment is that we have a high volume of Synchronous sampled COLLECT events recorded that fail due to timeouts. I have a TableRunstatsPolicy defined which excludes certain highly transactional tables but this appears to be by-passed by the Fabricate and Synchronous stats collection as they still appear in a query against PD_GET_DIAG_HIST.

So, to reiterate, I guess my question(s) are

  • what initiates the Fabricate and Synchronous actions and
  • how do I stop them for specific tables?

Any thoughts, help or comment appreciated

Regards

Mark Gillis

Mark Barinstein's profile image
Mark Barinstein posted 05/13/26 01:43 PM

Hello Mark,

According to the Automatic statistics collection link:

  • what initiates the Fabricate and Synchronous actions and


Automatic statistics collection can occur in two ways:
- For synchronous collection at statement compilation time, you can use the real-time statistics (RTS) feature. The auto_stmt_stats database configuration parameter is used for RTS statistics collection and RUNSTATS activity logging.

  • how do I stop them for specific tables?

Synchronous statistics collection and fabrication do not use the online maintenance window that you specify in your maintenance policy, because synchronous requests must occur immediately and have limited collection time. Instead, synchronous statistics collection and fabrication uses to the policy that specifies the set of tables that are within the scope of automatic statistics collection.

So, if you see that despite you set your runstats policy (BTW, it's called DB2AutoRunstatsPolicy, not TableRunstatsPolicy) with some filter (RunstatsTableScope) sync runstats violating this filter do occure, that this may mean, that it's either some bug or you defined some incorrect policy...

Mark Gillis's profile image
Mark Gillis IBM Champion posted 05/14/26 05:23 AM

Thanks for the info Mark.

That seems to confirm that there isn't really any way of controlling the Fabricate and Synchronous actions. If the DB CFG settings are enabled to allow AUTO RUNSTATS then it allows these actions also.

Disabling them for specific tables in this scenario might then be a bug. The runstats policy (BTW I referred to it as TableRunstatsPolicy as that is what it is called in SYSTOOLS.POLICY although I realise the XML doc is called DB2AutoRunstatsPolicy. And when you update it using AUTOMAINT_SET_POLICYFILE it is called AUTO_RUNSTATS. Bit confusing) simply uses
 <RunstatsTableScope>
    <FilterCondition>TABNAME NOT IN (VALUES 'PCL_PROG','PCL_HIST') </FilterCondition>
</RunstatsTableScope>
I believe that this works as there no actions appearing in the query against the PD_GET_DIAG_HIST table function for these tables except Fabricate and Synchronous actions. It's hard to be entirely sure as there is no syntax checking when you upload a policy, but given that I can see no User or Asynchronous  activity for these tables, and a great deal of activity for the other application tables, I believe it to be operating as intended.

So, if the policy specifying two tables to exclude should exclude all auto statistics gathering including Fabricate and Synchronous actions, then this might be a bug.

What do you think?

Regards

Mark Gillis

Mark Barinstein's profile image
Mark Barinstein posted 05/14/26 09:19 AM

Mark,

I believe that this works as there no actions appearing in the query against the PD_GET_DIAG_HIST table function for these tables except Fabricate and Synchronous actions. 

Seems, that it's either a bug in the documentation (which states that "synchronous statistics collection and fabrication uses to the policy that specifies the set of tables that are within the scope of automatic statistics collection") or a bug in Db2 code.

I believe that it's better to open a Case with IBM Support to shed some light on this discrepancy...

Mark Gillis's profile image
Mark Gillis IBM Champion posted 05/19/26 05:25 AM

FYI: Case raised with IBM Support ref TS022209004

Related Content