× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: RE: RPG native to SQL
  • From: "Raikov, Lo" <RaikovL@xxxxxxxxxx>
  • Date: Fri, 1 Sep 2000 11:30:32 +1000

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
+---

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.