My application is running on RedHat-3 Unix platform, Apache 2.2.X and Tomcat-5. It connects to Sybase 15.X database through a JDBC connection pool and runs a stored procedure.
Sometimes, the stored procedure is NOT returning any data in the middle layer, but interestingly at the same time if I run the SP with exactly same parameters from a DB Tool like DBArtisan etc, it returns the data.
Once the tomcat is restarted, it behaves normally and expected data is returned from DB.
Surprisingly there is no Java or SQL exception is seen.
I checked the CPU usage of the Unix box and the database, they looked ok.
It's really hard to tell, so I'm only guessing, but one possibility would be that there is an implicit conversion taking place somewhere in the stored procedure (most often this is a text-to-date conversion). That conversion works well when using default session settings, but when something changes the default settings (possibly a sparsely used feature of your system), the procedure all of sudden breaks. Since the connections are pooled, the altered session "poisons" the connection pool. Restart of the webserver refreshes the connection pool and resets the behavior to "normal".
As I said, I'm only guessing and also I work with tools different from you (Oracle and OAS), but in my environment this scenario would certainly be possible. I'd suggest carefully inspecting the stored procedure for implicit conversions (and for other suspicious operations too) and maybe adding some logging/tracing code into it.
Joined: Feb 01, 2012
Thanks Martin for your inputs. I just found out that the Unix host is low in RAM memory, struggling and it is juggling around the available memory, which may have caused the JVM to read some corrupted pages.
I am looking to add some more memory to it, let's see if it resolves the issue.