RE: Another V5R2 question (Resolved-ish) -- SYSTEM21

Mike (Mlemon) ? offhand I remember it was the same
figure in Euros. We have maintenance with them, but
don?t use them for anything any more.

With regard to the performance problem, I have finally
(I hope) got to the bottom of it. The Interface we use
for our Data Warehousing pulls data from System21,
does some manipulation on it, and then FTP?s it off to
an SQL Server. 
These routines need to perform some currency
conversions, these are done by using the standard GEAC
programs GL820 & GL821. The standard versions of these
programs have no  SQL code in them, but when JBA
modified our software for Euro compliance, both of
these had SQL added. These changes could have easily
been done without SQL, but JBA?s policy at one time
was to reduce the number of additional logicals
created and SQL was commonly used.
Having said that, the SQL Code did not look as if it
would have such a dramatic effect?..

This overnight routine, on our ?test? box, before
V5R2, took 5hrs15mins to run. It is a small box, and
on our ?live? box which is still on V4R5, takes
1hr45mins. After upgrading to V5R2 the routine ran for
9 hours or so, before the machine IPL?d, and was
nowhere near finishing! 
I ran some debug over the job, but the only thing the
Query Optimiser suggested was creating an additional
logical (keyed only on CONO) which I did, but made no
difference.
I then replaced the SQL in GL820 & GL821 with
traditional RPG (creating 2 logical files in the
process). The overnight job now runs on V5R2 on our
?test? box in 2hrs30mins. 
Wow, a dramatic improvement!!!
Then I thought, ?how much of this is V5R2?s fault??.
So, I ran a test on our ?live? box (V4R5), using the
SQLless GL820 & GL821. The run time was reduced from
1hr45mins to just 45mins. 

The only conclusion I can draw from this, is that the
SQL in these 2 programs performed very poorly,
regardless of OS release. As mentioned before, V5R2
seems less tolerant of poorly written/performing SQL
as shown by the dramatic time increase.

In the big scheme of things, using these Currency
Conversion routines within the system is no real
problem, even if they do perform poorly. Generally
they are called 1 or 2 times when receiving stock, or
creating a sales invoice line, etc. The problem was
our data warehousing interface calling the program
multiple times, i.e. extract 100,000 invoice lines
means calling the routine up to 100,000 times!.
100,000 fractions of a second then become noticeable
and are a problem???..

I do worry about the other programs we have SQL
embedded in, but I have tested some of them and not
found any other problems??..YET!

Thanks for your help

Juan  


__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html