HIGH2KEY and LOW2KEY BLANK after PDA Auto-Catalog Update processing.

Document ID : KB000052261
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

A PDA Extract Procedure is run to collect statistics on tablespaces only, without their associated indexes. It also, at the same time, updates the DB2 catalog to mimic what RUNSTATS does, in theory, with COLSTATS=I(ndex) defined.

SYSCOLUMNS is updated with blank values in HIGH2KEY and LOW2KEY.

This may cause the optimizer to make some unfortunate access path choices in some cases.

Solution:

This problem is caused by using Database Analyzer in a way other than how it is designed to work.

There are two options to resolve this problem:

  1. Do not specify COLSTATS=I(ndex) for Extract Procedures where indexes are excluded from statistics collection.

    Turn the COLSTATS off with COLSTATS=N.

  2. Include indexes in object selection to use COLSTATS=I(ndex) column. The help for Column Statistics for Index states the following:

    "I" - Save statistics for columns used in indexes only. Extract should contain indexes in object selection.

Typically you would want your Tablespace/Table statistics to stay in sync with their corresponding Indexes statistics so that the DB2 optimizer is fed valid info.