RE: When should one use SQL/RPG?

<snip>
However, I'm not quite
sure how the 'best choice'
an index is selected by the SQL engine. I've noticed that it will create
an index of its own if it
doesn't suit the engine's purpose, even if I want the engine to use an
particular index that exists.
More or less it says in the joblog that the cost is too expensive to use
the existing one
<snip>

The SQL optimizer will start with the where clause when identifying which
index to use and then (IIRC) move to the join clause.  I have never seen it
use a DDS logical with select omit criteria.  Rearranging the sequence of
your where clause can make a dramatic difference in performance.

<snip>
Now I want to add some more to the question. What about joining files to
get one/several
record(s)? Is SQL a choice that can be considered or is RPG  still a
better choice?
<snip>

You might want to try an SQL view.  This would essentially be a select
statement joining all your files together and optionally containing
selection criteria.  It can be read in an RPG program the same as a regular
physical or logical file.  Two issues to consider however; The SQL statement
describing the view is run each time the view is initially accessed and the
view cannot be sequenced.  If you want the records read in a specific
sequence you will need a select statement over the view to order the
returned records.

HTH,

Rick