Excludable History Type tables from PD to PY Environment Copy (this time is urgent)

[Zoltan_Gyimesi]

Hi JDEList Community,

Prolog:
First of all excuse me, that I will post this onto the Application, CNC / Tech and Developers Forum
Currently I am totally out of CNC Tech and Application Consultant help and this time this issue is really URGENT & IMPORTANT.
Thanks in advance your appretiations & Please respect, I am just a developer.

Platform:
XE SP2, Oracle Database, Windows Enterprise Server with not a robust one resources

In the affected scenario I narrowed my suspection to 13 standard tables, based on description and/or record count. I will attach them to my post in an MS Excel file. Please, check it.
I suppose, these tables stores Change History entries, which not required in PY environment after an Environment Copy from PD.

The last PD to PY Environment Copy copied these tables and the duration time of the action almost exceeded the 20 hours.

Now we do not have so large time-frame and I would like dramatically decrease the duration time.

I am in doubt, because I am not sure which table exclusion will not cause SURELY any issue, problem in PY, and which can cause.

Please, let me know, which tables of these can I SURELY exclude from PD to PY Environment Copy?

As I mentioned, it is urgent and I need this info on this Saturday optimaly before 10:00 AM (UTC!), but hopefully (not sure) won’t be late up to 04:00 PM (UTC!)

Finally, I would like to mention, that the rquired Table Space also an issue as well.

Please, ask me, if I wasn’t clear enough.

Great Thanks in Advance & Regards,

Zoltán
…just a developer

 

[Larry_Jones]

Re: Excludable History Type tables from PD to PY Environment Copy (this time is urgent)

Zoltán,

Most of these tables are in active use by the system. All need to be there whether there is data in them or not. Some I can speak directly to:

F4074: Contains order specific (Purchase and Sales Order) special pricing rules/adjustments. It is NOT a history table even though the Row Exits are mis-named "Price History".

F42019/F42119: Depending on how setup Sales Orders move from F4201/F4211 into these tables after they have closed. Customer Service Inquiry (P4210) is based on a view that is a UNION of F4211 and F42119.

F42199 and F43199 are in fact just change history tables. Not critical unless you care about change history.

Are you using R98403 to copy Data between Path codes? This is a VERY slow way to copy large amounts of data. For an Oracle Database a good method is using the DataPump export and import utilities (avail with Oracle 10G and higher).

Regards,

 

[Zoltan_Gyimesi]

Re: Excludable History Type tables from PD to PY Environment Copy (this time is urgent)

Hi Larry,

Thanks for your reply.

[ QUOTE ]
Are you using R98403 to copy Data between Path codes? This is a VERY slow way to copy large amounts of data. For an Oracle Database a good method is using the DataPump export and import utilities (avail with Oracle 10G and higher).

[/ QUOTE ]

I know, R98403 is very slow, but up to know, it wasn't an issue for our CNCs - and I am not a CNC guy at all.

One of our "old" CNC guy is far away on holiday, the other young guys are very good, but they are already not available for week.

At last, but not least, the requirement raised to late and there wasn't enough time to consider the possibility to use other faster method, to elaborate it and test it. Sure, we will do it in the near future.

The copy is not my job, an other guy did last weekend and will do this weeken, but on the other side, I am very much involved in this project and have a lot of work in it - on the other hand, I will have a lot of other job in this project on this weekend.

Mainly after your reply, currently I tend to be on the safe side this time and let R98403 run almost 20 hours. It will overlay "only" with 4 scheduled export process (also created by me in an other project). It didn't caused any issue last weekend - except the long duration, hopefully will not cause this time too. By my consideration, this has less risk.

Regards,

Zoltán

 

[pfd]

Re: Excludable History Type tables from PD to PY Environment Copy (this time is urgent)

Zoltan,

Larry is right about the tables in your list. The biggest tables in our system is F4111 (Cardex),F3111 (WO parts list) and F0911(GL). We generally purge F3111 using the vanilla JDE program R4801P. Depending on the size of your F3111 if that is something that you can do between today and tommorrow it may help. You could purge the cardex F4111 since it is a history table. you could purge F0911 using R09911 but that is more complicated and you could consider doing the next time you copy data from PD to PY.

Or better still using a better method other than R98403 to copy the data!!

Thanks,
Matt

 

[Zoltan_Gyimesi]

Re: Excludable History Type tables from PD to PY Environment Copy (this time is urgent)

Hi Matt,

[ QUOTE ]
Thanks,
Matt

[/ QUOTE ]

The thanks is my turn

Saving, truncating some Ledgers is always amoung our targets.
Unfortunatelly, currently we do not have the appropriate human experts resource available for it.

All of this issue is not my responsibility, I just would like to decrease the duration time of the process (not to overlay with scheduled export UBE) and reduce the required table space.

To be on the safe side, I will exclude only some of our (not mine) custom table.

FYI:
The largest table in this instance (based on record vont, not on occupied table space) is F42199 S.O. Detail Ledger File, which counts 150% more records, than F0911 (GL) + F4111 (Cardex).

Thanks & Regards,

Zoltán

 

[slandess]

Re: Excludable History Type tables from PD to PY Environment Copy (this time is urgent)

[ QUOTE ]

Zoltan wrote:
FYI:
The largest table in this instance (based on record vont, not on occupied table space) is F42199 S.O. Detail Ledger File, which counts 150% more records, than F0911 (GL) + F4111 (Cardex).


[/ QUOTE ]

Zoltan -

IIRC, Credit Note from history Sales Order Entry either uses F42199 or is configurable to use either F42119 or F42199 as the source of data for return orders.

Please verify this with an SOE application consultant or B/A before purging/truncating Order Ledger (F42199) or Order History (F42119) records.

 

[pfd]

Re: Excludable History Type tables from PD to PY Environment Copy (this time is urgent)

Zoltan,

So how did the environment copy go? Any lessons learnt??

Thanks,
Matt

 

[Zoltan_Gyimesi]

Re: Excludable History Type tables from PD to PY Environment Copy (this time is urgent)

Hi Matt, Larry & Stephen,

First of all, thanks all of your help.The only help that I got, camed from ny developers colleague, that I surely can exclude 2 of his custom history tables.

I was really alone in these decisions, finally on one's own responsibility excluded 11 of 13 tables in the attached list and the mentioned 2 custom tables

I do not excluded:
F4074 Price Adjustment Ledger File
F4115 Item History

Before environment copy I deleted the contents of the excluded tables in PY7333 (Re-Created them in OMW

)
- do not leave out of synch data from the previous environment copy
- making more free space for the work in PY7333 in the already allocated table space

This way I excluded approx 21 million record - for comparision - count of records in F0911+F4111 General and Item ledger was approx 8 million.

The duration time of the copy was half of the previous action: 9 hours and 20 minutes.

PY7333 is already in use from sunday evening and and no probal araised up to now.

Thanks again & Regards,

Zoltán