RC=08 on user database but that index is not full when running an SQL query with count(*) and group by

Products

• Datacom
• DATACOM - AD

Issue/Introduction

When I run the following SQL query I am getting an index full dbid=827 

01 SELECT CO_NO AS CONO, COUNT(*) AS RECCOUNT 
02 FROM SYSUSR.DDRMS00 
03 GROUP BY CO_NO 

=> 

--------------------------------------------------------------------------DQ093 
DATAQUERY: HELP PANEL FOR ERROR MESSAGE 
------------------------------------------------------------------------------- 


DQ093E - INDEX FULL - DBID=827 

DB Return Code = 08-008 SQL Return Code = 0259- Command = QEXEC 
Error Program = DBSERV Error Diagnostic 3 = 848 -008 

CAUSE: DATAQUERY has encountered an unexpected error when processing an 
SQL statement. The return codes and diagnostic information 
supplied by database are displayed above. 

ACTION: Contact your DATAQUERY administrator and describe the error 
message, including the text, message number and diagnostic 
information displayed above and what you were doing when the 
error occurred. 


------------------------------------------------------------------------------- 
<PF1> PANEL HELP <PF2> RETURN 



This ran last month with going against 794,625 records and now has 796,783 records. 
After it cancelled the first time I increased both the index and data areas for DBID 827 by 500% and still got the error. 

Environment

z/VSE 
SQL 11.0 

Cause

Key has been defined with  attribute INCLUDE-NIL-KEY=N.

Resolution

Turned on  PXX dump for this RC 08(008) and found that IXX006 is too small. 
Index for the CBS database needs to be increased in size. 

After increasing the size the RC 08 still occurs. 
Found that a secondary key that fits this query has been defined with INCLUDE-NIL-KEY=N  so CBS cannot use this key and the master key is really not good for this query. 
After changing this key to have INCLUDE-NIL-KEY=Y and index rebuilt  this query ran successfully. 
 
Also found that some SQL tables have been defined with logging=no which is not as required..
All SQL tables need both logging and recovery turned on . Gave DQ DQL query to find out which tables need to be changed.