Monitoring background statistics collection

 

The system value QDBFSTCCOL controls who is allowed to create statistics in the background. The following list provides the possible values:

*ALL

Allows all statistics to be collected in the background. This is the default setting.

*NONE

Restricts everyone from creating statistics in the background. This does not prevent immediate user-requested statistics from being collected, however.

*USER

Allows only user-requested statistics to be collected in the background.

*SYSTEM

Allows only system-requested statistics to be collected in the background.

When you switch the system value to something other than *ALL or *SYSTEM, the Statistics Manager continues to place statistics requests in the Plan Cache. When the system value is switched back to *ALL, for example, background processing analyzes the entire Plan Cache and looks for any column statistics requests that are there. This background task also identifies column statistics that have been used by an plan in the Plan Cache and determines if these column statistics have become stale. Requests for the new column statistics as well as requests for refresh of the stale columns statistics are then executed.

All background statistic collections initiated by the system or submitted to the background by a user are performed by the system job QDBFSTCCOL (user-initiated immediate requests are run within the user's job). This job uses multiple threads to create the statistics. The number of threads is determined by the number of processors that the system has. Each thread is then associated with a request queue.

There are four types of request queues based on who submitted the request and how long the collection is estimated to take. The default priority assigned to each thread can determine to which queue the thread belongs:

Background statistics collections attempt to use as much parallelism as possible. This parallelism is independent of the SMP feature installed on the iSeries™. However, parallel processing is allowed only for immediate statistics collection if SMP is installed on the system and the job requesting the column statistics is set to allow parallelism.

 

Parent topic:

Collecting statistics with the Statistics Manager

 

Related information


Allow background database statistics collection (QDBFSTCCOL) system value