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
( select current_date as asofdate
, left(dcstkl,3) as dcplnt
where dcstat <> 'C'
select asofdate, dcplnt, sum(dcobal) as dcobal
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.
Error: [SQL0901] SQL system error.
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?
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:
_i Journaling: Why Is My Logical File Journaled? i_
"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