java app shuts down Informix Server

Hi all, I am getting reports from a customer saying my Java application is causing the Informix server to crash. He reports that it started causing problems when they started processing large amounts of data ( millions of rows? ). They have had to restart the Informix server several times to get the processing complete. What he does is restart the database and then run the process again. After 4 tries, it seems to complete. So my guess is after a certain amount of data, the Informix server gets unhappy. I am happy to have my java app take the blame, but nothing is giving me any clues as to why its freezing the server. Here is my app setup: I am using the JDBC driver from IBM( the latest one from like June 03 or something ). I am very suspicious of this driver. I am trying to convince customers to migrate to a better JDBC driver like DataDirect, but I need to be able to say the JDBC driver is causing the problems. Informix Server 9.3 on an HP machine ( not sure what type, but this problem happened on a Sun Solaris as well ). The app they are running is fairly simple. It opens a cursor ( SELECT * FROM Table WHERE X=Y ). That Query may result in the million+ rows, but I use a cursor so its in a loop: while(rs.next) { ... do stuff ... } rs.close() The bizarre part is the exceptions java returns points to the top of the loop ( while( rs.next() ). Not anything fancy and it seems to run through many rows before it even throws an exception. The other part it crashes is at that rs.close() statement. So its not throwing any exceptions at any logic portion of the code and the exceptions that are being thrown are no help to anyone. Here is what is returned: ava.sql.SQLException: System or internal error at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:348) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2202) at com.informix.jdbc.IfxSqli.executeClose(IfxSqli.java:1740) at com.informix.jdbc.IfxResultSet.close(IfxResultSet.java:1589) at The customer also looked at the Informix log to find this amazing error message -22 Invalid argument. My suspicions are either the JDBC driver is not designed for this type of load ( it is free from IBM, what do I expect? ) or the database is not tuned for that load, or perhaps there are some leaks in my app that keep certain resources open. I am no DBA so I can't really track down the database issues. I know Informix deals with this type of load factor all the time so I am not thinking its the DB server at all. I would just like some clues to help figure out what the problem is. Can anyone point me in a direction to help pinpoint the problem? Perhaps anyone with horror stories using this JDBC driver? Thanks to everyone. Also can someone explain something to me? If I open a cursor that may contain a million+ rows, how does Informix handle that? What I mean is the cursor on in my app retrieves one row at a time, but does Informix have to load them all into memory first or what? I am fasinated by how databases do their magic and wonder if perhaps I should take the DB server into consideration when creating a cursor of that size ( everything IS indexed btw ). Mel Haynes Jr

Mel Haynes Jr wrote: > Hi all, > > I am getting reports from a customer saying my Java application is causing > the Informix server to crash. He reports that it started causing problems > when they started processing large amounts of data ( millions of rows? ). > They have had to restart the Informix server several times to get the > processing complete. What he does is restart the database and then run the > process again. After 4 tries, it seems to complete. So my guess is after a > certain amount of data, the Informix server gets unhappy. I am happy to > have my java app take the blame, but nothing is giving me any clues as to > why its freezing the server. Here is my app setup: > > I am using the JDBC driver from IBM( the latest one from like June 03 or > something ). I am very suspicious of this driver. I am trying to convince > customers to migrate to a better JDBC driver like DataDirect, but I need > to be able to say the JDBC driver is causing the problems. > > Informix Server 9.3 on an HP machine ( not sure what type, but this > problem happened on a Sun Solaris as well ). > > The app they are running is fairly simple. It opens a cursor ( SELECT * > FROM Table WHERE X=Y ). That Query may result in the million+ rows, but I > use a cursor so its in a loop: > while(rs.next) { > ... do stuff ... > } > > rs.close() > > The bizarre part is the exceptions java returns points to the top of the > loop ( while( rs.next() ). Not anything fancy and it seems to run through > many rows before it even throws an exception. The other part it crashes is > at that rs.close() statement. So its not throwing any exceptions at any > logic portion of the code and the exceptions that are being thrown are no > help to anyone. Here is what is returned: > > ava.sql.SQLException: System or internal error > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:348) > at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2202) > at com.informix.jdbc.IfxSqli.executeClose(IfxSqli.java:1740) > at com.informix.jdbc.IfxResultSet.close(IfxResultSet.java:1589) > at > > The customer also looked at the Informix log to find this amazing error > message > -22 Invalid argument. > > My suspicions are either the JDBC driver is not designed for this type of > load ( it is free from IBM, what do I expect? ) or the database is not > tuned for that load, or perhaps there are some leaks in my app that keep > certain resources open. I am no DBA so I can't really track down the > database issues. > > I know Informix deals with this type of load factor all the time so I am > not thinking its the DB server at all. I would just like some clues to > help figure out what the problem is. Can anyone point me in a direction to > help pinpoint the problem? Perhaps anyone with horror stories using this > JDBC driver? Thanks to everyone. > > Also can someone explain something to me? If I open a cursor that may > contain a million+ rows, how does Informix handle that? What I mean is the > cursor on in my app retrieves one row at a time, but does Informix have to > load them all into memory first or what? I am fasinated by how databases > do their magic and wonder if perhaps I should take the DB server into > consideration when creating a cursor of that size ( everything IS indexed > btw ). Why are you reading >1M rows? I hope it's not for updating or deleting? Error 22 (on my Linux box, anyway) says: "-22 Invalid argument. An operating-system error code with the meaning shown was unexpectedly returned to the database server. Look for other operating-system error messages that might give more information. If the error recurs, please refer to the Appendix entitled "Trapping Errors" in your Administrator's Guide to acquire additional diagnostics. Contact Informix Technical Support with the diagnostic information." Are there any signs of any other errors in the OS logs? Anyway, Informix will read rows into a buffer pool as needed and pass them over to the application as needed. I'm not aware of any specific issues with chucking lots of data through the JDBC driver, as one of my clients does a similar thing and they've not had any specific problems like yours. (Plenty of other ones, but none involving the JDBC driver. :o) I wonder if you could send us the schema and the actual SQL statement you are using. Probably the actual code fragment for the loop as well. And also please tell me that you're not doing updates or deletes. :o) -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche

First off I am not doing any deletes, but I am doing an update ( in a separate batch of statements ) I think I may have an answer. I got an e-mail from the same customer and he reported that someone in their IT department said there was a problem with one of the devices on the server. That's all fine and dandy, but they have reported this problem before so I am not sure to relax and blame hardware( wouldn't that be nice ) or peruse the issue more. For now I am going to leave it alone. Just for the record, I write pricing solutions for grocery and retail. The million+ records is all the prices for all the items in all the stores a company may have now and in the future. Expired records are taken out of the table by some other process, but the main reason for reading millions of rows is this: For any given day, all the items( items meaning your typical grocery items ) can be priced based on sales, holidays, etc. What I have to do every day is go through any unsent prices, see if they are valid for a the given day ( we do things in the future so that they have time to print tags, etc ) and generate a output record for the items. So basically I am just reading the rather large table. I am not doing anything to it in the cursor, but I do have to have an indication of that row being sent to the store so I have to update a process date column. I do an update using an indexed column. I don't know how else to handle the situation. I do use batch updates to speed it up, but other than that I am afraid I have to update the row. If you are still interested in some more detail I can provide a glimpse into my chaotic process. "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message news:brt19u$75tpp$1@ID-64669.news.uni-berlin.de... > Mel Haynes Jr wrote: > > > Hi all, > > > > I am getting reports from a customer saying my Java application is causing > > the Informix server to crash. He reports that it started causing problems > > when they started processing large amounts of data ( millions of rows? ). > > They have had to restart the Informix server several times to get the > > processing complete. What he does is restart the database and then run the > > process again. After 4 tries, it seems to complete. So my guess is after a > > certain amount of data, the Informix server gets unhappy. I am happy to > > have my java app take the blame, but nothing is giving me any clues as to > > why its freezing the server. Here is my app setup: > > > > I am using the JDBC driver from IBM( the latest one from like June 03 or > > something ). I am very suspicious of this driver. I am trying to convince > > customers to migrate to a better JDBC driver like DataDirect, but I need > > to be able to say the JDBC driver is causing the problems. > > > > Informix Server 9.3 on an HP machine ( not sure what type, but this > > problem happened on a Sun Solaris as well ). > > > > The app they are running is fairly simple. It opens a cursor ( SELECT * > > FROM Table WHERE X=Y ). That Query may result in the million+ rows, but I > > use a cursor so its in a loop: > > while(rs.next) { > > ... do stuff ... > > } > > > > rs.close() > > > > The bizarre part is the exceptions java returns points to the top of the > > loop ( while( rs.next() ). Not anything fancy and it seems to run through > > many rows before it even throws an exception. The other part it crashes is > > at that rs.close() statement. So its not throwing any exceptions at any > > logic portion of the code and the exceptions that are being thrown are no > > help to anyone. Here is what is returned: > > > > ava.sql.SQLException: System or internal error > > at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:348) > > at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2202) > > at com.informix.jdbc.IfxSqli.executeClose(IfxSqli.java:1740) > > at com.informix.jdbc.IfxResultSet.close(IfxResultSet.java:1589) > > at > > > > The customer also looked at the Informix log to find this amazing error > > message > > -22 Invalid argument. > > > > My suspicions are either the JDBC driver is not designed for this type of > > load ( it is free from IBM, what do I expect? ) or the database is not > > tuned for that load, or perhaps there are some leaks in my app that keep > > certain resources open. I am no DBA so I can't really track down the > > database issues. > > > > I know Informix deals with this type of load factor all the time so I am > > not thinking its the DB server at all. I would just like some clues to > > help figure out what the problem is. Can anyone point me in a direction to > > help pinpoint the problem? Perhaps anyone with horror stories using this > > JDBC driver? Thanks to everyone. > > > > Also can someone explain something to me? If I open a cursor that may > > contain a million+ rows, how does Informix handle that? What I mean is the > > cursor on in my app retrieves one row at a time, but does Informix have to > > load them all into memory first or what? I am fasinated by how databases > > do their magic and wonder if perhaps I should take the DB server into > > consideration when creating a cursor of that size ( everything IS indexed > > btw ). > > Why are you reading >1M rows? I hope it's not for updating or deleting? > > Error 22 (on my Linux box, anyway) says: > "-22 Invalid argument. > > An operating-system error code with the meaning shown was unexpectedly > returned to the database server. Look for other operating-system error > messages that might give more information. If the error recurs, please > refer to the Appendix entitled "Trapping Errors" in your Administrator's > Guide to acquire additional diagnostics. Contact Informix Technical Support > with the diagnostic information." > > Are there any signs of any other errors in the OS logs? > > Anyway, Informix will read rows into a buffer pool as needed and pass them > over to the application as needed. I'm not aware of any specific issues > with chucking lots of data through the JDBC driver, as one of my clients > does a similar thing and they've not had any specific problems like yours. > (Plenty of other ones, but none involving the JDBC driver. :o) > > I wonder if you could send us the schema and the actual SQL statement you > are using. Probably the actual code fragment for the loop as well. > > And also please tell me that you're not doing updates or deletes. :o) > > -- > "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" > - Coluche

"Mel Haynes Jr" <mhaynesjr@hotmail.com> writes: > I am getting reports from a customer saying my Java application is > causing the Informix server to crash. Apps that kill the IDS engine are usually not the fault of the app (unless the app dies in a critical section and corrupts shared memory). I've personally written apps that tickled engine bugs, though. These were the worst for me (and were fixed by upgrading the client to 9.40): Bug:113369 AF: MEMORY BLOCK HEADER CORRUPTION DETECTED IN ADD_TO_LIST *This bug is fixed in Version 7.3x (& later) and Version 9.21 (& later, include 9.30). Bug: 130051 A BLOCK W/ CORRUPT BLOCK HEADER IS REMOVED FROM BLOCK LIST BUT NOT FREELIST. WHEN ALLOCATING FROM FREELIST, BLOCK NOT ON BLOCK LIST -> CRASH *This bug fixed in 7.31.UD3 and later. (and 9.30.UC1 & later) -- Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412 Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142 President, CEO Fax: (760) 499-9152 My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

On Thu, 18 Dec 2003 14:46:53 -0500, Mel Haynes Jr wrote: > Hi all, > > I am getting reports from a customer saying my Java application is causing the <SNIP> > The customer also looked at the Informix log to find this amazing error > message > -22 Invalid argument. > <SNIP> Cannot comment on the rest, however, the error -22 is a red herring. Informix translates system errno's that it receives in to informix-like errors by negating them. So the error -22 is errno 22 which is "Invalid argument to a system call". Informix usually gets this when a pipe or semaphore is destroyed during a crash or the shutdown of a hung instance because not all of the virtual processors are responding normally and some do not get the shutdown message and later fail with errno 22 when they try to access resources through a system call that have been destroyed as part of the shutdown. So this is a result of the problem not a cause. Art S. Kagel