RE: RPG native to SQL -- MIDRANGE-L

Richard,

ODBC does not equal SQLRPG and you sure know that. What dialect of SQL are
you talking about on the 400? Static? Dynamic with PREPAREs? CLI? Did you
pre-package SQL requests for ODBC? I'm not questioning your judgement. I'm
only saying there is a lot more options available with SQL than with native
I/O. And it is definitely easy to write poor SQL code. As far as
benchmarking is concerned, I'm basically a performance person myself and I
too know what I'm talking about. :-) Speaking of numbers, the last benchmark
I ran was for 4,000 interactive users on the 740.  I had a good chance to
play with SQL and compare SQLRPG (embedded SQL) performance with RPG native.
If you do the conversion accurately, the loss can be limited to 10-15%.

But we have definitely strayed away from my original question.

Lo

> -----Original Message-----
> From: Richard Jackson [SMTP:richardjackson@richardjackson.net]
> Sent: Thursday, August 31, 2000 11:43 PM
> To:   MIDRANGE-L@midrange.com
> Subject:      RE: RPG native to SQL
> 
> "By the way, there is nothing in the AS/400 SQL implementation that would
> make it as inefficient as you're implying."
> 
> What experience do you base that statement on?  I spent five years
> benchmarking the JDE World software applications (RPG) and then three
> years
> benchmarking the JDE OneWorld applications (SQL/ODBC).  Between 1992 and
> 1995, I owned CPU sizing for JDE.  During 1996 and 1997, I performed
> additional World software benchmarks for JDE and customers.  In 1996, I
> performed the first large scale OneWorld benchmark (24 users) on AS/400.
> I
> then performed the same benchmarks on HP/Oracle, Digital NT and Alpha, and
> IBM RS6000, AS/400 and S/390 MVS and DB2.  We reached about inquiry-only
> 1,500 users on MVS.  I have performed other large-scale benchmarks with
> World and OneWorld code as recently as late 1999 and continue to do
> performance work on both products when they run on the AS/400.
> 
> From the database point of view, JDE OneWorld (SQL/ODBC) performs about
> the
> same IO operations as World does using native IO - READ is replaced with
> SELECT and so forth.  The average CPW per JDE World (RPG) user is about
> 0.65
> (the last measurement that I personally reported to JDE management was .57
> but that was for a product release about 5 years old so I rounded up).
> The
> average CPW per OneWorld (SQL/ODBC) user ranges from 3 to 45 with a mean
> around 10.  For OneWorld (SQL/ODBC), the numbers are for database only, no
> application code was running on the server.  The 0.65 number for World RPG
> includes the RPG code and the native database code.
> 
> My statements about memory are based on the same three years of OneWorld
> testing.  Earlier this year, I spent three weeks working on a problem
> where
> 275 users did not comfortably fit into a 11 gigabyte memory pool dedicated
> to ODBC - only ODBC code ran in that pool.  Under certain abnormal
> circumstances, three to six jobs in this pool would make extremely high
> memory demands and non-database faulting reached 1,200 per second in that
> that pool.  These events lasted from 5 to 20 minutes.  Interactive (that
> is
> to say, ODBC) jobs would slow way down and batch throughput was seriously
> effected.
> 
> We might disagree but I think that I know what I am talking about.
> 
> Richard Jackson
> mailto:richardjackson@richardjackson.net
> http://www.richardjacksonltd.com
> Voice: 1 (303) 808-8058
> Fax:   1 (303) 663-4325
> 
> -----Original Message-----
> From: owner-midrange-l@midrange.com
> [mailto:owner-midrange-l@midrange.com]On Behalf Of Raikov, Lo
> Sent: Wednesday, August 30, 2000 7:11 PM
> To: 'MIDRANGE-L@midrange.com'
> Subject: RE: RPG native to SQL
> 
> 
> Richard,
> 
> performance is a separate issue. By the way, there is nothing in the
> AS/400
> SQL implementation that would make it as inefficient as you're implying.
> Of
> course, a "typical" SQL RPG is probably more resource-hungry than a
> "typical" native RPG, but that's again another story.
> 
> I know I have to reacrchitect the application. I am just looking for
> somebody who's already been through that. It seems, however, that there is
> not a lot on offer. But maybe I should have explained myself in more
> detail.
> 
> Thank you,
> 
> Lo
> 
> > -----Original Message-----
> > From:       Richard Jackson [SMTP:richardjackson@richardjackson.net]
> > Sent:       Wednesday, August 30, 2000 11:17 PM
> > To: MIDRANGE-L@midrange.com
> > Subject:    RE: RPG native to SQL
> >
> > Native IO has a different view of the tables than SQL IO.
> >
> > It is possible to replace each read with a SELECT and each CHAIN/READE
> > loop
> > with an OPEN CURSOR and FETCH loop.  You can use an IBM SQL language
> > extension to fetch and update records using relative record numbers (I
> > think
> > ...)  But SQL has a lot more overhead per statement and most single
> > statements (those not using a cursor) perform a full open and close on
> the
> > file.  Programs using a large variety of SQL statements will probably
> > occupy
> > several times more virtual storage.  Please note that the ODBC driver
> > programs used by the ERP products easily use 30 megabytes of "warm"
> > virtual
> > storage and sometimes use 100 megabytes.
> >
> > You should rearchitect applications to take advantage of the strengths
> of
> > SQL (set selection and update) and to avoid the known issues and
> "badness
> > of
> > fit".  In particular, the high cost per statement suggests that you
> should
> > execute several times fewer statements or buy hardware to offset the
> delta
> > and careful tuning or more memory will probably be required.
> >
> >
> > Richard Jackson
> > mailto:richardjackson@richardjackson.net
> > http://www.richardjacksonltd.com
> > Voice: 1 (303) 808-8058
> > Fax:   1 (303) 663-4325
> >
> > -----Original Message-----
> > From: owner-midrange-l@midrange.com
> > [mailto:owner-midrange-l@midrange.com]On Behalf Of Raikov, Lo
> > Sent: Tuesday, August 29, 2000 10:16 PM
> > To: 'MIDRANGE-L@midrange.com'
> > Subject: RPG native to SQL
> >
> >
> > Does anybody have experience converting native RPG to SQL RPG? Are there
> > any
> > known methodologies? Tools? Consultancies?
> >
> > Lo
> > +---
> > | This is the Midrange System Mailing List!
> > | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> > | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> > | To unsubscribe from this list send email to
> > MIDRANGE-L-UNSUB@midrange.com.
> > | Questions should be directed to the list owner/operator:
> > david@midrange.com
> > +---
> >
> > +---
> > | This is the Midrange System Mailing List!
> > | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> > | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> > | To unsubscribe from this list send email to
> > MIDRANGE-L-UNSUB@midrange.com.
> > | Questions should be directed to the list owner/operator:
> > david@midrange.com
> > +---
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---