MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » March 2014

Re: Problem creating a view



fixed

The CPF6968 is the message causing the SQL0901 I can not currently replicate this error with existing views,
so I cannot tell if it is the same context, but with the view in question, here is the context info:

This is first level text in job log:
Journal entry cannot be associated with file WBOODV100.
Function check. CPF6968 unmonitored by QDBCRTME at statement *N,
instruction X'05CB'.
SQL system error.

here is the context info for CPF6968
Display Message Details

Message ID . . . . . . : CPF6968 Severity . . . . . . . : 40
Date sent . . . . . . : 03/13/14 Time sent . . . . . . : 09:50:10
Message type . . . . . : Escape
From . . . . . . . . . : MMURPHY CCSID . . . . . . . . : 65535

From program . . . . . . . . . : QJOSNDJE
From library . . . . . . . . : QSYS
Instruction . . . . . . . . : 06F9

To program . . . . . . . . . . : QDBCRTME
To library . . . . . . . . . : QSYS
Instruction . . . . . . . . : 05CB

Time sent . . . . . . . . . . : 09:50:10.451058



Bottom
Press Enter to continue.

F1=Help F3=Exit F12=Cancel



Here is the context info for the CPF9999
Display Message Details

Message ID . . . . . . : CPF9999 Severity . . . . . . . : 40
Date sent . . . . . . : 03/13/14 Time sent . . . . . . : 09:50:10
Message type . . . . . : Escape
From . . . . . . . . . : MMURPHY CCSID . . . . . . . . : 65535

From program . . . . . . . . . : QMHUNMSG

To program . . . . . . . . . . : QSQCRTV
To library . . . . . . . . . : QSYS
To module . . . . . . . . . : QSQCRTV
To procedure . . . . . . . . : QSQCRTV
To statement . . . . . . . . : 11527

Time sent . . . . . . . . . . : 09:50:10.451517



Bottom
Press Enter to continue.

F1=Help F3=Exit F12=Cancel



for the SQL0901 there is no context info:
Display Message Details

Message ID . . . . . . : SQL0901 Severity . . . . . . . : 50

Message type . . . . . : Diagnostic
From . . . . . . . . . : CCSID . . . . . . . . : 65535

Time sent . . . . . . . . . . :











Bottom
Press Enter to continue.

F1=Help F3=Exit F12=Cancel




Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----CRPence <CRPbottle@xxxxxxxxx> wrote: -----
To: midrange-l@xxxxxxxxxxxx
From: CRPence <CRPbottle@xxxxxxxxx>
Date: 03/12/2014 03:06PM
Subject: 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
..."






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact