72 Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
in the plan cache. It checks whether these column statistics have become stale. Requests for
new column statistics and for refresh of column statistics are then executed.
For a user to manually request column statistics collection, the user must have *OBJOPR and
*OBJALTER authority for the underlying table.
All background statistics 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 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:
Priority 90 is for short user requests.
Priority 93 is for long user requests.
Priority 96 is for short system requests.
Priority 99 is for long system requests.
To view the number of threads that are set up, follow these steps:
1. Type the following command:
2. Select menu option 20 to work with threads. Figure 4-7 shows the Work with Threads
display that opens.
Figure 4-7 Threads for the QDBFSTCCOL job
Background versus immediate statistics collection: Background statistics collections
attempt to use as much parallelism as possible. This parallelism is independent of the
symmetric multiprocessing (SMP) feature installed on the System i platform. 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.
Work with Threads
Job: QDBFSTCCOL User: QSYS Number: 001673
Type options, press Enter.
3=Hold 4=End 5=Display attributes 6=Release 10=Display call stack
11=Work with thread locks 14=Work with thread mutexes
Total Aux Run
Opt Thread Status CPU I/O Priority
00000001 EVTW .263 290 50
00000009 DEQW .132 185 99
00000008 DEQW .185 201 99
00000007 DEQW .292 606 96
00000006 DEQW .232 585 96
00000005 DEQW .277 523 96
00000004 DEQW .000 0 93
00000003 DEQW .000 0 90
00000002 DEQW .000 1 90