Table conversion performance

[HeidiC]

I need to load over 560K records from Item Master Z table F4101Z to F4101 through JDE program R4101Z1I. It took me 10 hours to load 100K records and crashed with a blue screen. I thought filling Short Item Number (ITM) by R4101Z1I might take a lot of time so I pre-filled the ITM in F4101Z1, that somehow improved the performance. (It took 8 hours to load 51K records if I didn't pre-fill in ITM.)

My DBA told me he saw many cursors open (up to 46) at the back end. Is there anyway I can improve the performance of this program?

Thank you for your help in advance!

Heidi

Xe Update4 Win2K SP18 Oracle 8.1.7<P ID="edit"><FONT SIZE=-1>Edited by HeidiC on 3/1/02 02:57 PM.</FONT></P>

 

[neil_shepherd]

Hi Heidi,

While going through the Z files is the recomended way as it ensures integrity, I'd consider writing directly to the F4101. If you do this outside of OW you'll have to ensure all fields are initialise with 0's or blanks, carry out all the date conversion, padding of fields such as BU and decimal scaling yourself. This should be much faster.

I'd load a small batch in to prove your routines first and do as much processing as possible against this data. The item master is fairly straightforward so this should be too much of a problem.

Good luck

Neil.

OW B733.3 (XE), SP 16
AS/400, OS V5.1

 

[HeidiC]

Thanks, Neil! Actually that's exactly what I am thinking now. It seems to me using an Oracle stored procedure to load these many records should be much faster. I am just a little worried about the data integrity since the Z program also at least populates data in Branch/Plant (F4102) and Item Location (F41021) and Item Master Tag (F4101T) as I am aware of. I wonder if there is any other important tables the program touches. (I will only convert data from F4101Z1 without touching F4101Z1A, at this point I am not concerned about the tables related to F4101Z1A.) Our DBA who watched the back end activities for me also found the Z program was conducting a lot of table IOs to other tables such as F4101A, F4101C, F4101M, F4101P. I am not sure if I can ignore these tables.

Please advice!

Thanks,
Heidi

Heidi
Xe Update4 Win2K SP18 Oracle 8.1.7

 

[neil_shepherd]

Hi Heidi,

You've identified all the tables I knwo of which are written to from the F4101Z1. Populating these should allow you to start processing against them. You should be fine. I imagine the rest must be from the F4101Z1A. I think you can get away without populating these. Easiest way is to manually enter an item with all the data you need and view if the tables such as F4101A have been populated, probably not if you haven't identified a need for the F4101Z1A.

Once loaded view the data via the item master and associated applications, they will highlight any fields they have problems with. You may want to carry out some integrity checking (eg Business unit exists in F0006) either in the stored procedure or afterwards via a custom program.

Regards

Neil.

OW B733.3 (XE), SP 16
AS/400, OS V5.1

 

[Larry_Jones]

Don't worry about those tables (F4101A, F4101C, ...). These are "temporary" tables used by the Item Master BSFN which was designed for interactive application use - and needed someplace to store item information between forms.

I'll second Neil's and others opinions about using 'Z' programs. They are very slow, "finicky", and difficult to work with. If you research the output / tables to be updated and are careful about filling *ALL* of the fields you can do a better job than the 'standard JDE solution' does. I use a custom UBE rather than do everything outside of JDE so that I can still use some BSFNs to handle stuff such as Next Number assignment, etc. Useful BSFN's I've found include:
- F4101 Write Item Master (B4100290)
- F4102, Update Item Branch Record (B4100840)
- F41021 Write Location and Cardex (N4101120)
- F4105 Update Item Cost (XF4105)

Hope this helps,

Larry Jones
[email protected]
OneWorld XE, SP 15.1
HPUX 11, Oracle SE 8.1.6
Mfg, Distribution, Financials

 

[raiym]

I was wondering if you would also need to look at the
F4111 (Cardex) files. I seem to remember them being
populated with the transaction data during the Z
Process.

Yathindra
XE Update 4 Win2000 AS/400

--- Larry_Jones <[email protected]> wrote:
http://www.jdelist.com/cgi-bin/wwwthreads/showflat.pl?Cat=&Board=OWDEV&Number=29982


=====
Yathindra
One World XE SP 13
Win NT AS/400

__________________________________________________

 

[HeidiC]

Thank you guys for your valuable input! I think I know what I can do now.

Thanks,
Heidi
Xe Update4 Win2K SP18 Oracle 8.1.7