|
Peri, Not sure if this is your live or test environment, but you should be able to set trace=true on the toolbox JDBC driver, this should show you which job and which SQL statement is getting the error on the database access, As it dumps out loads of info about the db connection to system.out cheers Colin.W http://as400blog.blogspot.com Extension 5800 Direct dial 0870 429 5800 -----Original Message----- From: Vel, Periasamy [mailto:Periasamy.Vel@xxxxxxxxxxxxxxxx] Sent: 03 March 2005 00:21 To: midrange-l@xxxxxxxxxxxx Subject: DB2 on AS/400 performance issue (JDBC) 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. ********************************************************************** -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. This e-mail has been sent by a company of Bertram Group Ltd, whose registered office is 1 Broadland Business Park, Norwich, NR7 0WF. This message, and any attachments, are intended solely for the addressee and may contain privileged or confidential information. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited and may be unlawful. If you believe that you have received this email in error, please contact the sender immediately. Opinions, conclusions and statements of intent in this e-mail are those of the sender and will not bind a Bertram Group Ltd company unless confirmed in writing by a director independently of this message. Although we have taken steps to ensure that this email and any attachments are free from any virus, we advise that in keeping with good computing practice the recipient should ensure they are actually virus free.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
