AS400 to SQL Conversion

bradbeckett

Member
Sorry in advance about the long post (and my first).

I’m new to the Edwards world, so please bear with me.

We are currently evaluating moving from AS400 World to SQL One World.

AS400/820 V5R1 World A7.3Q10
Data on AS400 is from 1995 to 2003

SQL Install B7334 SP20.

Procedure followed:
AS/400
Copy data from Prod to CRP
Apply Coexistence PTF to CRP Environment.
Run All applicable UBEs and create tables from OMW on AS/400 CRP data.

Run R98403 copying data from the AS400 CRP to the SQL CRP

During the R98403 (which takes 5 pcs 2 days to complete), we have some errors.
F0607 is a prime example:

On the 400 it is set to allow duplicate keys, on the SQL box it isn’t allowed.

Of our 24847 Records on the 400, 24363 transfer successfully to SQL, 484 fail.
If we run the following query on the 400:
SELECT JIAN8, JIWANO, JIPPED, JIPDBA, (Count(*)) AS Count
FROM F0618 GROUP BY JIAN8, JIWANO, JIPPED, JIPDBA HAVING ((((Count(*)))>1))
It shows the Duplicate keys on 484 Records

This is happening on about 10 files total, but they are all following the same pattern, not allowing duplicate key errors.

Is it possible to allow duplicate keys on the SQL box?
Has anyone ever run across anything like this before?

TIA
Brad
 
Unfortunately, the copy table operation generates a new table with new indices when it starts the copy operation. This means the unique key constraints are already in place when the records start copying over.

So the question now becomes: how do you remove the unique key? I see 2 ways: eliminate the unique key by modifying the original object, or eliminate the unique key by dropping the indices in SQL Server.

Since you know which files are in error, and the number is relatively small, you can try to manually do the table copy, have somebody ready to drop the indices as soon as you start the copy, and see if all the records make it across.

For safety though, you would probably want to do the first option. After you are done, you can restore your object from JD7333. You should probably do some data analysis and see why you are getting duplicate records. The same unique key constraints should have been on the AS/400 to begin with, and not allowed the duplicates.
 
On the AS400 you need to apply the PTF for the coexistence before you
move the files fo the sql. The PTF add fields to the files to allow for
the conversion. the conversions are special jobs on the Cum 13 PTF wich
needs to be applied.







bradbeckett wrote:




Consultant Programmer Analyst Functional for 25 years in manufacturing Industrial Engineering by training and programmer by training
 
rbonde

thanks but we already did the coexistence PTF.. this is my 400 procedure

Copy data from Prod to CRP
Apply Coexistence PTF to CRP Environment.
Run All applicable UBEs and create tables from OMW on AS/400 CRP data.
 
Ken,

I was not here for the first 4 years of the AS/400 world enviroment, so I can not say why these 10 databases are different, I just know they are. I have a gut feeling the orginal AS/400 installer changed these files on the 400 side, but I am going to have to investigate further to see if this is true.

If so I agree with you that the best idea would be to fix the objects on the 400, delete the objects on the SQL server, and rerun r98403 on the objects in question.

The time consuming part is tracking down the 1600 bad records in the 9.6 million record 0902.. well, maybe...

Thanks for your help, I will look deeper into this issue tomorrow.
 
Brad,

I did this once before, and I had to run the Payroll conversion from the
AS/400 CRP environment to the new SQL Server database.

Did you try this?

Andy

_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail



CNC Consultant
AS/400, SQL 7/2000, TSE and Citrix
 
Brad, Bob Kelly and Phil Juffs have done a few of these conversions in the past for JDE clients. They can be reached at (503) 598-1115 or you can send them an e-mail at [email protected] or [email protected].
bradbeckett <[email protected]> wrote:Sorry in advance about the long post (and my first).I’m new to the Edwards world, so please bear with me.We are currently evaluating moving from AS400 World to SQL One World.AS400/820 V5R1 World A7.3Q10Data on AS400 is from 1995 to 2003 SQL Install B7334 SP20.Procedure followed:AS/400Copy data from Prod to CRPApply Coexistence PTF to CRP Environment.Run All applicable UBEs and create tables from OMW on AS/400 CRP data.Run R98403 copying data from the AS400 CRP to the SQL CRP During the R98403 (which takes 5 pcs 2 days to complete), we have some errors.F0607 is a prime example:On the 400 it is set to allow duplicate keys, on the SQL box it isn’t allowed.Of our 24847 Records on the 400, 24363 transfer successfully to SQL, 484 fail.If we run the following query on the 400:SELECT JIAN8, JIWANO, JIPPED, JIPDBA, (Count(*)) AS CountFROM F0618 GROUP BY JIAN8, JIWANO, JIPPED, JIPDBA HAVING ((((Count(*)))>1))It shows the Duplicate keys on 484 RecordsT!
his !
is happening on about 10 files total, but they are all following the same pattern, not allowing duplicate key errors.Is it possible to allow duplicate keys on the SQL box?Has anyone ever run across anything like this before?TIABrad
--------------------------
To view this thread, go to:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OW&Number=51324
+ - - - - - - - - - - - - - - - - - - - - - - - -+
This is the JDEList One World® / XE mailing list/forum.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found on the JDEList Forum at
http://www.JDEList.com

JDEList is not affiliated with JDEdwards®

+ - - - - - - - - - - - - - - - - - - - - - - - -+


---------------------------------
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online


World, OW B733X and Xe
 
Back
Top