RE: Database to Excel -- MIDRANGE-L

On Wed, 2008-11-19 at 10:09 -0600, Dan Kimmel wrote:

Make sure your RPG to Java call is releasing the objects. It sounds like
there's a lot of memory being eaten up. Best to reuse objects if you can
rather than creating a "new" each time. Another trick is to have the jvm
start up with more memory. The jvm starts up automatically when you make
the first call from RPG. By default, the jvm starts up allocating a
fairly small amount of memory. If you leave lots of objects laying
around, the jvm has to expand its heap. That can be very slow,
particularly on i. On a really big spreadsheet, I'll bet that expansion
happens many times. Creating lots of objects, even if you're releasing
them (eliminate all references),

If I remember correctly, the jvm knows nothing about references in an
rpg program, so it has to assume the worst. So "releasing them" means
releasing them explicity. This is hard to do because so many operations
will create an object implicity.

will cause the garbage collector to run
often if there's little memory allocated. I read somewhere that
references are retained to program end for all objects created in RPG by
calling the constructor. Because the reference is retained, they are
retained in the heap and cannot be garbage collected.

Well, at the cost of some extra coding, it is possible (and often a good
idea) to release a whole lot (including ones created implicitly)
occasionally during execution. Take a look at
<http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/books_web/c0925075175.htm>.
Short version: you impose a stack discipline on references to java
objects.

BTW, my thanks to Jon Paris for his presentation "Brewing Up Mixed Java
and RPG Applications" at TUG TEC 2006. <plug> The next TEC is coming
March 24 through 26, 2009. Watch <http://www.tug.ca> for details.
</plug>

Cheers,
Terry.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Scott Klement
Sent: Tuesday, November 18, 2008 4:42 PM
To: Midrange Systems Technical Discussion
Subject: Re: Database to Excel

Hi Sharon,

POI/HSSF has proven to struggle when spreadsheets get larger and larger.

(I don't know why that is.) In particular, adding more columns seems
to make things disproportionately slower.

The problem isn't because POI is written in Java. There's an inefficient
algorithm in it somewhere that's causing it to be slower. (Though, it'd
be perhaps twice as fast if it were pure RPG, since pure RPG executes
faster... but that's still not good. Excel could write your document
to disk in seconds, it should take 45 minutes *or* 23 minutes!)

One option you might consider, instead of generating standard XLS
documents, is to use the new format that's based on XML. The newer
versions of Excel use XML as their native format. Outputting XML can be
done in pure RPG and can be done efficiently. Of course, it requires you
to use a recent version of Excel, and may be more complex to code
(POI/HSSF does a lot of things for you that you might have to write
extra code to output in HSSF) but... it's something to look into, at
least.

Wintermute, Sharon wrote:

I have a question. I recently used Scott Klement's Excel articles to
create an Excel processor. It uses a control file that describes

what

file, what Excel worksheet to use as a base, then adds the data to a

new

copy of the worksheet. Once this is done, another job actually sends
the file from the IFS to the user. I had to separate the creation and

the sending since SNDDST only uses QDLS and QDLS cannot be used in a
job where Java is involved (multi-threading).

The process itself works great. It runs every 10 minutes looking for
new requests to process. Now we have discovered that some of these
requests are huge files. Over 75K rows to be created. This one
request ran for over 45 minutes on a usually lightning box.

This lpar has over 8 gig memory and its on a M525-7792 so its usually
a speed demon.

I really need to decrease the time significantly. Any ideas? Is
there another way to do this, perhaps without Java?

Sharon Wintermute

--
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.

--
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.