Re: Problem creating a view

Mark:

Is there a QDFTJRN *DTAARA in this library where you are trying to create this view?

Is this customer running a "high availability" or "disaster recovery" product that might now require journaling of all tables (physical files)?

Hope that helps,

Mark S. Waterbury

> 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.

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.

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. The difference here is that the view does not exist. It is normally a 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.

Can anyone shed some light on this for me?

One more thing. There is no journal in BKNTEST, and the tables referenced in the view are not journaled. This has not been a problem before, or at least not one that stopped me from creating views.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx