unicode ccsid on physical files -- JAVA400-L

Richard,

Thanks for the detailed clarifications. Although I would have loved to see a 12x improvement, the 25% we did see is very significant and welcome. The ccsid switchover was simple with an immediate payback. I am certainly glad the tip was included in the performance guide. We would never have thought to consider the cost of data conversion otherwise.

Alex Garrison

agarrison@logtech.com

----- Original Message -----

From: cujo@us.ibm.com

To: JAVA400-L@midrange.com

Sent: Thursday, November 11, 1999 1:55 PM

Subject: Re: Fw: forcing native access with websphere and toolbox

I don't have anything to do with the externalizing of performance numbers,
but it was my code change to the AS/400 Native JDBC Driver and my testing
that produced the 12x number. I created a "best case" scenario for
figuring out how large the performance improvement could be. In that test,
I query only one table with many columns (all character data) and many rows
(thousands). I ran this test on a smaller, older AS/400 in the lab and on
a very high-end system and saw very similar ratios in the performance
change.

I hope that no one suggested to you that we could increase performance of
your average appliation by anything close to a factor of 12 by making this
change. The point of the exercise on my part was simply to figure out just
how expensive it can be to have to do translation of data. I think you
would agree that a 25% improvement is significant.

Anyway, I apologize if expectations have been unrealistically set, but I do
suggest converting your database tables and columns to unicode whenever it
is feasible in your environment. I, of course, recognize that it isn't
always.

Regards,

Richard D. Dettinger
AS/400 Java Data Access Team

Think your job is safe? Your future? Your company's existence? Think
again.
Right now there are people planning to do you in. To replace you, steal
your
paycheck, obliterate your business, throw you out in the street.
...This is your wake-up call. Digital Darwinism is unstoppable.
-Paul Somerson

"Alex Garrison" <agarrison@logtech.com> on 11/11/99 10:49:23 AM

Please respond to JAVA400-L@midrange.com

To:   JAVA400-L@midrange.com
cc:
Subject: Fw: forcing native access with websphere and toolbox

Anyway, I have yet to crack open the V4R4 performance guide, but it has
chapters on each of the components involved in our system.

One of the things the performance guide recommends is to change your
physical files to use ccsid 13488 (unicode) for all character data. This
is supposed to speed things up (IBM says by up to a factor of 12) by
eliminating the conversion of ebcdic to unicode that must normally take
place once you have retrieved data.   We are doing this now to all our
files. Although we do see an improvement (approximately 25%), we dont see
nearly the improvement IBM claims.

----- Original Message -----
From: Luther Ananda Miller
To: JAVA400-L@midrange.com
Sent: Thursday, November 11, 1999 9:24 AM
Subject: Re: forcing native access with websphere and toolbox

We haven't targeted performance yet- it is coming soon. We are happy with
ODBC performance via SQL and stored procedures to the 400 from PC client
apps. So far, we are dissapointed (and surprised) with initial performance
of an applet which communicates with servlets running under WebSphere (via
the HTTP server) which in turn use JDBC access. We have not yet pinpointed
the bottleneck(s) so it is too early to say where the problem lies. I don't
doubt that record level access is faster, but it doesn't suit our
particular requirements. On the other hand, we also have some servlets
which respond with HTTP/JSP and access the database with the toolbox JDBC
drivers to execute stored procedures, and except for the first time they
are run after starting up, the performance is fine. Anyway we have a lot of
hunting to do. If we are not happy with the end result, then Rochester has
some work to do IMHO. I expect performance via JDBC to be at least as good
as with ODBC on the 400 (actually, better if I use the native DB2 drivers
directly on the box), especially if Rochester wants to market the 400 as
THE java server. Anyway, I have yet to crack open the V4R4 performance
guide, but it has chapters on each of the components involved in our
system.

Luther

----- Original Message -----
From: Alex Garrison
To: JAVA400-L@midrange.com
Sent: Thursday, 11 November 1999 14:19
Subject: Re: forcing native access with websphere and toolbox

A personal comment on jdbc:

We used the jdbc drivers from websphere on the as/400 (both toolbox and
native) for about 17 months. To make a long story short - the performance
just isnt there.

I know others have had a great experience with jdbc. We brought in
several such lucky folks as consultants to learn from their experiences.
Some techniques do help - stored procedures, analyzing sql optimizer
diagnostics, etc. Bottom line, though, is that we get much better (like
over 50%) performance using record-level i/o. I know we lose portability
by going to record-level access, but a portable slow-running servlet is
just no good to us.   We really just got to the point where we had no
choice.

I am not saying everyone should dump jdbc for record-level i/o, but I
have a feeling there are others out there with the same experience we had.
Anyone else go from jdbc to record-level i/o or are unhappy with jdbc
performance in websphere on an as/400?

    ----- Original Message -----
    From: Luther Ananda Miller
    To: JAVA400-L@midrange.com
    Sent: Thursday, November 11, 1999 4:38 AM
    Subject: Re: forcing native access with websphere and toolbox

    If you use JDBC, you can use the toolbox JDBC drivers to connect to the
AS/400 from anywhere. If you are running your servlets on the AS/400, you
can configure your servlet to use the native DB2 JDBC drivers instead of
the toolbox drivers for faster access. (Not sure how it compares to record
level access- I imagine record level performs faster). Another advtange of
JDBC is that your servlet will likely run on any java platform and connect
to any database, depending on the SQL that you use instead.

    Interesting about how you must connect as QTMHHTTP to bypass TCP/IP on
the 400 -- I did not know that.

    Luther

      ----- Original Message -----
      From: Alex Garrison
      To: JAVA400-L@midrange.com
      Sent: Wednesday, 10 November 1999 21:36
      Subject: forcing native access with websphere and toolbox

      Tip of the day: IBM Java Toolbox Record Level I/O from Servlets
    ...