Re: Problem creating a view

On 3/12/2014 1:39 PM, Mark Murphy/STAR BASE Consulting Inc. wrote:

Let's get this out of the way first, V7.1 TR

I am having trouble creating a specific view. The error is CPF6968
Journal entry cannot be associated with file WBOODV100.

Please provide the full context of the message, beyond just the failing SQL request; i.e. the DSPJOBLOG OUTPUT(*PRINT) version [best with LOG(4 0 *SECLVL) active for the job], or the F6=Print version taken from F1=Help on the message viewed in an [inter]active joblog.

Further comments inline\below. Note: The OP did not appear via NNTP, so I am actually responding to a reply to the OP, but this message is composed as if directly in response to the OP; i.e. the /threading/ of my reply may appear incorrect.

I can execute the SELECT statement by itself, but when I add
CREATE OR REPLACE VIEW to the front of it, I get the error.

Thus making the request DDL vs a DML\SELECT.

Here is the SQL:

create or replace view wboodv100 as
with
openorders as
( select current_date as asofdate
, left(dcstkl,3) as dcplnt
, dcobal
from ocrh
where dcstat <> 'C'
)
select asofdate, dcplnt, sum(dcobal) as dcobal
from openorders
group by asofdate, dcplnt
;

Now I have seen this error before with other views, and if I DROP
the view before running the CREATE, the error goes away, and I am
able to create the view successfully.

And "this error" is the CPF6968 From and To the same programs with the same return code, and also followed by the SQL0901?

The difference here is that the view does not exist. It is normally
an intermittent problem that I can get around. This time I can't
seem to find the magic formula. I can and have created other views in
the same library. This happens within Squirrel, System i Navigator,
and on Green Screen SQL.

Squirrel reports:
Error: [SQL0901] SQL system error.
SQLState: 58004
ErrorCode: -901.
I believe it is the same error.

The joblog of the server job processing the request will have the details. Being a -901, there are instructions about where more information for that generic SQL failure is tracked\logged; probably a TechNote, and include the follow search kwd to narrow the topics: QRECOVERY

Can anyone shed some light on this for me?
<<SNIP>>

The full details\context of the SQL0901 are also of interest. The fact that there is a -901 error is a clear indication of a defect. If the msg CPF6968 is validly issued, then the SQL should handle the error either by ignoring or warning of the condition or issuing an appropriate SQLCODE rather than the generic [equivalent to the CPF9999 aka *FC] Function Check error. Or if the error msg CPF6968 is invalidly issued, then again, a defect is implied.

Seems to me, that the described condition is probably\somehow a side effect of the [intended or attempted] "covert journaling" for logical files. A description can be found here:
<http://www.redbooks.ibm.com/abstracts/tips0677.html?Open>
_i Journaling: Why Is My Logical File Journaled? i_
_Abstract_
"Starting in IBM i 6.1, logical files, SQL views, and SQL indexes will be journaled, even if you do not change your environment. Why did the operating system starting journaling your logical files? Why are some journaled and some not journaled? How can you end journaling for some of them or start journaling for others? This technote addresses these questions and more.

Written by: Don Zimmerman
..."