Hi

Problem : Trying to upload 10000+ employee records for a test using data pump
Have set the MAX_ERRORS_ALLOWED parameter to a high number.

Still data pump seems to stop after the first record in error. The concurrent process ends with status "completed"
and the remaining records are not processed at all.

Any ideas ?

Thanks
Vin

Another question..more data migration strategy related.

Does SHN & worthy down projects use data pump for all the entities they are migrating or is it a combination of data pump and APIs?

Thanks

Hi Vinoo,

A while ago I was playing around with the idea of using WEB ADI to load employee records. The idea being to supply the user with a blank spreadsheet generated by WEB ADI (based upon the create emp api), let them fill in the details and then to upload the details straight from the spreadsheet.

Can't remember how far I got with it but if you still have a problem on Monday afternoon I'll try to dig out what I did and pass it onto you.

Pat

Vin, you bet your sweet kazoo we're using Data Pump here at WD! Even for our own APIs. That was an edict from the project management right from day 1 apparently.

In answer to your original question: I'm not a betting man, but if I was I'd have a shilling (each way of course) on your batch lines having the same link value. If that's the case, make the link values different (or null) - basically, the link value controls how D-P sees related rows - it regards all records of the same link value as a contiguous sequence of data - the first time a record in a given link value fails, it ignores every subsequent record with the same link value. For a bunch of 'create employee' batch lines this doesn't really make sense - ie we shouldn't stop loading subsequent employee records just because the first one failed. With assignments it's more logical, as if you think about it you'd really like to be confident in loading the historical rows in date sequence - if the second record for an employee fails, then loading the rest of the rows will cause problems if you try and reprocess the batch having fixed the problem (eg datetrack mode of 'UPDATE' is no longer valid as there are now future-dated rows!) To that end, we have in the assignment history batch a different link value for each employee, but the assignment batch lines for the employee all have the same link value. If that makes any sense at all..... probably not...

Clive

A grateful round of applause from the small team here.

UPDATE hr_pump_batch_lines
SET link_value = NULL
WHERE batch_id = <employee load batch id>
did the trick.
and lo and behold!...data pump gets a life again

Cheers
Vin

Pat

Has the Web ADI approach been rolled out to production users. Might be worth considering if it is stable.

Cheers
Vin

Vin,

No. The project was shelved and the concept of using Web ADI and Excel for "small" data loads put in mothballs until it is required.

Pat

Late to the party as always.

Here at the nations health project, the original migration process was designed to use DP.
However, the whole process has been re-engineered to use APi's driven by scripts instead.
Reason? Performance. Pure and simple.

Our production system now currently stands at 180,000 employees, and we're still only 1/12 of the way through the rollout. Some of the API's are behaving like dogs (element entry is a big culprit) but running them with DP exacerbates the situation.

I do recall a past visit to a well-known-press-agency implementation where they ended up re-writing all of the api's because of performance issues with bulk data...

We have raised some bugs with Oracle, specifically to do with the element entry api performance. Whether they actually see the light of day is another story.

We were told we 'had' to use DP - however, with a somewhat liberal interpretation of the rules, we were able to write our own wrapper for DP to operate on. We didn't need to do this in too many places, but one of those we did do was elements. As we saw it the use of DP for element entries was problematic in that deciding which pump process to use (create or update) was tedious, and in some cases impossible because of some of the validation relying on the presence of a session date. Furthermore, the sheer volume of user-keys being generated by some 500K rows of data on top of all the other stuff in there would have been detrimental for the overall performance of the load process. After all, the user keys table gets hit by virtually everything that DP does - either inserting into, or reading from. So our element wrapper - somewhat unimaginatively called 'MAINTAIN_ELE_ENTRY' is a generic 'insert or update depending on whether or not the link is a standard one' sort of process, with no ele entry user key reliance or creation involved. I have to say that, given that we have just completed the live migration for a well-known air force loosely based in the UK, which encompassed some 3.3M rows of data of which 600K were element entries - and come in 1.5 days ahead of schedule - it wasn't too shabby. Obviously not the volumes that our health-conscious friends in the Midlands have to contend with, but encouraging nonetheless. TBH the main issue we've always had is the background trigger-related activity - particularly OAB *spits on floor*

Our element entry value table is just a shade over the billion-row mark....