Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / October 2006

Tip: Looking for answers? Try searching our database.

db2 hangs when using snapshot monitor on linux

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
steingold@gmail.com - 25 Oct 2006 11:40 GMT
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 - 25 Oct 2006 21:05 GMT
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

> Hi all
>
[quoted text clipped - 15 lines]
> TIA
> steingold.
steingold@gmail.com - 26 Oct 2006 07:40 GMT
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 - 27 Oct 2006 02:56 GMT
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

> Hi Phil, thanks for your reply.
>
[quoted text clipped - 8 lines]
> increased the frequency of the snapshot, to make sure that this is what
> causes the database to hang.
steingold@gmail.com - 30 Oct 2006 08:42 GMT
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();
            }
        }
    }
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.