MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2013

Re: SQL changes - what is error telling me?



fixed

On 11-Dec-2013 10:24 -0800, Stone, Joel wrote:
The following SQL functions well:

Insert into jrnflb4af
select * from jrnflALL
where joseqn in
(select min(joseqn)
from jrnflall
group by joobj, joctrr
union
select max(joseqn)
from jrnflall
group by joobj,joctrr
)
order by joobj,joctrr,joseqn

I made the following changes (in CAPS) to add DATE/TIME in front
of JOSEQN:

Insert into jrnflb4af
select * from jrnflALL
where JOCTRR = 8232817
AND joseqn in
(select min (JODATE || CHAR(JOTIME) || CHAR(joseqn))
from jrnflall
group by joobj, joctrr
union
select max (JODATE || CHAR(JOTIME) || CHAR(joseqn))
from jrnflall
group by joobj,joctrr
)
order by joobj,joctrr, CHAR(joseqn)


Note: There is an unstated assumption that the DatFmt of the job that requested the DSPJRN must be in *YMD so the JODATE data enables proper collation to enable MIN and MAX; so stated here.

The above query is not properly formed to effect what is desired. The IN predicate is testing a NUMERIC(10) named JOSEQN to a character expression [we know, because *TYPE5 Outfile Format is not being used]. That requires the character expression in the aggregate of the subquery needs to be implicitly converted to the numeric data type that matches JOSEQN, to enable the comparison. But because the CHAR(JOBTIME) effects a CHAR(8) result for a six-digit number, there will be blanks embedded in the data; which otherwise would be numeric digits [per no negative values nor decimal points or other formatting].

The query would be expected, in prior releases, to fail with msg CPD4019 RC6 suggesting that "A select or omit error occurred..." due to "A data mapping error occurred on the field before the select or omit operation was attempted." And IMO should still fail that way.

The predicate simply needs correction to have the potential to produce the correct results. Instead of testing JOSEQN against the expression, the effectively identical expression should be compared; i.e. by replacing JOSEQN with the expression (JODATE || CHAR(JOTIME) || CHAR(JOSEQN)). Presumably the ordering should similarly be done on that expression; i.e. given the origin of this modification is to resolve a reset journal sequence number, the date-qualified value would be required to ensure collation is to have the matching MIN precede the matching MAX.

Insert into jrnflb4af
select * from jrnflALL
where JOCTRR = 8232817
AND JODATE || CHAR(JOTIME) || CHAR(joseqn) in
(select min (JODATE || CHAR(JOTIME) || CHAR(joseqn))
from jrnflall
group by joobj, joctrr
union
select max (JODATE || CHAR(JOTIME) || CHAR(joseqn))
from jrnflall
group by joobj,joctrr
)
order by joobj,joctrr, JODATE || CHAR(JOTIME) || CHAR(joseqn)


Now I get this ugly incomprehensible error: What is this complaining
about??

Yet the first two messages shown, the apparent "this", are not errors; they are completion messages. OK, kidding aside... It would help if the colon preceded what was deemed the "error" instead of preceding a question followed by two completion messages.

6697 entries converted from journal JRNLCMIS in PDBJRNLIB.
Member JRNFLB4AF file JRNFLB4AF in JSTONE cleared.

The above messages are apparently the effect of a DSPJRN OUTPUT(*OUTFILE). Having used LOGCLPGM(*YES) probably would have clarified that; i.e. the request message showing the DSPJRN would have appeared prior. The request message showing the CALL prior to those two messages likely would also help clarify. With CLP Logging the request message showing the RUNSQLSTM also would have appeared prior to the following:

Function error X'1720' in machine instruction. Internal dump
identifier (ID) 0201590F.

The above is the generic LIC Function Check msg MCH3203 RCx1720 for which a VLog VL02001720 with the noted Dump ID (DMPID) would have been produced. That dump further explains the error condition.

Having an actual spooled joblog vs the copy\paste from an active joblog produces some *context* for the failure. Knowing that *a* LIC *FC transpired is useless without knowing what invoked the LIC and what LIC was invoked. Little different than the required context for most other error messages... OS or otherwise, but especially /generic/ error message conditions.

The Print Internal Data (PRTINTDTA) command could be used to spool the VLog, although there may be other VLogs nearby to that specific log with major code 0200 and minor code 1720 that could provide more details about the failure. The task dump that is included in that specific dump data would also help to reveal more context for the failure. Typically the surrounding VLogs in time and some possibly identified with the same job would be collected as data for a service\support call, in addition to other details that would be required to re-create the error.

Ownership of object DBOPTRC12 in QTEMP type *FILE changed.
File DBOPTRC12 created in library QTEMP.
Object DBOPTRC12 in QTEMP type *FILE deleted.
*** DBOP open FAILED. Exception from call to SLIC! ***.

The above are just messaging informing of the DBOP component logging a /trace/ file about the prior catastrophic failure of the query it was trying to execute. The data in the file may have been spooled before the file was deleted.?

Internal failure occurred in query processor.

The generic CPF4204 of the OS Query (QQ) feature, informing that the query encountered an unrecoverable error.

SQL system error.

The generic SQL0901 of the OS SQL feature, saying that the SQL request [in this case, surely the query SELECT, as part of an INSERT statement] failed miserably.

Ownership of object QP0Z984297 in QUSRSYS type *USRSPC changed.
File QAP0ZDMP in library QTEMP already exists.
User Trace data for job 984297/JSTONE/MN001CPNBA dumped to member
QP0Z984297 in file QAP0ZDMP in library QTEMP.
28 records copied from member QP0Z984297.
1 User Trace buffer(s) deleted.

Like the Query feature, the SQL also logs an effective /trace/ file, but after logging a generic error message, instead of before. IIRC the /copied/ records are copied to a spool file before the the trace buffer [¿the file named earlier?] is deleted.

RUNSQLSTM command failed.
SQL9010 received by ETJ021 at 11300. (C D I R)
? C

This informs that the CLP that included the apparent DSPJRN and the RUNSQLSTM did not monitor for the SQL9010, was named ETJ021. A request message preceding the DSPJRN messages was apparently CALL ETJ021. We know that the the compiled CL run-time exception handler intercepted the error SQL9010 issued for the failing RUNSQLSTM command which I believe was coded on line 113.00. That CL run-time exception handler then inquired how the user wanted to respond to the unmonitored error; inquired as either CPA0701 or CPA0702 depending on whether the program was CLP or CLLE. A reply of C=Cancel was given, but for lack of the context provided by a spooled joblog [into printer file QPJOBLOG], we can not know if the user replied or if there was an auto-reply.

Function check. SQL9010 unmonitored by ETJ021 at statement 11300,
instruction X'0072'.

And the generic OS Function Check CPF9999 informing that the prior error was unmonitored. IIRC, not shown if I=Ignore or R-Retry had been chosen instead... but mostly inferred, because the C=Cancel reply shows prior.

After all that... the error message is saying: "Bad thing! The query can not be completed, and there is not a specific error to diagnose the issue, so instead of a specific error message, the database query feature created /dumps/ that can be investigated to try determine what happened and\or what might be needed to generate a re-create so that the developers can review the failure happening in the lab."

I suspect the error may be a side effect of some changes to the underlying LIC database query support in what I recall were attempts to be _more forgiving_ of mapping\selection errors. That is to imply, I suspect that instead of giving the hard-failure of CPD4019 to inform the user that there is an error that prevents performing selection [similarly I seem to recall something also for ordering], the code tries to continue running the query; i.e. further evaluating selection, rather than stopping the query with an error.

Perhaps at some point, after being unable to find any data over many rows, some tracking regarding the prior mapping errors fills up some internal storage areas and the code finally /falls-over/ instead of doing something more reasonable... because the situation is deemed sufficiently extreme and abnormal, that there may be little incentive to code up a /prettier/ resolution. Regardless of the reality [i.e. my /perhaps/, of course, is pure speculation], the LIC *FC is an obvious defect, because a poorly\improperly constructed query [with very few exceptions] should not cause the LIC database query processing to fail with a generic *Function Check* error. Such a query should not exhibit such a failure merely because of its poor construction, except for conditions such as /system/ limits perhaps; not for something like data mapping error tracking limits [again that is speculation].






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact