CPYF/SQL INSERT problem - Duplicate Key

Hi guys,

I need to copy some records form one to other file and using SQL INSERT operations I get an error:SQL0803 - Duplicate Key Errors.

When I try use cpyf some records are copied not everything. The rest - uncopied records - are with duplicated keys.
And the question is: how to copy this duplicated records?

Regards

Tomek

Hmmmnnn, SKOREK where did you study computer analysis?



Question 1. Did you carefully and thoroughly review (emphasis on
"thorough" and "careful") the joblog when you tried the CPYF command?
Probably not, because the copy normally stops at the error record - so
the remaining (in your terminology "uncopied") records may, OR MAY NOT,
cause duplicated keys. There is enough information in the detailed
joblog to find the record causing the error, if you look properly.



Question 2. Why would you want to copy records that cause "duplicate
key" errors?? The message is trying to tell YOU something. Maybe you
haven't done enough analysis yet or maybe what you are trying to copy
has already been copied in an earlier attempt and therefore shouldn't be
copied again! Maybe you should clear the target file and start again.



I am sure there are several organizations that offer in depth courses on
IBM Commands and Basic Analysis, you should use them. This list is
about JDE World software - you might help yourself by letting us know
what files you are using (i.e. copying from and copying to).



Regards,



Colin

We use the Insert (SQL) and CPYF commands quite a bit. The FIRST thing I do
is analyze the tables and figure out what we want to accomplish. I have yet
to run into a situation where we would want duplicate records.

Sometimes we create a logical with additional keys to "eliminate"
duplicates, perhaps you could try this?

Colin's post has useful information to keep in mind also.

S. E. Tuthill
A73 C11 V5R3



From: Colin_Hugill [mailto:[email protected]]
Sent: Thursday, May 12, 2005 7:48 AM
To: [email protected]
Subject: RE: CPYF/SQL INSERT problem - Duplicate Key


Hmmmnnn, SKOREK where did you study computer analysis?



Question 1. Did you carefully and thoroughly review (emphasis on
"thorough" and "careful") the joblog when you tried the CPYF command?
Probably not, because the copy normally stops at the error record - so
the remaining (in your terminology "uncopied") records may, OR MAY NOT,
cause duplicated keys. There is enough information in the detailed
joblog to find the record causing the error, if you look properly.



Question 2. Why would you want to copy records that cause "duplicate
key" errors?? The message is trying to tell YOU something. Maybe you
haven't done enough analysis yet or maybe what you are trying to copy
has already been copied in an earlier attempt and therefore shouldn't be
copied again! Maybe you should clear the target file and start again.



I am sure there are several organizations that offer in depth courses on
IBM Commands and Basic Analysis, you should use them. This list is
about JDE World software - you might help yourself by letting us know
what files you are using (i.e. copying from and copying to).



Regards,



Colin



Colin Hugill (Consultant) (World A7.3 cum12)




_____


The entire <http://www.jdelist.com/ubb/showflat.php?Cat=&Board=> JDELIST
thread is available for viewing.



Looking for a job? Check out the Job


This is the JDELIST World Mailing List.
The instructions on how to unsubscribe from any JDELIST mailing list are
available here <http://www.jdelist.com/unsubscr.shtml> .
JDELIST is not affiliated with JDEdwards(r).

.

[ QUOTE ]
Tomek wrote:
I need to copy some records form one to other file and using SQL INSERT operations I get an error:SQL0803 - Duplicate Key Errors.

When I try use cpyf some records are copied not everything. The rest - uncopied records - are with duplicated keys.
And the question is: how to copy this duplicated records?


[/ QUOTE ]

Tomek -
It is obvious from your posting that the file you are copying FROM is keyed differently from the file that you are copying TO. The iSeries file manager will NOT allow you to create duplicate records in the TO-file if the keys are specified as being UNIQUE.

For example:
I have created a process that allows me to copy data from an AS/400 journal back into a physical file to allow easier examination of WHO changed the data in the physical file.

We were analyzing changes to the menu master file (F0082), but the menu master is keyed uniquely on the menu name (MNMNI).

However,if several changes were made to the same menu record in F0082, this could result in several records in my output file with the same key value, so I created a work file for my output that had the same exact record layout as F0082, but I took the UNIQUE keyword away to allow multiple records for a given menu.

As Colin said, you need to better analyze what you are trying to accomplish before posting...this is really not a JDE-specific question.

By the way, if you don't care about the duplicates, CPYF will ignore the duplicate key errors (but it still won't copy the duplicates to the target file) by specifying ERRLVL(*NOMAX).


Regards,

Tomek,

This is not a JDE World issue, but an AS/400 DB2 issue. You have at
least one file (could be the physical file or one of the logical files, or
could be more) defined as requiring unique keys. So, for example, if the
key is employee number, you could NOT have two or more records in the file
for employee number 51349. DB2/400 will not allow entry of duplicates for
files with an access path defined as requiring unique keys. So whatever you
are trying to do is wrong, and you need to figure out why you are doing
something that creates duplicate keys.

John Dickey
JDE Financial Systems Administrator/Analyst
White-Rodgers, division of Emerson Electric
8100 West Florissant, P.O. Box 36922
St. Louis, MO 63136
314-553-3067

Re: RE: CPYF/SQL INSERT problem - Duplicate Key

Sorry Colin,

maybe first message was to quick or maybe I didn't described it properly.

So more detail:

there are duplicated records if we look at i.e. 3 key fields, but if we take into consideration more fields - it is not truth, records are not the same. If I search in destination file records which are described as duplicated - they don't exist, but error still occurs :-(

.....

a few notice after hours ...

key fields ... key fields ...
OK, but which key fields??? in SQL and CPYF I use PF without any key (I mean in PF key is not define - in LF yes, but not in PF)

So how "works" checking if record are or are not duplicated??

both files haven't UNIQUE option.
both PF haven't any defined key.

stuthill wrote about using logical file .....

well ... probably if I use logical for open query it should help. I have to check it.

And sorry again ... I know ... I should think and wrote and think and think before posting any post of this group.
yes I know - my ...

.....

a few notice after next hours ...

yes - I'm stupid

And this is my greatest discovery this day

Regards

Tomeks

Re: RE: CPYF/SQL INSERT problem - Duplicate Key

Hi, Tomek -

If your PFs are not keyed, then this error comes from one of the logical files (LFs) that is defined over the physical file you want to copy into. There should be indication in the joblog which file is causing this error. Then you can review the keyfields for this LF and it should be clear what is causing the duplicates. Hope this helps!

RE: RE: CPYF/SQL INSERT problem - Duplicate Key

Tomeks,

Hmmm, are you an SQL techy? I do not know what the SQL term would be
for physical files and what the SQL term would be for logical files. You
first have to find the file(s) (could be the physical file or one of the
logical files that are built over the physical file) that are defined as
having unique key required. Yes, there could be more than 1 uniquely keyed
file involved. Say the physical file is called FILE1. You would have
logical files called FILE1L1, FILEL2, and FILEL3. Say FILEL3 is uniquely
keyed. So even if your SQL or copy is to file FILE1, your action must still
satisfy the unique key requirement of file FILEL3. If it does not satisfy
the requirment, you will get the DUPLICATE KEYS error message. Hopefully
you have someone on site who can help you research this problem further.
Maybe you need to bring in an IBM consultant to help you out? This is going
to be impossible to resolve on this list. We would need to be on your
computer to figure this out.

John Dickey
JDE Financial Systems Administrator/Analyst
White-Rodgers, division of Emerson Electric
8100 West Florissant, P.O. Box 36922
St. Louis, MO 63136
314-553-3067

RE: RE: CPYF/SQL INSERT problem - Duplicate Key

Maybe I am speaking out of school here, but I thought the logicals were
views. Reason being we have discovered duplicated data in Physical files
before that came in through a logical which did not include the whole key
that the other logicals had. Also I have seen logicals using a tool called
DBM that had filters on them to restrict certain data such as closed
orders. From this I concluded that the logicals were views and not
necessarily requirements. Having said that, the physical files that do use
logicals generally only mix and match the mey sequence of the same fields
and as long as your programs work through the logicals and do not interact
directly with the physical files.
Good Luck
Hal

Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC



John=5FDickey <[email protected]>
Sent by: [email protected]
05/12/05 02:22 PM
Please respond to
JD Edwards=AE World <[email protected]>


To
[email protected]
cc

Subject
RE: RE: CPYF/SQL INSERT problem - Duplicate Key






Tomeks,

Hmmm, are you an SQL techy? I do not know what the SQL term would be
for physical files and what the SQL term would be for logical files. You
first have to find the file(s) (could be the physical file or one of the
logical files that are built over the physical file) that are defined as
having unique key required. Yes, there could be more than 1 uniquely keyed
file involved. Say the physical file is called FILE1. You would have
logical files called FILE1L1, FILEL2, and FILEL3. Say FILEL3 is uniquely
keyed. So even if your SQL or copy is to file FILE1, your action must
still
satisfy the unique key requirement of file FILEL3. If it does not satisfy
the requirment, you will get the DUPLICATE KEYS error message. Hopefully
you have someone on site who can help you research this problem further.
Maybe you need to bring in an IBM consultant to help you out? This is
going
to be impossible to reso! lve on this list. We would need to be on your
computer to figure this out.

John Dickey
JDE Financial Systems Administrator/Analyst
White-Rodgers, division of Emerson Electric
8100 West Florissant, P.O. Box 36922
St. Louis, MO 63136
314-553-3067

Re: RE: CPYF/SQL INSERT problem - Duplicate Key

Tomek,

Please DON'T TRY Seth Tuthill's approach with another logical - Seth and I have corresponded offline and what Seth is referring to DOES NOT apply to your current problem, Also you lack enough technical experience/knoweldge on the AS/400 (iSeries) to even begin to play with what he referred to.

Sally White, Steve Landess, and John Dickey have all posted some pretty solid replies - I suggest you print them all then read all their replies together, very carefully. Then take a break have a cofffee and something to eat. Then come back and look at your problem again. Maybe you must get someone to help you dig deep into the joblog and get the basic details to identify the target (copy to) file or it's related logical(s) that is causing you so much grief. Also the log can tell what record number in the from file (maybe also the record in the to file) that results in the duplicate key error.

Good luck and good night......Colin

RE: RE: CPYF/SQL INSERT problem - Duplicate Key

Hal,

Well, you are close, but not quite. Take JD Edwards world. File
0911, which we all "love", is a physical file, but with no keys. File
F0911LA, a logical file over physical file F0911, is UNIQUELY KEYED, as
defined by JDE. If you were to have a program trying to write to F0911
using F0911 as the file name in your program, and you try to add/write a
record with a key value list that exists for F0911LA, you are going to get
an error message saying DUPLICATE KEYS and the record will not be written.
If you try to write the record using another F0911 logical file, you will
still get the duplicate keys error.

Duplicate records will be allowed if none of the files
(physical/logical) are defined as UNIQUE KEY. Look at file F0911LB. This
logical has a key list, but it does NOT have a unique key requirement. That
is the subtle distinction that I suspect you are missing in your
understanding. In my experieince most AS/400 files are not defined with
UNIQUE keys, so it is understandable that many people do not fully
understand this unique key situation.

John Dickey
JDE Financial Systems Administrator/Analyst
White-Rodgers, division of Emerson Electric
8100 West Florissant, P.O. Box 36922
St. Louis, MO 63136
314-553-3067

RE: RE: CPYF/SQL INSERT problem - Duplicate Key

Tomek,

Please DON'T TRY Seth Tuthill's approach with another logical - Seth and
I have corresponded offline and what Seth is referring to DOES NOT apply
to your current problem, also you lack enough technical
experience/knoweldge on the AS/400 (iSeries) to even begin to play with
what he referred to.

Sally White, Steve Landess, and John Dickey have all posted some pretty
solid replies - I suggest you print all of them, then read all their
replies together, very carefully. Then take a break have a coffee and
something to eat. Then come back and look at your problem again. You
must get someone to help you dig deeper into the joblog and get the
basic details to identify the target (copy to) file or it's related
logical(s) that is causing you so much grief. The log can also tell
what the record number is in the from file (maybe also the record in the
to file) that results in the duplicate key error.

Good luck and good night......Colin



Regards,



Colin

RE: RE: CPYF/SQL INSERT problem - Duplicate Key

Interesting, thanks for the info.


Hal McGee
Engineering Data Manager
Group Engineering - Process and Compliance
Seating Products Group
B/E Aerospace
Winston-Salem, NC



John=5FDickey <[email protected]>
Sent by: [email protected]
05/12/05 03:08 PM
Please respond to
JD Edwards=AE World <[email protected]>


To
[email protected]
cc

Subject
RE: RE: CPYF/SQL INSERT problem - Duplicate Key






Hal,

Well, you are close, but not quite. Take JD Edwards world. File
0911, which we all "love", is a physical file, but with no keys. File
F0911LA, a logical file over physical file F0911, is UNIQUELY KEYED, as
defined by JDE. If you were to have a program trying to write to F0911
using F0911 as the file name in your program, and you try to add/write a
record with a key value list that exists for F0911LA, you are going to get
an error message saying DUPLICATE KEYS and the record will not be written.
If you try to write the record using another F0911 logical file, you will
still get the duplicate keys error.

Duplicate records will be allowed if none of the files
(physical/logical) are defined as UNIQUE KEY. Look at file F0911LB. This
logical has a key list, but it does NOT have a unique key requirement.
That
is the subtle distinction that I suspect you are missing in your
understanding. In my exper! ieince most AS/400 files are not defined with
UNIQUE keys, so it is understandable that many people do not fully
understand this unique key situation.

John Dickey
JDE Financial Systems Administrator/Analyst
White-Rodgers, division of Emerson Electric
8100 West Florissant, P.O. Box 36922
St. Louis, MO 63136
314-553-3067

Tomek,

World files did not always have unique keys on them. This causes a lot of problems if you ever decide to move to OneWorld. If you want to keep the duplicates - which in most cases, they're not really dups (since the Time is considered to be the uniqe key). Look at the logicals ontop of if and see what identifies this file with a unique key.

It is apparent that you have several things populating this and not all of them use keyed logicals (indexes). Create a copy of the file (with no data) into a temp library and then (making sure that there are no logicals on it), copy the data over.

If you are trying to copy to a file with different columns, then you will have to use the *MAP/*DROP parameter to get them all populated.

Hope this helps,

Kristian

RE: RE: CPYF/SQL INSERT problem - Duplicate Key

Hal,



I just saw John Dickey's reply. I will attempt to add a little more
clarification (John Dickey I hope you are not offended - oh yes and
Steve Landess is probably writing something on this topic as a scribe
away here). In fact there was a JDE lister by the name of Jackson
(Richard, I think) whom I held in awe and regarded as an AS/400 systems
programmer. If you are still monitoring this list Mr. Jackson, I would
appreciate your clinical and precise comments.



I go back to the S/38 days very early in the 1980's when logical files
were called "views". Generally they still are even though the S/398 is
long gone.



They were called views probably because Relational Data Base concepts
were pretty new to most people in the mid range arena (and quite a few
in the Mainframe era at that time) and trying call them indexes caused
problems for everyone from S/32 and S/34 (and later one S/36) who had a
different concept of indexes . They were also called "views" I think
because their "definition" controlled what records (and fields) a
process (program, command, utility etc) would see when it used that
logical file. A logical is an index over the based on physical file
named in the file's source specs (let's not get into joined files here
pulleeezzz).



Each logical file (view) has its own defined key list. That is - key
field names, listed in order or sequence to make up the full compound
key. So in theory 10 logical views over one physical file could all
have different key field names with none repeated between any of the ten
logical files.



You can define a logical file (view) that contains only a subset of the
fields in the "based on" physical - using such a logical would
effectively hide the existence of the unnamed fields. You can, if you
know how, add additional fields in a logical that change the type of
date (e.g. from Numeric to Alpha, sub string values from existing fields
etc. Some JDE'ers will know all about how the data for a field in one
file is stored in another file in a different format - this is how you
overcome it and maybe define a join between the files using the
redefined/sub string data etc.)



You can also nominate SELECT/OMIT criteria in a logical file (view) that
determines what records are included in the "view". John referred to
F0911LR - there is another view used by the Fixed Asset post process to
identify only posted F0911 records that have not been posted to fixed
assets but can be. It uses SELECT/OMIT criteria based on field BRE
(amongst others) to only include the required records - why, because the
FA post process doesn't need to read the whole F0911 to find what is
needed for FA posting. Again there is another logical view over F0911
that is used by the Workfile generation process to prick only records
eligible for Workfile generation but have yet to be processed. In both
cases if you have 11,000,000 (yes million) records in F0911 and run the
Workfile generation about 10 times a day and the fixed asset post about
8 times, you can guess how much machine resource you are wasting and how
much the user temperament might be changing.



It is the Data Base Management function (buried down in the micro or
"machine" level code below the user machine interface level) that does
all index checking and maintenance including duplicate keys. You know
this function generally as DB/400. DB/400 will not allow entries to be
added or changed to a physical that would result in a duplicate entry in
a "related" logical that is defined as keyed UNIQUE. To do this it has
to know the data base relationships between a physical file and all its
related logicals - hence some insight into were the DSPDBR command comes
from. DB/400 as the DBM checks against the index entries for all
related logical files (views) defined as unique before committing
(excuse the loose use of the commit word) the write or update to the
physical file. It has to because there may be other concurrent
processes also using the same physical via different "logical views" and
the indexes must be up to date for those other processes.



Yes - you often see many logical files over one physical that only seem
to differ in the sequence of the key fields BUT this very often is NOT
always the case, assume the latter to be the case until disproved.



Yes - physical files can be keyed but often are not - they do not need
to be keyed. I am a bit of a dinosaur - back in 1981/2 I was taught
never to key physical files when designing/spec'ing. The problem being
that a RGZPFM will destroy the arrival sequence of records in the file.
It may be a different story these days. The again the DBM has improved
enormously (OPNQRYF and SQL as well) as has everything else to do with
what we know now as the iSeries.



Yes - the definition of a logical file can create "requirements" (your
term) which in turn are controlled by the DBM (data base manager -
DB/400 which is integrated into the Operating System of the AS/400 or
iSeries).



No - when your program works through logical files (views) it is almost
certainly interacting with the "based on" physical file(s) - the Logical
Files are basically just index entries with an internal address pointing
to the location of the physical record. Apart from key values, the data
your program requires is retrieved from the physical file record, the
data is not in the logical file.



Yes - I have twice seen duplicate records in a physical file that had
related uniquely keyed logical views in 1989 and 1991. It was caused in
both cases by some horse thieving characters that pulled some smart
tricks (amongst other things, with CPYF parameters and keywords, and the
parameters and keywords in the "create logical" command - CRTLF and
maybe also the parameters for CRTRPG program and maybe also some MONSMSG
statements). They wanted to get all records across in the conversion
because we were red hot on reconciling on a file, by file, basis. They
didn't pay much attention to what was happening with the logicals. I
call these types cowboys - all "yippy yi oh" and nothing solid (what do
expect if they eat nothing but beans), they can change horses real fast
(but can also be run out of town on a rail subject to meeting HR and
political correctness standards).



Should duplicate keyed records exist - NO - at least not in the normal
way, you had to do something very clever and tricky. Remember I am
talking about "early" AS/400 days in 1989 and 1991. For some strange
reason, that escapes my memory now, it seemed that the logical views
dropped (did not include) the index entry for the duplicate record.
Checking the physical in arrival sequence we finally identified the
missing entries in the logical - the logical was created from the
physical by reading it from record one through to end - as being the
second and subsequent duplicates. John is almost certainly correct when
he suggested that none of your logical files were defined as Unique -
the extension is that if any views were defined as unique then the
duplicates related to fields not named as keys in the Unique view(s).



I am sure Steve Landess and/or John Dickey have the down and dirty
technical knowledge to clarify anything I have misrepresented here and
they could also explain how duplicates might have happened (in all the
gory detail - but who wants to know how, just don't do it).



Phew..goodnight,



Colin

Re: RE: CPYF/SQL INSERT problem - Duplicate Key

Tomek -

Either the physical to-file or one of its logical files has unique key requirements that is causing your duplicate key errors.

Here is one technique for problem determination:

1) Sign on to the system

2) In your interactive session, execute the command
CHGJOB LOG(4 00 *SECLVL) LOGCLPGM(*YES)

3) Now execute your CPYF command or SQL statement and force the duplicate key error to occur

4) Then interactively execute the command
DSPJOBLOG OUTPUT(*PRINT)

5) Use WRKSPLF to find the output from DSPJOBLOG and scan the resulting QPJOBLOG spooled file for CPF5009 (Duplicate record key in member xxxxxx). The second-level text of this message will specify the the RRN of the record in the FROM-file that caused the duplicate key condition to occur during the insert or copy.

The data in this From-file record contains the duplicate key values...use STRSQL to start interactive SQL, then execute this SQL statement:

SELECT * FROM FROMFILE where RRN(FROMFILE) = RR#

(where FROMFILE is the From-file in the CPF5009 message and RR# is the relative record # from the CPF5009 message)

6) If your to-file is a logical file, use DSPFD to determine the name of the physical file upon which it is based.

7) Once you know the name of the physical to-file, use the command
DSPDBR FILE(PHYSICALFILE) OUTPUT(*PRINT)
to print a list of all of the logical files that are dependent upon this physical file, and then use DSPFD on each of the logical files to see which ones have unique key constraints.

8) You should now you have all of the information necessary to determine the cause of your problem.

Regards,

RE: RE: CPYF/SQL INSERT problem - Duplicate Key

Tomek,
If you face a Duplicate Key error when using CPYF or SQL then it means the
important or KEY fields already exist in the TO File.

There are several options if you insist on copying the Duplicate records
across.

0. Get system administrator or senior programmer to guide you

1. Do a CLRPF on the TO FILE to completely clear the TO file before you do
an SQL or CPYF. Use this option if you don't need all the records in the TO
FILE

2. Compare the the KEY fields in TO FILE and FROM FILE and only CPYF or SQL
fields that do exist in TOFILE. and UPDATE those exist in TO FILE.





Cheers!
___________________
Marsh R Narewec
STC Computer Services

Re: RE: RE: CPYF/SQL INSERT problem - Duplicate Key

Tragedy!!!

And not with file ...
Yesterday ... I mean in night I wrote by TWO hours explanation post ... detalized my cases ... it was a big post ... and I go to JDElist today ... and ... nothing, my post doesn't exist ...

yea ... woman are `dangerous

at 3 in night my wife went to me ... and you know ... one kiss, second kiss ...
and you know ... by big impression I click "close window" instead of "sent post"

Have you a nice weekend (and nice) guys ;-)

Regards

Tomek

Re: RE: RE: CPYF/SQL INSERT problem - Duplicate Key

nice night of course