RE: SQL insert -- JAVA400-L

As far as the journaling issue, when you run the SQL statement through
CA400 it is probably running with a commit level of "none" or "off" so
it is not using transactions.  If you are using transactions, then the
iseries will require that the table is journaled before you can perform
and update type statement on it.  If you're not using transactions, just
make sure autocommit is on (i.e. transactions are not being used) and it
should work OK.

As far as the performance issues, there are way too many factors to
weigh when comparing performance of a iseries UDB database vs. an Oracle
database.  It might simply depend on how powerful one piece of hardware
is vs. the other.  But that list goes on and on...  Welcome to the TPC
benchmarking wars.

Tim

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Suresh Kumar
Sent: Wednesday, March 26, 2003 6:50 AM
To: java400-l@xxxxxxxxxxxx
Subject: SQL insert


Hi all,
  First of all, couple of weeks ago, I had posed a question regarding
the slowness of AS400 data access thru JT400. Thanx for all the help, we
brought down the response time from 15-20 minutes to about 20 seconds
now. Just to summarize (might help somebody),  we implemented the
following  steps:

1. Client PC running Web Server is on the same LAN as AS400 (before the
web server was accessing AS400 data remotely). 2. Appserver with
connection pooling (this seems to have cut down the response time by
half). 3. Stored procedures: this did not improve response time time
dramatically (about 10-15%). I was expecting 50% improvement. 4. Remove
DOM structures within the Java. We are now going with Hash table
concept.(this has definitely increased the speed by atleast 50%) 5.
Preparestatements wherever necessary.

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

2. New problem: when we try to insert data into one of the AS400 table,
we get an error SQL7008 (<file> in <library> not valid for operation).
It looks like the system is expectng for us to journal the table.
Howevere, this table is not journalled even in our main AS400
application. At the same time, when this SQL statement is executed using
CA400, there is no error and record gets inserted into that table. We
don't do journalling at all on this table anywhere. Is there any
parameter that needs to be specified when the connection is first
established??

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

Thanx for all the help,
Suresh