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

book

Article ID: 16771

calendar_today

Updated On:

Products

CA Datacom - DB CA Datacom CA Datacom - AD CA Datacom - Server CA CIS CA Common Services for z/OS CA 90s Services CA Database Management Solutions for DB2 for z/OS CA Common Product Services Component CA Common Services CA Datacom/AD CA ecoMeter Server Component FOC CA Easytrieve Report Generator for Common Services CA Infocai Maintenance CA IPC Unicenter CA-JCLCheck Common Component CA Mainframe VM Product Manager CA Chorus Software Manager CA On Demand Portal CA Service Desk Manager - Unified Self Service CA PAM Client for Linux for zSeries CA Mainframe Connector for Linux on System z CA Graphical Management Interface CA Web Administrator for Top Secret CA CA- Xpertware

Issue/Introduction



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?

 

 

Environment

Release: DATABB00200-14-Datacom/AD
Component:

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 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.