Why do I see different record counts between SELECT COUNT(*) and SELECT * ?

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

I did a SELECT COUNT(*) and SELECT * on one of our tables. 

SELECT COUNT(*)  gave me 21,090 while SELECT * returned 21,108 records.

Why is there a difference between the two and how do I get consistent numbers?

 

 

Answer:

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 but some PTFs are needed to maintain the record counts accurate and for COUNT(*) to work correctly:

For 14.0: RO66356, RO69078, RO66748  RO65555, RO76960 

For 15.0: RO77114 

No PTFs are needed for 15.1.

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

This feature was implemented by PTF RO78675 (14.0), RO78731 (15.0) and it is sourced in 15.1

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

To reset the record count, run DBUTLTY function RETIX with KEYNAME=*SETR.

To reset the counts for a single area:

RETIX DBID=nnnn,AREA=aaa,KEYNAME=*SETR

To reset the counts for an entire database:

RETIX DBID=nnnn,KEYNAME=*SETR

Additional Information:

See TEC1172412 for more details on resetting the record count.