RE: RPG native to SQL -- MIDRANGE-L

Right!  Sorry, I was thinking about client-server SQL, not static SQLRPG.  I
was off the track you were on.

Embedded static SQL with stable SQL packages is a completely different
animal than dynamic SQL via ODBC.  In the case I was referring to, the SQL
was generated on client machines, transported by Client Access Express and
TCP/IP, and was always dynamic.  When viewed with database monitor, a fairly
large percentage of statements are reoptimized - I don't have any numbers to
quote, they vary from site to site and application to application but
something like 10 to 20 percent.  Users are permitted to provide information
that can modify where and order by clauses.  The queries use host variables
but statements arriving from clients are generated and dynamic.  Before
V4R3, some  customers overflowed SQL packages more than once per day.  At
V4R3, very few customers can overflow a package in one day but many can in
two.

If you translate a locally-run application from native database access to
SQL (and you don't do anything foolish), I will accept your 15 percent or
less - I don't have enough experience to argue :)  If you take that same
application and put it onto another machine then run it over DRDA, I think
the numbers will come out higher.  I have more experience with this - my MVS
test used a 3033 driving SQL via DRDA to a CMOS 10-way.  If you convert your
application to dynamic SQL (as in my case) and throw in some extra
functionality, I think that you will get the numbers that I got.  I'm not
sure what the next step is but it scares me just to think about it.

By the way, dynamic CLI seems to be pretty good even if you create a new SQL
package every time.

>>And it is definitely easy to write poor SQL code.

I might observe that the range of normal cost for native IO operations is
narrower than it is for SQL.  In other words, it is possible to write a
single SQL statement that can take hours to execute.  I cannot think of a
native IO statement that can take that long and I think that, in most cases,
I could replace the hours-long SQL statement with a handwritten native IO.
I'm not trying to say that this is a good idea only that it is easy to
casually write a big ugly SQL statement but it is much harder to casually
write something that ugly using native IO.  Since we don't normally write
code like that, I say that SQL has a wider normal range than native.

> "By the way, there is nothing in the AS/400 SQL implementation that would
> make it as inefficient as you're implying."

There is dynamic and ODBC and they are in the AS/400 SQL implementation.
That is what got me excited - "real" client-server (read with tongue in
cheek).  I stopped thinking just long enough to miss the "SQLRPG" in the
next sentence.  If I had paid attention to that hint, I might have been
thinking about static prepared SQL AND ODBC (read with rueful intonation).

On my down days, I think that IBM created SQL on the 400 to work just like
SQL on the mainframe - static-bound - and allowed dynamic SQL just so that
they could "check the box" when a customer RFP asked if the AS/400 can do
dynamic SQL like Oracle and SQL Server.  On other days, I have a better
attitude.

Lately, I have spent more time on batch than on AS/400 interactive.  The
last big benchmark I did in Rochester was sort of mixed with some client
code firing in dynamic SQL but also talking to some code running on the
host.  The host code was running SQL via CLI.  We were running on big boxes
but the apps weren't quite done yet and some of the bugs were important so
the results weren't all we could hope for.

Driving those large-user-count interactive tests is tough.  How did you do
it?


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: Thursday, August 31, 2000 7:31 PM
To: 'MIDRANGE-L@midrange.com'
Subject: RE: RPG native to SQL


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

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