db2 hangs when using snapshot monitor on linux

[steingold]

Hi all

I'm using DB2 UDB v8.2 on linux.
After minimizing the code in order to isolate the problem, I have a
simple jdbc client that executes the following SQL statement in loop :
SELECT * FROM TABLE (SNAPSHOT_LOCK (CAST (NULL AS CHAR), -1)) AS
LOCK_INFO

After each query the client sleeps for 100 ms, and then continues the
loop, so the database machine is not under load (cpu consumption is
about 5-10 %).
After couple of minutes the database process takes 100 % cpu, I cannot
stop the database, and the sometimes, the OS hangs, and I need to
reboot the machine (push the reset button).

Does anybody knows this problem ? How can I solve it ?

TIA
steingold.

[Phil Sherman]

It would be easier to help you if you gave more information about the
environment. Without ALL of the code you are executing, it's guesswork
to determine what you've done.

The simplest error is that you aren't closing the statement after
retrieving the data rows. Your error is indicative of running out of DB2
agents or memory. Try some system monitoring tools (ie. "top") while
this is running and see if you can discover anything else about what is
happening.

I'd also think that checking locks every 0.1 second would generate a
awful lot of data to analyze.

Phil Sherman
st*******@gmail.com wrote:

Hi all

I'm using DB2 UDB v8.2 on linux.
After minimizing the code in order to isolate the problem, I have a
simple jdbc client that executes the following SQL statement in loop :
SELECT * FROM TABLE (SNAPSHOT_LOCK (CAST (NULL AS CHAR), -1)) AS
LOCK_INFO

After each query the client sleeps for 100 ms, and then continues the
loop, so the database machine is not under load (cpu consumption is
about 5-10 %).
After couple of minutes the database process takes 100 % cpu, I cannot
stop the database, and the sometimes, the OS hangs, and I need to
reboot the machine (push the reset button).

Does anybody knows this problem ? How can I solve it ?

TIA
steingold.

[steingold]

Hi Phil, thanks for your reply.

What kind of environmental data would be relevant ?

I do close the statement after retrieving the data rows. If it is a
memory issue, I cannot see it in "top" or in "vmstat".
Is there any db2 tool that might supply a better diagnostic ?

The origin application does not take the snapshot every 0.1 seconds,
and it still hanged. Only when I tried to isolate the problem, I
increased the frequency of the snapshot, to make sure that this is what
causes the database to hang.

[Phil Sherman]

Try putting your SQL statement into a file and using a shell script to
execute it. If you can run this without problems, you definitely have an
issue with the Java code interface.

A database snapshot will give you information about agents.

Check the APAR list from the most recent fixpack and look through it for
issues like yours. I believe that each fixpack's APAR list includes the
APARs repaired in prior fixpacks. If not, you'll need to check prior
fixpack APAR lists.

Make sure that your code does not do something like a connect for each
statement executed without corresponding disconnects.

Again, good luck

Phil Sherman

st*******@gmail.com wrote:

Hi Phil, thanks for your reply.

What kind of environmental data would be relevant ?

I do close the statement after retrieving the data rows. If it is a
memory issue, I cannot see it in "top" or in "vmstat".
Is there any db2 tool that might supply a better diagnostic ?

The origin application does not take the snapshot every 0.1 seconds,
and it still hanged. Only when I tried to isolate the problem, I
increased the frequency of the snapshot, to make sure that this is what
causes the database to hang.

[steingold]

Well, I found some APAR's about snapshots that cause the database to
crash or hang (though neither one of them is about the snapshots that I
take), so I installed FixPak 13 which should contain the fixes for all
of these known bugs, but it still happens.

I attached the client that demonstrates the problem.

public static void main(String[] args) throws InterruptedException {
Connection con = null;
Statement statement = null;
ResultSet rs = null;
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
con = DriverManager.getConnection("jdbc:db2://server:50000/dbname",
"user", "pasword");

int i = 0;
while (true) {
statement = con.createStatement();
rs = statement
.executeQuery("SELECT * FROM TABLE (SNAPSHOT_LOCK (CAST (NULL AS
CHAR), -1)) AS LOCK_INFO");
if (rs.next()) {
System.out.println(rs.getString(1));
}
Thread.sleep(100);
i++;
rs.close();
rs = null;
statement.close();
statement = null;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (con != null)
con.close();
} catch (SQLException e) {
}
try {
if (statement != null)
statement.close();
} catch (SQLException e) {
}

try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}