RE: SQL insert

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Suresh Kumar
>
>  Couple of observations:
> 1. Even with all these steps, response time of 20 seconds is not
> acceptable. We are continuing to improve the code. We do have
> about 20 SQL statements to get data from various tables (all
> tables are small) and then the HTML is created and sent to
> client. Is 20 seconds response acceptable??

The AS/400 database, DB2/UDB, is not a purely relational database.  While it
supports a very robust SQL interface, it also has a faster underlying access
method.  And since JDBC is basicallly a Java wrapper around SQL CLI access,
JDBC is typically not the fastest access method.  There are two other
methods to access data that tend to be faster.

One is the Record-Level Access methods of the toolbox - look for KeyedFile
and its various methods.  These allows you to directly access individual
records in a table (file) very quickly.

Second is to write a small RPG program to return the data.  This program can
be called repeatedly to return the records in a data structure, which can
then be parsed in the Java program.  This technique is very fast as well.


> 3. The same setup with Oracle as back end seems to run much
> faster (within 5 seconds). Is this generally true??

No, nothing is "generally" true about Oracle and DB2/400.  Oracle is
designed around SQL, and it typically requires a large amount of tuning.  It
may be that your particular Oracle database is well tuned for the queries
you are making.  But in any event, this is as fast as Oracle is going to
get - you have no other options.  On the other hand, you can dramatically
improve your DB2/400 access by using native I/O techniques - techniques
which are not available to the Oracle programmer.  It means that you will
have to understand how the AS/400 database works, and it will require extra
programming, but in the end, you get what you pay for.  If you want one
piece of code that runs everywhere, chances are it's not going to run
particularly where ANYWHERE.

Joe