On 03-May-2016 09:58 -0500, dlclark wrote:
I have an application that suddenly stopped working while I was on
vacation and the following message <ed: appended at the bottom> was
being issued.
After getting back, I copied the guts of the associated SQL view into
iNav and it ran just fine.
So the SELECT statement from the VIEW operated without errors? But
was the SELECT * FROM the_view [or whatever the application performs]
was found to be failing still, for you, just as before for others?
So, without changing a thing, I recreated the VIEW from its
production source.
If the VIEW is suspected as being corrupted, the problematic VIEW
best would be /renamed/ [e.g. RNMOBJ command or RENAME statement] or
/moved/ [e.g. MOVOBJ command] into an alternate library to
compare\contrast the results of the failing request against the old vs
the new VIEW. Other requests like DSPFFD, DSPFD, and DMPOBJ against the
VIEW might directly or subtly reveal /corruption/ with the logical file;
e.g. partial\truncated output or errors logged.
Now the application is working fine.
Hmm... OK. Seems from that comment, whatever is the SQL
statement\request that the application used, was confirmed as still
failing after returning. And so was that [effectively same] failing
statement repeated in Run SQL scripts; i.e. the query of the VIEW [as
opposed to running the statement that defines the VIEW, as noted above,
was done]?
Any ideas how an SQL VIEW can get "corrupted" and cause a message
like this?
The embedded statement's stored or a cached [version of an] Access
Plan that statement that refers to the VIEW has just as much chance at
having\being a problem.
Although perceived as ruled-out, the actual data still could be an
origin. There are considerations which may not be obvious to the
layperson, for determining that possibility. Notably, the access plan
could have changed in a way that some invocations were performed [even
on the identical data] when in the past they may not have been
performed; and again the same, after the re-create of the VIEW [with a
new access plan] those failing invocations are not requested in
finalizing the query. Similarly, the order of the data and what rows
are actually fetched, because unless all rows are processed, the query
may not have encountered the problematic row(s). However those presume
the error seen is /normal/ as an effective data mapping error; but as I
recall, the mapping errors should be *a different return code* than the
rc02 from the OP, for which a prior message like msg CPF426B with rc13
would be expected.
Now, there were some PTFs applied to the system some time last week.
Implying... the problem first started soon after the PTFs? Or is
that not confirmed, only speculated as possible? The history log should
reveal the PTF activity in the timeline; need only find the first
occurrence of the failing query of the VIEW to find where in that timeline.
Is it likely the view simply had to be recreated because of the PTFs
that were applied?
Unless there was a PTF with special instructions that document the
need to effect the re-create of any VIEW using that system UDF, then a
negative effect from the invocation that was not data-dependent would
imply a defect[ive PTF].
Additional Message Information
Message ID . . . . . . : CPF503E Severity . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 05/03/16 Time sent . . . : 10:31:24
Message . . . . : User-defined function error on member TSRCOUTV01.
Cause . . . . . : An error occurred while invoking user-defined
function TIMESTAMP_FORMAT in library QSYS2. The error occurred while
invoking the associated external program or service program QQQSVUSR
in library QSYS, program entry point or external name
QQQTimestamp_Format, specific name *N. The error occurred on member
TSRCOUTV01 file TSRCOUTV01 in library WISEUTL.
The error code is 2. The error codes and their meanings follow:
1 -- The external program or service program returned SQLSTATE 00000.
The text message returned from the program is: .
2 -- The external program failed before it completed.
The actual spooled joblog should be provided; all data with full
logging [optionally a joblog with debug active], since the failing
request [and if not visible in the joblog, the actual request; e.g.
SELECT * FROM WISEUTL/TSRCOUTV01] up to the request to DSPJOBLOG. The
msgCPF503E rc2 implies there was a failure in the system program that
implements the UDF, such that the "program failed before it completed",
for which presumably some previously logged errors would have been
present; those would be the most relevant symptoms for searching for a
preventive or corrective fix.
FWiW, a possiblly related APAR+PTF [specifically mentioning
TIMESTAMP_FORMAT though not in the abstract, and no mention of the
synonym\alternative name TO_DATE, and RC2 is not correlated with a
message nor is the message in proper symptom-keyword format]; no mention
if the PTF is corrective since the condition appears, or only preventive
to the origin of the failure:
APAR SE55841
[
http://www.ibm.com/support/docview.wss?uid=nas25b8c57cf898f264c86257b80003c7584]
OSP-DB-OTHER-F/QQQSVUSR-T/QQQSVUSR-RC2-MSGMCH3601 VIEW WITH TIMESTAMP
FUNCTION FAILS WITH MCH3601 AND CPF503E
PTF SI50301 on C3298710
[
http://www.ibm.com/support/docview.wss?uid=nas311dd19eda8ab744986257bd0007efdbd]
OSP-DB-OTHER-F/QQQSVUSR-T/QQQSVUSR-RC2-MSGMCH3601 VIEW WITH
TIMESTAMP_FORMAT function fails with MCH3601 and CPF503E
"...
CORRECTION FOR APAR SE55841 :
-----------------------------
The problem with views that contain TIMESTAMP_FORMAT function
has been fixed.
CIRCUMVENTION FOR APAR SE55841 :
--------------------------------
None.
..."
midrange.com
