RE: Import taking over 24 hours

If you saw the inner workings of CPYTOIMPF you'd see that is first creates a
secondary (interim) database file that has one record for each field in the
original database. So a 46 million record file with say 50 fields ends up
with an interim file containing over 200 million records. Then it reads each
record from the interim db file and builds the IFS file from that. 
xTools reads the DB files and writes each record directly to the IFS in CSV
format, just like you would have coded it. In addition, I hand-optimized a
lot of the internal RPG IV code to avoid VARYING return values from
subprocedure calls. Instead I pass two parameters by reference, one is a
buffer (return variable) the second is the return variable length. That
helped a lot over using the published/external interfaces.
Its amazing how short you can make the data path if you have the point of
view that shorter/faster is better vs "machines are so fast that code
efficiency isn't important". 

-Bob Cozzi
www.RPGxTools.com
If everything is under control, you are going too slow.
- Mario Andretti

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of RPower@xxxxxxxxxx
Sent: Monday, May 09, 2005 9:24 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Import taking over 24 hours

27 days?  27 days?
Wow... that's quite a chunk of time.  And you got it down to 7 hours? 
That's like 1% give or take a decimal or 2... Wow.

Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
Tel: 709-576-8132
Email: rpower@xxxxxxxxxx
Website: http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. - 
Sir Winston Churchill




"Bob Cozzi" <cozzi@xxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
09/05/2005 11:37 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
<dpalme@xxxxxxxxxxx>
cc
"'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
Subject
RE: Import taking over 24 hours






When I wrote the RPG xTools, I tested the Convert to Comma Separated 
Values
interface by running it over a 46 million record file. The typical item
master size is several hundred million records--the 46 million record file
is a summary file, yikes!
Anyway, using the IBM tools it took about 27 days to convert the file to 
CSV
format on a module 820 and then a faster (albeit "smaller" processor 
group)
model 800. 
Using the routine in RPG xTools, it took about 14 days to convert this 46
million record file to CSV format.
After many, many hours of tweaking the parameters, memory allocations and
procedure calls, I got it down to about 7 hours for all 46 million 
records.

The point here is that you really have to look at the code as it is 
running
and determine what changes need to be made to optimize the routines. For
example, lots of fields probably means lots of calls to routines that
convert each field. But if you change the routine to call the conversion
once and tell it how many fields to convert, you can reduce the overhead.
This is just one small piece to the larger piece.
What you haven't said is if the conversion code is written in RPG IV or
RPGIII, COBOL, or ILE C.


-Bob Cozzi
www.RPGxTools.com
If everything is under control, you are going too slow.
- Mario Andretti

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of dpalme@xxxxxxxxxxx
Sent: Monday, May 09, 2005 8:44 AM
To: RPG Group
Subject: Import taking over 24 hours

We have been working on an import situation for a couple of months now, 
and 
I would like to get some feedback from some of you on this.  We have a 
RS/6000 which is running some older cobol programs, we are not in a 
position

to replace those programs at this time, what we need to do is import that 
data (from standard unix cobol files ACUCOBOL to be exact) into several 
physcial files on our 9406-170. 

We are using MS Access (which I hate but it serves its purpose here) to 
dump

the data from the cobol files into tab delimited files which are then 
ftp'd 
over to the 400 and then imported into the physical files.

Everything does work but it is taking around 28 hours for the import 
process

to complete.

The files can be broken into three groups, customer master data - which 
takes about 20 minutes to complete....the flat file is approximately 8.5mb 

in size.  We have several line item files which are imported into one 
single

physical file, the flat files for this group are approximately 400mb.  The 

third and final file is a header file which is approx 150mb in size.

We had an outside vendor actually write the code for us which as I said 
before does work, but it is taking a very long time to process. 

If anyone has any ideas or thoughts on how we can speed this process up I 
would love to hear those ideas.  I need to check with the legal department 

before posting the code since I am not sure what contractual obligations 
we 
are under as far as the code goes.

I do know that he is splitting the fields up by checking for the tab and 
then writing the data to the file. the header file has quite a few fields 
in

it, and if anyone wants to know the number of fields I can verify that and 

re-post.
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.