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

book

Article ID: 52261

calendar_today

Updated On:

Products

CA RC Compare for DB2 for z/OS CA Database Analyzer for DB2 for z/OS CA Fast Unload for DB2 for z/OS CA Fast Check for DB2 for z/OS CA Fast Index for DB2 for z/OS CA Fast Load for DB2 for z/OS CA Rapid Reorg for DB2 for z/OS CA Distributed Database Management CA Database Analyzer for DB2 for Oracle CA Database Analyzer (IMS Tools) CA Mainframe Configuration Manager for IMS for z/OS IMS TOOLS - MISC CA Compress Data Compression (IMS Tools) CA Database Analyzer for IMS for z/OS CA Database Copier for IMS for z/OS CA Database Organizer for IMS for z/OS CA Mainframe Extended Terminal Manager (IMS Tools) CA High Performance Recovery for IMS for z/OS CA Database Organizer (IMS Tools) CA Mainframe Program Restart Manager for IMS for z/OS CA Secondary Index Builder for IMS for z/OS CA Secondary Index for IMS for z/OS

Issue/Introduction

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.

Environment

Release:
Component: DBA