× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Dennis,

Unless the SummaryFile is used by another process, you don't even need to create it.

You could simple use embedded SQL and fetch the summary rows directly. Now one thing you lose is the
ability to do "position to" the same way you've always done so. But you can easily offer "subset by"
and "start at", which for 99.99% of the time meet the user's needs quite nicely.

Declare cursor C1 for
select Division, sum(Sales) from SalesFile
where division >= :startingDivsion
group by Division
order by Division

open c1

fetch c1 into :returnedData

Change the >= to an = to "subset by" instead of "start at"


With a little extra work, you can even do the "position to" if absolutely necessary.


Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of D Reaper
Sent: Sunday, June 01, 2008 2:47 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: AW: SQL/RPG

Hi Birgitta,

Thanks for the information. I thought I had a basic understanding of
SQL, it appears to be much more robust then I thought.

As a further experiment, I created a RPG read program and a RPG embedded
SQL program using FETCH. I also created a logical file on the SalesFile
with the key based on division. I ran both programs, first before the
logical was created and then with the logical created.

The RPG programs mimic the following SQL:

insert into SummaryFile
select Division, sum(Sales) from SalesFile
group by Division

The SummaryFile has two fields: division and sales, keyed on Division.

The first run of the RPG read and embedded SQL programs ran in about 9
minutes. These programs update the SummaryFile after every read/fetch on
the SalesFile.

The second run of the RPG read and embedded SQL programs using the
logical file ran in about 6.5 minutes. These programs only update the
SalesFile when the division changes.

The SQL statement run using RUNSQLSTM ran in about 9 seconds.

Our iSeries is a 520 running V5R3.

I'll review the material you suggested.

Thanks.

Dennis

BirgittaHauser wrote:

Hi,

First what you want to know is too complex to explain it in 2 minutes.

Because there are no logical files on the physical (and I assume also no
SQL
indexes), each of the 1.5 million of rows must be read (that means a
table
scan must be performed) and grouped temporary. With RPG and even
embedded
SQL you are reading row by row, move it into an array and summarize it.
SQL
itself can use a bunch of additional access methods and decides itself
how
to get the result in the fastes way. RUNSQLSTM will also use a table
scan,
but reads blocked (contrary to reading row by row) and groups the result
into temporary hashtables.

If you create a keyed logical file (or an SQL index) over division and
let
embedded SQL do the summary, you'll see the embedded SQL will be almost
as
fast as the RUNSQLSTM.

There are tons of information in the iSeries Online Library and several
redbooks:
Database Performance and Query Optimization

http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/rzajq/rz
aj
q.pdf
Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS
http://www.redbooks.ibm.com/abstracts/sg246598.html?Open
OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4
http://www.redbooks.ibm.com/abstracts/sg247326.html?Open
SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246654.html?Open

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them
and keeping them!"

--
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 e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.