SQL and performance

[P.Goovaerts@xxxxxxxxxx]

First, excuse if this mail is in the wrong mailing-list but there's no SQL 
specific...

We have had some problems because of 'lack of CPW' (CFINT01-job taking 75% 
of our system).  Checking out what jobs caused this pointed us to RPG 
programs using SQL.
Giving the fact that we have files with about 1 million records and we 
want to enhance our RPG with SQL:

1) i.e. a file with 250000 records which is accessed by 'select * from 
where'
What exactly happens at system level?
- are all records read when SQL is executed?
I alway's thought that 
- a 'performant' index (lf) was searched
- if necessary, a new index is created automaticallyindien nodig deze 
index eventueel gecreëerd wordt
- ALL records are read

One of our test shows that only about 20000 records are read...

2) join-union
Any tips for performance welcome!

3) What is the impact when creating tables inside our RPG/SQL program?
example:
- create tableX
- select records using table1 / table2/,... /tableN 
- put result in tableX
- select records from tableX

I alway's thought that I/O is the prime attacking job on the system but, 
if IBM say's it's going to change RPG to use SQL in stead of native I/O 
(read, reade, readp), it seems that this will have huge impact on CPW 
too...

Finally, I know... getting more CPW solves this issue too but we first 
want to be sure that our developing is ok!