Re: SQLSTATE 58004

The 58004 is probably with SQLCODE -901 which is SQL0901. That is an internal error in the SQL processor, which in many cases is a defect in the SQL database code. If not the default action, there is I believe an environment variable which can be set to force some dump data to be placed into a user space when the -901 occurs, and then a spool file gets generated as a copy of that USRSPC during the failure. Much like what is described in:
http://www.ibm.com/developerworks/forums/thread.jspa?threadID=56958

Searching ibm.com, I did not have much luck finding the feature [that I do not recall how it is activated] which implements the automated dumping. However as an apparent defect, probably the error can be reported to your service provider; of course a dump or some other debug details would need to be provided for them to assist much beyond suggesting the latest cumulative PTF & DB fixpack. Since the problem was occurring but is no longer, even after reverting to the original setup, the problem may be a side effect of an implementation change for the query or some other situation that is currently absent but may return; i.e. the problem may return again unexpectedly, just as alluded was a concern.

However even without the dump, the joblog for the job performing the SQL would almost always log previous error messages for the error msgSQL0901. That previous error may have a "symptom string" that can be searched on the IBM support\service pages to locate a specific preventive fix.

I used to always code for -901 with an inquiry message sent to QSYSOPR and a wait on a specific reply that only I knew [anything but the correct response would re-inquiry and await the correct reply value], such that only an ENDRQS or ENDJOB would terminate the processing. In so doing, the failing job can be inspected for its "last SQL" and enables collection of job & dump information while the open files, locks, & program stack are still active [even though the statement itself is failed\completed] such that job attributes which might be relevant can also be gathered by WRKJOB OUTPUT(*PRINT).

Regards, Chuck

Raul A. Jager W. wrote:

I have a NetData macro (source below) that was working fine during several years, suddenly, yesterday it failed with a SQLSTATE 58004 in function listAsie(). The cause seems to be in the "WHERE" clause, eliminating the "AND" it works, eliminating the "OR" also works (but the report is not what I need). I also
tried to replace $(REMOTE_USER) with a constant, same error.
Temporary solution: I replaced the physical file with a view, and
eliminated the "AND". Today I restored the original version:
Surprise, it works!!! I can not reproduce the error, it went away
by itself, it may return when I least want it.

<<SNIP all but SQL statement:>>

SELECT fasie, nasie, situa, tasie, nasiev
, b.f7(fasie) as f, fh_crea, culp_act
, fh_act, tasa, impt4 FROM ed.asie_fn
WHERE (culp_crea = '$(REMOTE_USER)'
or culp_act = '$(REMOTE_USER)') and situa <= '2'
ORDER by fasie, nasie