Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS

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