RC=08 on user database but that index is not full when running an SQL query with count(*) and group by
book
Article ID: 115639
calendar_today
Updated On:
Products
DatacomDATACOM - 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 -------------------------------------------------------------------------------
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.