Datacom SQL query slow " WARNING: INDEX CARDINALITY STATISTICS NOT COMPUTED"

Products

• Datacom/DB
• Datacom
• Datacom/AD
• Datacom/Server

Issue/Introduction

A Datacom SQL query is non responsive and taking too much of time without giving any results. 

Run the query in DBSQLPR with option MSG=SD to get the SQL Optimization report.

In the KEY DEFINITIONS section of the report it shows the cardinality for all fields is 1 and message:

 *** WARNING: INDEX CARDINALITY STATISTICS NOT COMPUTED ***    

Sample report:

------------------------ KEY DEFINITIONS -------------------------   
  INDEX DEFINITIONS FOR: ttt/dbid SYSUSR.tttdbid ttt  
 KEYS= 1, IDXLVLS=4, LN=   59, ROWS/BLK= 57 ROWS=  100000
     KEY tttK1 ID=110 FLG=D0 00 FLDS= 5 DXX= 198 BLKCHG=  40 ROWS=100000
         OFFSET=    0, LN=  2, DIR=ASC  SENS=N CARD=        1 field1 
         OFFSET=    2, LN=  4, DIR=ASC  SENS=N CARD=        1 field2         
  KEY  tttK1 PROVIDES INDEX-ONLY PROCESSING  
 *** WARNING: INDEX CARDINALITY STATISTICS NOT COMPUTED ***     

Environment

Release: 15.1

Cause

There is no index cardinality statistics for the keys in the DBID.

Index cardinality statistics are computed on a DBUTLTY RETIX, LOAD or REPORT AREA=IXX,DBID=nnn,TYPE=G,UPDATE=YES.

A DBUTLTY DEFRAG will not compute cardinality statistics.

Resolution

Run a DBUTLTY TYPE=G report to compute the index cardinality statistics for the DBID:

REPORT AREA=IXX,DBID=nnn,TYPE=G,UPDATE=YES 

For type G requests the entire Index is read to generate current statistics.
The report executes in the MUF and can occur while any other MUF activity is occurring to this database, including update activity.

Additional Information

See documentation section Index Area (IXX) Reports.