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

Products

• Datacom
• DATACOM - AD
• CIS
• COMMON SERVICES FOR Z/OS
• 90S SERVICES
• DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
• COMMON PRODUCT SERVICES COMPONENT
• Common Services
• CA ECOMETER SERVER COMPONENT FOC
• Easytrieve Report Generator for Common Services
• INFOCAI MAINTENANCE
• IPC
• UNICENTER JCLCHECK COMMON COMPONENT
• Mainframe VM Product Manager
• CHORUS SOFTWARE MANAGER
• CA ON DEMAND PORTAL
• CA Service Desk Manager - Unified Self Service
• PAM CLIENT FOR LINUX ON MAINFRAME
• MAINFRAME CONNECTOR FOR LINUX ON MAINFRAME
• GRAPHICAL MANAGEMENT INTERFACE
• WEB ADMINISTRATOR FOR TOP SECRET
• 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.