Cardinality Without Runstats

By Joe Geller, Dave Simpson, Tony Andrews

As we all know, for each statement, Db2 determines the access path to use to find the data.  It chooses the cheapest access path using the table statistics.  Of these statistics, the most fundamental are the cardinality of the table and the filter factors of each predicate.  The cardinality is the number of rows in the table as calculated by Runstats (and is only current as of when Runstats was last executed).  The filter factors determine the number of rows that each step of the access path is estimated to return.

So far so good.  However, Db2 has objects and functions that are “temporary” tables of one type or another.  These cannot have Runstats run against them.  But since cardinality is the most crucial statistic of access path selection, what can you do?

The table constructs that this article will discuss are:

  • Global Temp Tables
  • Table UDFs
  • Arrays

Click here to read the full article. (IDUG login required). 

2 Likes
Recent Stories
Where has the Db2 Connect Gateway gone?

An Introduction to db2mon

Improving Performance in Your Data Warehouse – Part 2