Thanks, Richard, for the correction on indexes - I tried one and was able
to read it in RPG. There is still nothing exactly like logical files when
they combine both view and index, with a subset of columns and key fields.
You MAY, if the optimizer chooses, get the right index used for the
selection and ordering columns.
On the first point, yes, there are many more things available. Still, I see
little gain in converting existing applications to SQL, as I said, "just to
go to SQL" for its own sake. Performance will probably suffer (BTW, I work
for a company whose business is SQL performance analytical products). Views
and "with xxxx as select..." are often deadly where performance is
concerned - they often result in complete copies to temporary tables, e.g.
If you need the additional function, of course, use SQL. But even applying
referential integrity and triggers to existing code is not being done very
much - it's a daunting task. You know the scope of this, I'm sure -
removing code in apps that does all the work that RI and cascades and
triggers would do, changing all the messages that you need to respond
to--no simple task.
Much of the additional function you list is done in C-specs - it's easy to
do exception join processing, just read primary by key, then SETLL EQ to
test existence of matching record or not and take only those that have no
match. Time to process may very well be less, SQL engine has to do
basically the same thing (unless it's smart enough to flip bits in a
bitmap). UDFs are easily created in service programs - you could even use
the same ones, perhaps, for COBOL or RPG.
I think it can be said that SQL is still not as record-oriented as native
I/O - it was always a block-oriented system. Esp. on the 400, but less so,
now, with actual flow constructs in stored procedures, etc.
Overloading SQL with lots of these other things can (note, "can") make for
maintenance headaches, I think. And, again, performance is a major area
where problems can arise.
We use SQL all the time, it often makes for easy first-time development.
But it can be excruciatingly slow, and there's no way to get indexes to
support temporary result sets for optimization, e.g. But native I/O is
often the better choice for implementation. And building intermediate
result tables will often have better performance.
The issues are in 2 arenas, one in the DDL (definition) and the other in
the DML (manipulation) arenas. There are things that both SQL and DDS give
us vis-a-vis functionality. If we need, say, multiple format logicals, we
use DDS. Can we do the same thing in SQL? Yes, but it's not as easy, IMO.
As far as processing the data is concerned, a fetch is a read is a fetch is
a read..., a delete is a delete current of cursor.
Again, I don't see great advantage or return on investment in converting
existing apps to SQL. New stuff, you pays your money and you makes your
choice. Designing SQL for good performance is harder, IMO, then with DDS,
and bad design in either case makes for problems. SQL makes it so blasted
easy to just write a statement with multiple joins, etc., that can bring
the fastest system to its knees, that we need to be very disciplined to put
in the effort to make it run well.
Oy, that's a lot of words this early in the day.
Regardez vous (hope that's right <g>)
Vern
At 11:37 AM 1/8/2003 +0100, you wrote:
Vern wrote:
> I think there is little to be gained by going to SQL, just to go to SQL.
> DDS has all the functionality (and more).
I firmly disagree with the last sentence: there are MANY MANY DB
possibilities unachievable thru DDS. Just to name a few:
in tables:
User Defined Types, BLOBs, CLOBs, DataLinks (no native IO in RPG/COBOL)
in views:
exception joins, right joins, calculations, use of functions (scalar,
column, User Defined), group by, ...etc
> RPG can use SQL tables and views, but not indexes, IIRC, for native
> IO. This precludes the benefits of keyed processing in RPG. (I could be
off
> here.)
Yes, you are: a non-EVI index can be directly processed in RPG !
Richard THEIS
iSeries Education, France
midrange.com
