RE: RPG native to SQL

["Richard Jackson" <richardjackson@xxxxxxxxxxxxxxxxxx>]

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