Jump to content


Photo
- - - - -

Data Migration


27 replies to this topic

#1 Phil

Phil

    Expert

  • Members
  • PipPipPipPip
  • 187 posts
  • Gender:Male

Posted 10 March 2005 - 10:44 AM

Hi all,

I am having problems whilst migrating data from Pro-isam to Oracle.

The error is very intermittant and very irritating. ;)

I have created 11 Oracle tables from 11 .pro files and then created 11 functions to delete from the .pro files and add to the Oracle tables.

The functions are designed to run on to the next one, and this is where the problem is. If I run them singlely they are fine and the data migrates successfully, but if I tag them all together the 2nd function will always fall over, with the most common Oracle error message ; unique constraint.

I have inserted WAITS in between the functions but this doesn't resolve the issue either.

Any Ideas ?

Phil

#2 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 10 March 2005 - 11:10 AM

Have you tried a commit at LS or function exit ?

Are the files that you are converting dependant on each other ?

#3 Phil

Phil

    Expert

  • Members
  • PipPipPipPip
  • 187 posts
  • Gender:Male

Posted 10 March 2005 - 11:13 AM

Neil,

I tried an embedded SQL COMMIT in Function Exit Logic, which had no bearing on the problem.

And, no the files are not dependant on each other.

Phil

#4 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 10 March 2005 - 11:15 AM

Very odd,

Stupid question, if you truncate the tables before hand, does this have any bearing ?

#5 Phil

Phil

    Expert

  • Members
  • PipPipPipPip
  • 187 posts
  • Gender:Male

Posted 10 March 2005 - 11:23 AM

Nope.

#6 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 10 March 2005 - 11:31 AM

Hmmm, most common error is unique constraint violated right ?

Thats very strange

And have you tried a different second function ?

#7 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 10 March 2005 - 11:32 AM

Do the indexes on the tables match the key structure of the pro isam file you converting ?

#8 Phil

Phil

    Expert

  • Members
  • PipPipPipPip
  • 187 posts
  • Gender:Male

Posted 10 March 2005 - 11:40 AM

Yes, I have tried different functions, and it'll always fall over on the 2nd one.

Yes, the indexes on the Oracle tables match the key structure of the pro isam files.

All data that was written with CO/DIV = 'Y' has now been converted to have @COMP as the first key.

#9 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 10 March 2005 - 11:45 AM

If you change it from @COMP to something like XX_COMP ?

And setup XX_COMP = @COMP

Edited by Neil Hunter, 10 March 2005 - 11:45 AM.


#10 Phil

Phil

    Expert

  • Members
  • PipPipPipPip
  • 187 posts
  • Gender:Male

Posted 10 March 2005 - 11:59 AM

Still the same.

#11 Phil

Phil

    Expert

  • Members
  • PipPipPipPip
  • 187 posts
  • Gender:Male

Posted 10 March 2005 - 12:18 PM

Neil, you have helped me to identify the area of the problem.

I have just chosen 4 files that did not have CO/DIV 'Y' and therefore didn't need @COMP inserting. All 4 files migrated successfully.

On we go.................

#12 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 10 March 2005 - 03:08 PM

Cool ;)

So you fully up and running then ?

#13 Phil

Phil

    Expert

  • Members
  • PipPipPipPip
  • 187 posts
  • Gender:Male

Posted 10 March 2005 - 03:23 PM

;)


No ;)

#14 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 10 March 2005 - 03:32 PM

You still getting the same errors ?

#15 TalentedFool

TalentedFool

    Member

  • Members
  • PipPip
  • 43 posts
  • Gender:Male

Posted 10 March 2005 - 03:39 PM

Personally I would have a look at how the primary keys are setup within the oracle table and how the unique constraint index is set up.

This is prob a dumb statement but If you're only keying the file on the value of @COMP then the unique constraint against the table is going to fall over every time. Like I said - prob dumb question.

Can you post a DB schema for the table it falls over on?
Thanks

Lee



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users