CPD4068 Duplicate Key
Iain Bishop
My RPG is falling over with a duplicate key error on a WRITE statement.
However, both QRY and a DSPPFM show that the file in question contains no
records at all.
When I do a CLRPFM on the file, the error no longer occurs and the program
runs ok.
Has anyone experienced this sort of problem before and do you know what
causes it?
Thanks
Terence
Are you writing to a PF or LF ? Are there any LF files built over it?
Iain Bishop
thats returning the CPD4068 Key Reserved error. The LF is not a Join LF.
"Terence" <arrowc...@bigfoot.com> wrote in message
news:440c28e5.04030...@posting.google.com...
Saml
to set the FRCRATIO to 1 and see what happens.
Sam
"Iain Bishop" <iebi...@yahoo.co.uk> wrote in message
news:Bfe1c.84548$Wa....@news-server.bigpond.net.au...
Steve Landess
You just answered your own question...
Steve Landess
Austin, Texas
(512) 423-0935
"Iain Bishop" <iebi...@yahoo.co.uk> wrote in message
news:KGu1c.85693$Wa.6...@news-server.bigpond.net.au...
Iain Bishop
records.
"Steve Landess" <steve_...@hotmail.com> wrote in message
news:R8v1c.28743$OH4....@fe2.texas.rr.com...
Steve Landess
> I'm writing to a PF. There are LF's built over it and its one of the
> LF's thats returning the CPD4068 Key Reserved error. The LF is not a Join
LF.
>
no
> records.
All bets are off here - this is not simply a duplicate key error.
It appears that the file is journaled and you are running
in a commitment control environment.
Here is the message you are receiving:
Message ID . . . . . . . . . : CPD4068
Message file . . . . . . . . : QCPFMSG
Library . . . . . . . . . : QSYS
Message . . . . : Key for record in member &1 reserved.
Cause . . . . . : The output or update operation on member &1, file &2 in
library &3, record &7 format &8 member number &9 was not successful.
Format
&5 member number &6 is under commitment control and has the key reserved.
If
the record number is zero, the duplicate key occurred on an output
operation.
Wish I could help more, but I've never worked
with applications that use commitment control.
Go to www.Midrange.com and search the archives
for "Commitment Control". You'll get lots of hits there.
Also, search the IBM Infocenter for documentation on commitment control -
http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm
Steve
evolutionbaby
wouldn't see the records. Check your logicals to make sure they are
pointing to the right physical. If a logical is pointing to a physical
in another library, it could cause something like that.
Laurie
Iain Bishop
empties my PF before the RPG (the one giving the error) runs that writes to
the PF.
The SQLRPG was compiled with the COMMIT parameter set to *CHG.
I have recompiled it with this parameter set to *NONE and I no longer get my
error.
I have spoken to a colleague and he says he has seen other SQLRPG programs
do strange things when compiled with commitment control.
So don't compile your SQLRPG's with commitment control. I don't know why but
it seems they give unpredictable results. Does anyone know why this is?
Iain
"evolutionbaby" <evolut...@comcast.net> wrote in message
news:40476A15...@comcast.net...
Steve Landess
> I've finally got to the bottom of this. I have a SQLRPG program which
> empties my PF before the RPG (the one giving the error) runs that writes
to
> the PF.
> The SQLRPG was compiled with the COMMIT parameter set to *CHG.
> I have recompiled it with this parameter set to *NONE and I no longer get
my
> error.
> I have spoken to a colleague and he says he has seen other SQLRPG programs
> do strange things when compiled with commitment control.
> So don't compile your SQLRPG's with commitment control. I don't know why
but
> it seems they give unpredictable results. Does anyone know why this is?
Iain,
Did you read my 2nd posting?
You need to read about commitment control. When you are using
commitment control, YOU have control over whether the database
changes get externalized or not at any point during program execution.
Basically:
1) The files must be journaled.
2) You start the commitment control for the job by executing the
STRCMTCTL command, or in the case of an SQLRPG program
it is specified on the COMMIT parm of the CRTSQLRPG command.
3) If an error occurs and you decide that you want to put the files back the
way they were before the program started, within the RPG program you can
use the ROLBK operation code to remove all of the database changes that
have been made so far by this program (or since the last COMMIT operation
was executed).
4) If the program runs to completion and everything worked OK, then you
would
use the COMMIT operation code to make the database changes permanent.
5) End commitment control for the job with ENDCMTCTL.
Also:
In your original posting, you made no mention of the SQLRPG program that you
were
using to clear your file before running the program that bombed. Had you
mentioned
it, I would have told you that unless you want to follow the above steps you
need to
compile your program with COMMIT(*NONE).
I'm sure that if I gave wrong advice above, someone here will correct me...
Regards,
Charles Wilt
I didn't look at the whole thread but, couldn't he have also just did a
COMMIT after the clear?
Charles
In article <KfT1c.28277$lS1....@fe2.texas.rr.com>,
steve_...@hotmail.com says...
Steve Landess
>> I have spoken to a colleague and he says he has seen other
>> SQLRPG programs do strange things when compiled with
>> commitment control. So don't compile your SQLRPG's with
>> commitment control.
>>
>> I don't know why but it seems they give unpredictable results.
>> Does anyone know why this is?
Charles,
Yes and No. Yes if the files are journaled. No if they aren't.
But I was trying to answer his question. Based on the question, I
assumed that he knows little or nothing about commitment control.
I spent about 20 minutes looking at the text for the CPD4068 message,
trying understand how he would have gotten commitment control
started when he knows nothing about it.
Regards,
Steve Landess
Austin, Texas
(512) 423-0935
Dan Hicks
> I've finally got to the bottom of this. I have a SQLRPG program
> which empties my PF before the RPG (the one giving the error)
> runs that writes to the PF. The SQLRPG was compiled with the
> COMMIT parameter set to *CHG. I have recompiled it with this
> parameter set to *NONE and I no longer get my error. I have
> spoken to a colleague and he says he has seen other SQLRPG
> programs do strange things when compiled with commitment control.
> So don't compile your SQLRPG's with commitment control. I don't
> know why but it seems they give unpredictable results. Does
> anyone know why this is?
It's not strange at all. Under commit, the system must assure that
backout is possible. Therefore it will not let you insert a key
which conflicts with another key whose removal has not yet been
committed. If you commit the changes from the SQLRPG program then
you'll be able to insert the new key.
--
Dan Hicks
There are three groups of people: Those who can count and those who
can't.
Jonathan Ball
The CRTSQLRPGx shipped default value is to compile the
object COMMIT(*CHG). This means the program will
*always* try to run under commitment control, whether
the updated files are journaled or not. If the files
aren't journaled, the implied commit operation will fail.
The programmer need not know anything of commitment
control in order for this to occur.
Jonathan Ball
> I've finally got to the bottom of this. I have a SQLRPG program which
> empties my PF before the RPG (the one giving the error) runs that writes to
> the PF.
> The SQLRPG was compiled with the COMMIT parameter set to *CHG.
That's the shipped default for the CRTSQLRPGx commands.
> I have recompiled it with this parameter set to *NONE and I no longer get my
> error.
Right.
> I have spoken to a colleague and he says he has seen other SQLRPG programs
> do strange things when compiled with commitment control.
> So don't compile your SQLRPG's with commitment control.
If you're going to use commitment control, you'd
probably want them to compile with (at least) *CHG on
the COMMIT parameter.
Your problem wasn't the compilation with COMMIT(*CHG)
_per se_; your problem is that the files you updated
weren't journaled. If they had been journaled, your
program compiled with COMMIT(*CHG) (by default) would
have worked just fine.
Iain Bishop
I've written (I normally write RPG's) and so I didn't know the implications
of the COMMIT parameter.
Iain
"Jonathan Ball" <jon...@whitehouse.not> wrote in message
news:KYd2c.23795$aT1....@newsread1.news.pas.earthlink.net...
Jonathan Ball
> Thanks for your replies and help everyone. This is actually the first SQLRPG
> I've written (I normally write RPG's) and so I didn't know the implications
> of the COMMIT parameter.
Despite knowing about it, I still compile a SQLRPG
program with COMMIT(*CHG) sometimes, even though the
files on which it will operate are not journaled.
Charles Wilt
jon...@whitehouse.not says...
>
> If you're going to use commitment control, you'd
> probably want them to compile with (at least) *CHG on
> the COMMIT parameter.
>
> Your problem wasn't the compilation with COMMIT(*CHG)
> _per se_; your problem is that the files you updated
> weren't journaled. If they had been journaled, your
> program compiled with COMMIT(*CHG) (by default) would
> have worked just fine.
>
Are you sure about that? My impression was that his problem was due to
the fact that the deletion of the current records had not been committed
prior to trying to write new records which happened to share duplicate
keys with some of the records that had been deleted.
Which would mean that the original poster's files are indeed journaled.
If the files hadn't been journaled, he would have gotten a different
message; I believe it would have been:
SQL7008 - FILENAME in LIBRARY not valid for operation.
Reason: The reason code is 3.
3 - FILENAME is not journaled, or no authority to the journal.
HTH,
Charles
Jonathan Ball
> In article <X0e2c.23804$aT1....@newsread1.news.pas.earthlink.net>,
> jon...@whitehouse.not says...
>
>>If you're going to use commitment control, you'd
>>probably want them to compile with (at least) *CHG on
>>the COMMIT parameter.
>>
>>Your problem wasn't the compilation with COMMIT(*CHG)
>>_per se_; your problem is that the files you updated
>>weren't journaled. If they had been journaled, your
>>program compiled with COMMIT(*CHG) (by default) would
>>have worked just fine.
>>
>
>
> Are you sure about that?
100%? No.
> My impression was that his problem was due to
> the fact that the deletion of the current records had not been committed
> prior to trying to write new records which happened to share duplicate
> keys with some of the records that had been deleted.
>
> Which would mean that the original poster's files are indeed journaled.
Recall that the only change he made was to recompile
his module with COMMIT(*NONE), and then it worked
without error. That suggests a commitment control
problem to me.
Charles Wilt
jon...@whitehouse.not says...
> Charles Wilt wrote:
> > In article <X0e2c.23804$aT1....@newsread1.news.pas.earthlink.net>,
> > jon...@whitehouse.not says...
> >
> >>If you're going to use commitment control, you'd
> >>probably want them to compile with (at least) *CHG on
> >>the COMMIT parameter.
> >>
> >>Your problem wasn't the compilation with COMMIT(*CHG)
> >>_per se_; your problem is that the files you updated
> >>weren't journaled. If they had been journaled, your
> >>program compiled with COMMIT(*CHG) (by default) would
> >>have worked just fine.
> >>
> >
> >
> > Are you sure about that?
>
> 100%? No.
>
> > My impression was that his problem was due to
> > the fact that the deletion of the current records had not been committed
> > prior to trying to write new records which happened to share duplicate
> > keys with some of the records that had been deleted.
> >
> > Which would mean that the original poster's files are indeed journaled.
>
> Recall that the only change he made was to recompile
> his module with COMMIT(*NONE), and then it worked
> without error. That suggests a commitment control
> problem to me.
>
Correct, it is a commitment control problem. But the files not being
journaled wouldn't cause the Duplicate Key message.
Assuming that the process worked prior to being recompiled, then I'd say
that using COMMIT(*NONE) was the "correct" answer. Correct meaning,
it's the way the process was originally set up and that the process was
not intended to or needs to use commitment control.
But saying that the original poster's problem was due to his files not
being journaled was incorrect.
The real problem is that the SQLRPG program deleted records from a PF
under commitment control. Then an RPG program that wasn't using
commitment control tried to write out a record that had a duplicate key
with one of the records that had just been "deleted". I say "deleted"
because those deletes hadn't been committed to the database yet, so the
records were actually still there; thus the duplicated key error.
The original poster actually had three options for fixing this:
1) Stop using any commitment control - CRTSQLRPG ... COMMIT(*NONE)
2) Commit the deleted records to the DB prior to calling the RPG *PGM
3) Change the RPG program to also use commitment control in the same
unit of work as the SQLRPG program.
Option 1 is a valid solution if commitment control wasn't needed/wanted.
Option 2 is IMHO, probably not a good idea. Since it still leaves part
of the process using commitment control (the SQLRPG *PGM) and another
part not using commitment control (the RPG *PGM).
Option 3 is the option to use if commitment control is wanted/needed.
The entire process is under commitment control.
HTH,
Charles
Dan Hicks
> Recall that the only change he made was to recompile his module with
> COMMIT(*NONE), and then it worked without error. That suggests a
> commitment control problem to me.
It's a problem with the IMPROPER USE of commitment control, it's not
a problem IN commitment control. Commitment control was operating
as it should.
--
Dan Hicks
My theology, briefly, is that the universe was dictated but not
signed. --Christopher Morley