Different record count between SELECT COUNT(*) and SELECT *

Products

• Datacom
• DATACOM - AD

Issue/Introduction

This article explains why you might see a different record count between running SQL SELECT COUNT(*) and SELECT * 

Environment

Datacom 15.1

Resolution

The SQL COUNT(*) was enhanced in Datacom 14.0 to use the CXX record count instead of counting the actual rows in the table. 

This requires that the CXX record count be accurate. Until version 12.0, when a MUF crashed, the CXX stats in memory were not copied to disk, so the next MUF startup could have an incorrect record count. This changed in 14.0 .

Additional Information

This SQL enhancement can be temporarily turned off by adding MUF startup option SQL_COUNT_OPT_OFF

A workaround is to add a predicate that is always true, like WHERE 1=1.

To reset the record count, run DBUTLTY function DATASCAN OPTION=COUNT_SET_RECORDS