Re: AW: SQL/RPG

[D Reaper <dreaper@xxxxxxxxxxx>]

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/rzaj
> 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!"