Chapter 4. Statistics Manager 71
Figure 4-6 Example of statistics and indexes working together
Let us assume that the database analyst decides to create an index by Customer_No. On the
third run of the query (Q1), the query is optimized with the statistic and the index, and the
optimizer may decide to use the index in the implementation of the query.
The example presented in this section illustrates how both concepts complement each other.
4.1.6 Monitoring background statistics collections
System value QDBFSTCCOL controls who is allowed to create statistics in the background.
To display the value of QDBFSTCCOL, on the command line, type:
DSPSYSVAL SYSVAL(QDBFSTCCOL)
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.
򐂰 *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, it does not
mean that Statistics Manager does not place information about the column statistics it wanted
into the plan cache. It simply means that these column statistics are not gathered. When
switching the system value back to *ALL, for example, this initiates some background
processing that analyzes the entire plan cache and looks for any column statistics requests
that are there. This background task also checks which column statistics are used by any plan
No Index
No Stat
Q1 optimizes
with defaults,
runs without
index
Queue up
stats request
for Customer_No
Q1 = ...WHERE Customer_No = 112358 GROUP BY Customer_No...
No Index
Stat
Q1 optimizes
with stat,
runs without
index
Index
Stat
Q1 optimizes
with stat and
index, runs with
index
Generate
Stats for
Customer_No
Time
Create index for
Customer_No
Is there a
difference in
performance?
Note: User-initiated pending background requests are visible, for example, through the
iSeries Navigator graphical user interface (GUI) (Viewing Statistics Collection Requests)
and can be removed from that queue via the same interface.
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:
WRKJOB JOB(QDBFSTCCOL)
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
System: AS09
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
Bottom

Get Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.