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