Re: Problem creating a view

On 12-Jun-2014 15:54 -0500, Mark Murphy/STAR BASE Consulting Inc. wrote:

CRPence on 03/13/2014 01:09PM wrote:

<<SNIP>>
FWiW: The most complete contextual information [outside of the SQL
request itself per the CREATE VIEW statement not being a CL
command-request; i.e. not being a request-message] is available
from the spooled output either from a DSPJOBLOG OUTPUT(*PRINT)
taken with the specified JOB() having LOG(4 0 *SECLVL) in effect...

I am resurrecting this thread because it has again reared its ugly
head. Once again, the view does not exist (though it did just
yesterday), and I am currently unable to recreate it, or any of the
dependent views (which also do not currently exist).

Here is the job log:

*NONE Request 06/12/14 16:44:20.871236
QUICMD QSYS 0543 QUICMD QSYS 0543
Message . : -RUNSQLSTM SRCSTMF('/home/mmurphy/SQL
Source/Buckhorn/ocrmhv.sql') COMMIT(*NONE) NAMING(*SQL)
CPF6968 Escape 40 06/12/14 16:44:20.960475
QJOSNDJE QSYS 06F9 QDBCRTME QSYS 05CB
Message . : Journal entry cannot be associated with file OCRMHV.
Cause . . : The specified journal entry cannot be associated with
file OCRMHV in library BKNTEST because of one of the following:
-- File OCRMHV in library BKNTEST is not a data base file.
-- File OCRMHV in library BKNTEST is a logical file that
is not journaled.
Recovery : Correct the error and try your request again.
CPF9999 Escape 40 06/12/14 16:44:20.960944
QMHUNMSG *N QSQCRTV QSYS *STMT
To module . . . . . . . . . : QSQCRTV
To procedure . . . . . . . : QSQCRTV
Statement . . . . . . . . . : 11527
Message . : Function check. CPF6968 unmonitored by QDBCRTME
at statement *N, instruction X'05CB'.
<<SNIP>>

So this failure is /solid/ presently; i.e. each time this script is run, the same failure transpires? That is bad for progressing, but good for enabling review by IBM development, if the problem is not easily recreated otherwise.

From QRECOVERY.QSQ901S
<ed: reformat return code> msgSQL0901 rc4266
<ed: reformat statement text>
create or replace view ocrmhv as
select * from ocrh
union all
select * from ocmh
VRM: V7R1M0
DBGROUP: SF99701 23
<<SNIP>>

Per use of SQL naming, I infer the files OCRH and OCMH are in the library BKNTEST.

What the DSPFD TYPE(*ATR) OUTPUT(*) information shows for the various "journal" details of those two table-references may be relevant; i.e. if those file names are TABLE file objects [or otherwise Physical Files]. Or perhaps if VIEWs, then also the Display File Description (DSPFD) details of the based-on files; i.e. drilled-down, to find the journal status of the underlying files.

The problem origin may be implicit journaling [covert journaling] of the SQL VIEW logical file; or more appropriately, the lack thereof; see:
<http://www.redbooks.ibm.com/abstracts/tips0677.html?Open> I infer there should be a D-CT journal entry for the file OCRMHV, and a D-MA for the member, and that the error msgCPF6968 implies that the F-MC for the member was not generated; i.e. the failure of the effective Send Journal Entry (SNDJRNE) of TYPE(MC) with CODE(F) was not allowed because the SQL VIEW was not [or was no longer] journaled.

I am unaware of the implementation of the implicit\covert journaling, so I do not know in what journal those entries would be found. Perhaps the Default Journal (DFTJRN) of the Start Commitment Control (STRCMTCTL) would be used [if that were applicable; almost surely not in this case, per COMMIT(*NONE)]. So probably a better SWAG is that the primary file [the first] of a multiple-file logical would determine where the journal entry would be deposited. But then the question becomes, what of the journaled status of the two table-references in the FROM clause of each of the SELECT subqueries? Perhaps something like the first file is not journaled but the second is, so the database create file processing for the VIEW perhaps did not recognize the need to effect the implicit Start Journal to effect the D-CT being sent, but for some reason the database create\add member processing did recognize the need to effect the F-MC being sent, and thus the conundrum [the illogical state] for which the error makes sense?

I suspect that either ending journaling of all of the based-on physical files or ensuring all of the based-on physical files are journaled [perhaps only when journaled to the same journal], then the problem might be circumvented.?

... clearly there is a *defect* that should be reported to the
service provider; most likely, for the lack of a monitor for the
CPF6968 in the program QDBCRTME, for which the *FC is manifest
instead of a proper percolation of an acknowledged condition back
to the SQL as invoker. Thus, the defect will [whether directly or
indirectly] have to be addressed by IBM via a PMR. <<SNIP>>

Irrespective any speculation I might have offered in my comments previously within this reply, the above text quoted from my prior reply still stands; even if instead of my implication that the QDBCRTME should monitor the error, that program might simply be impaired by some incorrect operations effected in prior processing, such that the true origin and the required corrective are elsewhere.