Re: ole db/400 performance large file

So far in this discussion I think my best bet is going to be ftp (which has fewer layers to push this thru) or possible stored procedure.
We don't have any choices in the received file & it's format. It's coming from another entity, not related, and basically "this is what you get".
jim

----- Original Message ----- From: "Evan Harris" <spanner@xxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Wednesday, January 13, 2010 9:30 PM
Subject: RE: ole db/400 performance large file

Transferring of a large number of records is always a hassle, particular
when you start going across machine boundaries and across networks. I guess
ASCII->EBCDIC can't help either.

Seems to me that a better option would be to revisit the logic of
accumulating the records for a bulk transfer.

Possible options:

- Write directly to the iseries instead of an intermediate database and run
in real time
- Use a data queue to add the records and run in real time
- Use a Web Service to add the records and run in real time
- Re-engineer the database to send each record separately using some kind of
add trigger
- Batch the records up somehow (Ugh)

God luck optimizing this. Personally I don't see it as a performance issue,
it's a design issue. The reason it is slow is because you are moving 500,000
records though a heck of a lot of layers. If the developer foresaw or
planned for the number of records involved the transfer process would have
been tested *before* hitting production. An all too common scenario.

Regards
Evan Harris

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Franz
Sent: Thursday, 14 January 2010 11:29 a.m.
To: MIDRANGE-L@xxxxxxxxxxxx
Subject: ole db/400 performance large file

I've search the archives for this & not found a good answer.
One of the network programmers is using the iSeries Access V5R4
OLE DB connection to move 500,000 records from a network server
to the System i (v5r4). The native file has no logicals, and no key.
It is taking many minutes to load the first 10,000 and hours to load
500,000.
Local lan connection. Cannot see why so slow.
File not in use on either end. Not a huge record layout.
How can we speed this up. It needs to run on a regular basis (weekly or even
daily).
We have considered ftp, but that server is off on the production server.
Same OLE connection used for many applications, but those are generally
random access, and no problems. Only the large file is a problem.
Verified the network side is accumulating the records very fast.
Jim Franz
--

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.