IBM Support

Why is the QDBFSTCCOL Taking CPU?

Troubleshooting


Problem

QDBFSTCCOL is the system statistics collection job which will, occasionally, use CPU when statistics need to be refreshed.
The QDBFSTCCOL job will use more CPU and I/O resources when refreshing statistics on very large files, as compared to smaller files.
 

Symptom

Low priority threads, associated with the QDBFSTCCOL job, start using CPU and I/O. At the same time, a user experiences a performance problem and becomes concerned that the QDBFSTCCOL job may be the culprit.

 

Cause

A new file is introduced to the system, such as with a large restore.  Statistics will need to be created if a new file is introduced to the system.
Existing statistics became stale due to records being added or changed. QDBFSTCCOL refreshes stale statistics.
Running a set of new queries on the system. New queries might require more statistics to be collected. You might want to run the queries once, let the QDBFSTCCOL finish collecting new statistics, and run the queries again. The query optimizer might be able to choose a better implementation once the statistics are available.

Diagnosing The Problem

The QDBFSTCCOL job becomes more noticeable and concerning, to system administrators, when it starts using CPU and I/O. However, CPU and I/O consumption alone, are not an indication of a problem. The software support representatives, in the IBM i Global Support Center, rarely see the QDBFSTCCOL job impacting performance of other jobs.
Additionally, the low priority tasks will throttle themselves if the disks are too busy or the CPU usage is too high. Higher priority work will have CPU priority.  Furthermore, the findings, of an investigation into the user jobs that are performing poorly, rarely show the statistics collector job as the root cause. Nearly all of our investigations show that a new query workload, an inefficient query plan has been introduced into the workload, or something else is the culprit. The performance impact is typically caused by the change in workload that triggers the QDBFSTCCOL tasks, and not the tasks themselves. 
 

Resolving The Problem

It is important to allow the job to use CPU when needed, to maintain accurate statistics.  Clients that disallow statistics collection will experience very poor performance typically across many business critical queries. Do not immediately change the QDBFSTCCOL system value to *NONE.
 
If it is determined that the system job is negatively impacting system performance
o Contact the IBM i Global Support Center to collect data for further analysis. 
 
o Upload Collection Services, Job Watcher (if you have it already) and QDBFSTCCOL joblog to the case along with timestamps of the problem and user job names that are experiencing slowness.
For the joblog:
    a) CRTLIB TSXXXXXXOQ
    b) CRTSAVF FILE(TSXXXXXXOQ/TSXXXXXXOQ) TEXT(QDBFSTCCOL)
    c) CRTOUTQ OUTQ(TSXXXXXXOQ/CASEOUTQ)
    d) WRKJOB JOB(XXXXXX/QSYS/QDBFSTCCOL) OUTPUT(*PRINT) OPTION(*JOBLOG)
    e) WRKSPLF and take option 2 beside the joblog just created and change the outq to the one just created.
    f) SAVOBJ OBJ(CASEOUTQ) LIB(TSXXXXXXOQ) DEV(*SAVF) OBJTYPE(*OUTQ) SAVF(TSXXXXXXOQ/TSXXXXXXOQ) TGTRLS(*CURRENT) SPLFDTA(*ALL)
        DTACPR(*MEDIUM)
 
To send in the joblog you can include the library in the system snapshot command (example below)
 

The following instructions assume MGTOOLS is on the system, If not, you can follow these instructions to install:
https://www.ibm.com/support/pages/mustgather-how-obtain-and-install-qmgtools-and-keep-it-current

We want to makes sure we have the latest version of MGTOOLS:
QMGTOOLS/CMPVER then F6=Download and update

This command assumes the issue happened within the last 2 days and the joblog is in the library from above. Make the appropriate substitutions.

QMGTOOLS/SYSSNAP OUTPUT(*IFS)
CSDATA(Y)
DAYSPRV(2)
LIB2SAV1(TSXXXXXXOQ) 

Step 4 from this link is a good resource on how to send the zip file to the case
https://www.ibm.com/support/pages/node/6574525

 
If you cannot open a case, then proceed with the following:
o Review the QDBFSTCCOL joblog to determine which file(s) are being refreshed. These files would have a large percentage of rows changing and, the larger the file, the more CPU and I/O resources will need to be used by the QDBFSTCCOL job. Consider blocking statistic collections for the file(s) and consider how this will impact the queries over the file.  Individual files can be excluded from stats collection via Navigator for i/Access Client Solutions.
 
o Review SYSCOLUMNSTAT view for more information
CREATE TABLE QGPL.MSGSTATS as (select * from qsys2.syscolumnstat where table_schema = 'yourlib' and table_name = 'yourfile') with data;
 
o Carefully consider a temporary work-around of changing the QDBFSTCCOL system value to *USER or *NONE. The CPU might not decrease immediately after a system value change; it takes at least a few minutes or more.
 

CAUTION:

The side effect of changing the system value is that the database statistics will not be refreshed and can be stale. Stale statistics can cause the SQL Query Optimizer to make ineffective decisions about how to implement your SQL queries and cause them to run longer. In addition, the processing and resource usage might be more noticeable when the QDBFSTCCOL system value is turned back on versus if the system were allowed to collect statistics over the entire day. The system builds statistics for what is in the plan cache so if the system value is toggled, on a regular basis, there might be a set of queries that only run a few times during the day that might never have appropriate statistics gathered.


For more information, refer to the IBM DB2 for i Indexing and Statistics Strategies white paper or the IBM Documentation Center
 

[{"Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m3p0000006x0lAAA","label":"Performance-\u003ESolution Documents"}],"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Type":"MASTER"}]

Historical Number

512435386

Document Information

Modified date:
23 November 2025

UID

nas8N1013253

Page Feedback