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

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


Article ID: 16771


Updated On:


Datacom DATACOM - AD


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


Datacom 15.1


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