Use sql procedures for database coding instead of RPG was: SQL Update in RPGLE.

sorry! Should have been a new subject.


On 3/31/06, Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx> wrote:
> > From: DeLong, Eric
> >
> > The HLL advantage comes from being able to rapidly get access to a record
> > in
> > a table.  The real issue here is "what is a transaction".  If the
> > transaction involves a great deal of file IO, then SQL will probably
> > outperform native access.
>
> This is only true if the I/O can be encapsulated into sets of like actions.
> The problem is that this is rarely the case.  The example that's often used
> is "adding 5% to list prices".  Well, how often do you actually add 5% to
> all the prices in a catalog?  The answer is "never".
>
> Now if you have a small file of item number and price increments, it will
> almost certainly be faster to do an UPDATE statement in SQL that applies
> that increment to all the items.  However even this is not a perfect
> example, you have to take into account the time it took to write the records
> to that little file in the first place.
>
> Generally speaking, it's hard to find cases in OLTP where the number of
> records in the set justifies using SQL.  Typically the only place in an
> interactive environment where SQL makes sense is in queries.
>
> In the end, though, it's all in the application design.  I have long had a
> private belief that a properly designed database might make an SQL MRP
> generation fly; not only that, it would be able to take care of DRP
> requirements at the same time.  But I've yet to design such a database nor
> seen anyone else do so.  Until such time as we manage this sort of
> application design, SQL is still best used for queries.

I am fairly convinced the opposite is true. Sql procedure code is much
more a fully functional, general purpose language than RPG programmers
are aware.  RPG should be used only for what sql procedures cant do -
green screen and API programming.

a few reasons to code sql procedures instead of rpg:
- on the whole, the programmer is more productive in sql procedures
- it is easier to read and comprehend an sql procedure compared to an
rpg program.
- the pool of sql procedure programmers is increasing, the pool of rpg
programmers is decreasing.
- at my last account the as400 programmer who was most productive was
a relatively recent hire with an oracle background. He coded all sql
procedures and CL programs.
- we dont know what the future is for the system i5.  In the not
unlikely event IBM shuts us down and shops have to migrate to the p5,
moving sql procedure code could be as easy as whatever you call a
restore in the unix world.  RPG will require a complete rewrite.

-Steve