DB2 on AS/400 performance issue (JDBC)

["Vel, Periasamy" <Periasamy.Vel@xxxxxxxxxxxxxxxx>]

We have a J2EE application on WebSphere (running on IBM Aix). The
application accesses DB2 on iSeries (V5R3) via JDBC using IBM toolbox
JDBC driver.

We notice strange things such as when a SQL error happens (e.g. wrong
sql statement, foreign key violation, not able to find a procedure, a
data conversion error) it also causes an impact on the DB server
performance (lot of resources used). As a result - the subsequence
queries take a long time to return and the application performance goes
really bad. Here's what we observed. If there is a faulty SQL statement
being executed, the server seems to be not only throwing an error
(expected), it tries to allocate more resources to that job and by the
result other things are waiting. That job remains in the RUN state and
uses a lot of resources.

For example, we have seen this after foreign key violation errors and
data conversion errors (invalid numbers). I consider them as just errors
- the database server should just fail the statements and that's it. I
don't understand why it keeps those things running. 

Here are some errors from the job log.

Message: Conversion Error on input host variable or parameter *N.

Cause: Host varibale or parameter *N or entry 2 in an SQLDA contains a
value that can not be converted to the attributes required by the
statement. Error type 6 occured. Error types and their meanings are: etc



Message: Field HVR00002 and value 2 not compatible. Reason 7

Cause: The attributes of varibale field HVR0002 in query record format
FORMAT0001 are not compatible with the attributes of value number 2. The
value is *N. The reason code is 7.

The problem is these AS400 errors don't tell anything about which
table/column the error is reported on. Any pointers on finding more
information on this would be appreciated. 

 

Has anyone experienced something like this?
Any pointers would be helpful.
Thanks

 

 



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed.  If you have received this email in error please notify
the sender by email, delete and destroy this message and its 
attachments.

**********************************************************************