Are there any Database Purge and Summarize shortcuts?

March 27, 2017 10:26 AM

Are there Database Purge and Summarize shortcuts?

The current production database has almost 10 years of part transaction and other data we would like to purge before going live with Epicor 10.1. So far this is painfully slow in Epicor 9. Anyone have a quicker way to do this and maintain data integrity?

Note: it goes much quicker in Epicor 10.1 but I’m trying to trim the database side before the conversion to speed up the process.

Thanks in Advance,

Chris Wineinger

Dorner Mfg

PartTran is broken. Epicor has a fix for the PartTran purge… At least on 9.05.702A they did. We received that fix.

Matt Caldwell
Information Technology Manager

1 Like

FYI: The database purge and summarize performance improvement SCR @ 9.05.702a is 132127 and we have the one-off readily available upon request for Progress/SQL/SQLU. To set expectations on it though, if there are tens of millions of records on PartTran/TranGLC that meet the purge criteria it could still take a weekend+ to complete in E905 even with this improvement SCR, so definitely complete a trial run against a non production database first to get the timing down.

Thanks! unfortunately, we are still on 905.701. No plans to stop on 905.702a before going to 10.

:’(

From this point on any suggestions on purging this data are going to ones you definitely don’t want me to see, so I’ll see myself out of this thread. :helicopter:

3 Likes

The upgrade requires a stop at 9.05.702A I believe, so you could theoretically incorporate the purge into your Upgrade cutover.

The database is too large at this point so a delay on the conversion (stop at 905.702a) would spill out of a normal weekend timeline for conversion

Regards,

Chris Wineinger

Chris,
How large is your DB?

I have been clearing PartTran and TranGLC records in SQL with a DELETE command, which is all the Purge and Summarize does, but I run into issues with the log size growing too large. I saw it is possible to copy the tables to temp tables with WHERE clauses then truncate the original table and copy the data back and drop the temp table. Has anyone attempted this?

John

< – Gets Popcorn for all the "You edit your production DB? " comments that are about to roll in…

2 Likes

Dude… if I were you I’d quickly remove that comment and hide under a rock.

4 Likes

just…wow

1 Like

4 Likes

320gb last I looked. Data goes back to 2008 which is why we need a purge.
Test conversion took about 32 hours going from 905.701 to 10.1.500.12.
That’s actual conversion time, not counting time between conversion steps.
Looking to put it on a faster box and go to 10.1.500.14+ to speed that part up also.

Regards,

Chris Wineinger

chris,

have you reached out to a partner or Epicor on that new conversion tool they talked about in the last mtg?

1 Like

That caused such a disturbance in the force and I was thrown back into this discussion.

My sincere apologies upfront, but, whoever led you to believe that it was OK to simply delete these records in SQL did you and your company a huge disservice.

Here is the pseudo logic of what the db purge and summarize does for PartTran records, emphasis is mine:

All PartTran records which have a TranDate <= Cutoff are deleted. For Inventory transactions a PartTran of TranType = ADJ-QTY will be created to represent the summarized ± quantity deleted for a company/PartNum/WareHouseCode/InvtyUOM/LotNum.

5 Likes

nice recap of the anarchists cook book nathan…

2 Likes

i have reliable info that they’ve improved that conversion tool to be multi-threading for large table dumps, greatly improving migration performance.

I seem to have hit a nerve :grin: For the record I am only doing this with a DEV DB that we use before moving changes in Production. I just don’t need 80GB of PartTran/TranGLC to do this and don’t want to add storage to our SAN.

John

1 Like

Glad to hear it wasn’t production, take your 80GB with you it is a lot better than botching your DB and then do a Purge Summarize in 10. Or run the Purge Summarize and then migrate but don’t do it yourself in SQL :wink:

1 Like