RE: AW: SQL/RPG

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.