Re: SQL changes - what is error telling me?

Hi Joel

I might suggest being explicit about casting - we had an example here of a numeric field that is an employee code - obviously not involved in calculations. It is 6 digits long.

Some things would not pass if this predicate is used -

empid like '1%'

Something like that, anyhow.

So I made a point of using the digits() function on the numeric field.

HTH
Vern

On 12/13/2013 10:57 AM, Stone, Joel wrote:

Thanks for the VERY comprehensive response.

The error I had in the SQL code was that the WHERE argument didn't match the IN.

At first I thought that I would need to use CHAR(TIME) and CHAR(JOSEQN) in order to cast as a string.

But I ran it WITHOUT the CHAR and it ran to completion error-free.

I think that it ran with expected results too.

I would prefer to use WITHOUT the CHAR casting as it is simpler to read and cleaner-looking.

Is it problematic in your opinion to skip the CHAR scalar casting and let the SQL engine "figure it out" as shown:?

Or can this lead to unexpected results?

I am hoping that even though I am mixing character and numerics, as long as the "WHERE" matches the "IN", I am comparing apples to apples so it is safe.



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


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, December 12, 2013 11:16 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL changes - what is error telling me?

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].