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!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von D Reaper
Gesendet: Sunday, June 01, 2008 05:58
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL/RPG
I have a very basic question concerning SQL. Can someone point me to
information on how SQL accesses data on the iSeries? What are the
underlying mechanics of the process?
Last week I was made aware of a sales related inquiry that was taking
many minutes to produce data. The purpose of the inquiry is to display
sales summarized by division (12 of them). There are about 1.5 million
records in the file, each record is 300 bytes long. There are no logical
files built on this physical file.
The program was written using embedded SQL with the fetch option. The
program does nothing else but load an array with summary data and then
display it using a subfile after all the data is read.
For testing purposes, I also wrote a version using the RPG read option.
Both of these options ran about the same time, about 9 minutes. I also
tried using the RUNSQLSTM, this ran in 9 seconds.
The time differences are amazing. How does SQL process all this data in
less then 9 seconds? I understand there are differences in how SQL and
RPG are implemented, but it seems a large difference.
Are there any books, papers, articles, that show how SQL access data on
the iSeries? How does it work with DB2, the OS, and the hardware to
deliver data?
Thanks,
Dennis
midrange.com
