My DB2 SQL search returns zero rows, even though matching rows do exist in the db

I have a HCL Domino application that executes a Java agent which runs a SQL query on a DB2 back-end database (DB2 version 11.5.7). Sometimes, the query returns the correct number of rows, but sometimes when the query is fired, DB2 - apparently - returns ZERO rows, even though I am 100% positive that matching rows exist in the DB2 database. (The problem is intermittent; when I run my application, it works for several SQL searches, then suddenly stops working, by returning ZERO rows on all subsequent queries/searches.) Again, I am positive that matching rows exist in the DB2 database. I checked the DB2 server logs and took an exact copy of the SQL that was executed on the DB2 side. As a test, I then ran that exact same SQL query via my DB2 client on my laptop and the query works fine every time. (This proves that the problem is NOT with the SQL query syntax, since the SQL query works perfectly when run via my DB2 client.) I have updated my JDBC drivers to the version recommended by IBM for this version of DB2, but it made no difference; the intermittent problem eventually recurs. I am out of ideas now. If anyone has any ideas/suggestions/tips, I would really appreciate any help. Thanks.

My Java program runs a SQL query on a DB2 back-end database (DB2 version 11.5.7). The query returns ZERO rows, but I am expecting at least one row to be returned in the result set. (The problem is intermittent; when I run my application, it works for several SQL searches, then suddenly stops working, by returning ZERO rows on all subsequent queries/searches. (No error messages at all are generated in the DB2 log. No Java exceptions/errors occur.)

Answer

It sounds like you are facing a challenging intermittent issue with your Java application querying the DB2 database. Here are some potential areas to investigate that might help resolve the problem:

**Resource Limits**: Look for resource limits on your DB2 instance, such as connection limits or memory usage, that may affect query execution.

**SQL Execution Context**: Verify if there are any environmental differences between running your query through the application versus the DB2 client. For example, check user permissions or roles associated with the connection used by the Java application.

**Debug Logging**: Add debug logging in your application to capture query execution, parameters, and connection details to isolate when the behavior changes.

**DB2 Configuration**: Review DB2 configuration settings, such as optimization or locking options, that might impact query behavior intermittently.

If the issue persists after investigating these areas, consider enabling detailed DB2 tracing to gather more information about query execution and performance. This may provide additional insights to help pinpoint the underlying cause.