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