Colleagues,

Consider the following snippets of a snapshot:

Number of executions               = 13
Number of compilations             = 4294967295
Rows read                          = 415532
Buffer pool data logical reads     = 13741
Buffer pool data physical reads    = 0
Buffer pool temporary data logical reads   = 0
Buffer pool temporary data physical reads  = 0
Buffer pool index logical reads    = 0
Buffer pool index physical reads   = 0
Buffer pool temporary index logical reads  = 0
Buffer pool temporary index physical reads = 0

The query in question goes against three tables, two M:N parents
resolved by an intersection table. Two of the tables (one parent and
the intersection) have 14,000-plus rows, and the third has 1,200-plus,
for a total of a bit more than 31,000 rows total. There are no
predicates in this query other than ON clauses for the joins. In other
words, DB2 does FTS's of all three tables, which is what I'd expect.
Also, the query runs several times per day.

Looking at the total number of "rows read" reported above (415,532)
and dividing it by the "number of executions" reported above (13),
yielding 32,000-plus, I'm led to believe that yes indeed, the query is
doing three FTS's *every time it runs*. Yet, I also see that it's
doing 13,741 logical reads, and 13,741 just happens to be the number
of rows returned by the query.

So, every time the query runs is DB2 re-doing the three FTS's, or, as
expected, is it doing a single logical read of the cached result set?

Thanks,

--Jeff