0

I am having a weird issue with in my program hitting a specific DB, I will try to explain it the best I can.

We have a web app that connects to a DB using WebSphere data pooling. We use that connection to connect to other DBs that are in the same DB Server. Everything is working fine at this point. I created a servlet that creates a report, so it has to run several queries to various DBs. It runs quick and fast but when it gets to the 4th DB it stalls and the connection hangs. I got into the DB servers Activity Monitor and there I see that it has created several threads some running and some suspended. When I kill one of those my program comes back and errors out for that iteration and continues. It continues to do this while it is hitting that specific DB, once it is done with it and moves on it does the next 3 DB with no problem.

I have looked at the properties of this DB and compared it to others but I am not seeing anything different. Can someone please point me in the right direction to determine the issue with this DB?

UPDATE - Here is a screenshot of the activity monitor:enter image description here

MJSalinas
  • 139
  • 1
  • 9
  • what does it do on this db? – Leos Literak Mar 13 '14 at 14:39
  • On the DB that gets hung? It just sits there, the Activity Monitor shows several processes some Running and some suspended. I will post a screen shot of the Activity Monitor. – MJSalinas Mar 13 '14 at 14:45
  • What do you run on this db from your servlet? – Leos Literak Mar 13 '14 at 14:47
  • It is a simple select statement with some joins. To use other DBs from the one I am connecting to I append the sql statements with the "dbname..tableName". Other than that I use the standard Java stuff to get a connection from a jdbc DataSource and then use PreparedStatement and ResultSet. – MJSalinas Mar 13 '14 at 14:51
  • Do you mean that your query utilizes resources from other databases? Could there be lock issue? – Leos Literak Mar 13 '14 at 15:00
  • It does sound like a lock, I don't know how to determine that. Whats interesting is that when I hit that DB in the program it works fine. I've been having this issue since last week. How do determine if it is in fact a lock? – MJSalinas Mar 13 '14 at 15:07
  • http://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table http://stackoverflow.com/questions/999181/how-to-find-who-locks-who-in-sql-server-2005 – Leos Literak Mar 13 '14 at 15:30
  • I tried it and checked for locks, and there were no locks. :( – MJSalinas Mar 13 '14 at 15:36

2 Answers2

0

check for the connections closing you might be executing so many queries together that will be affecting server's health,or try to run those queries in work bench so that you can see whether they taking too much time to execute.

Divya
  • 1,469
  • 1
  • 13
  • 25
  • Yes I tried executing the SQLs on the work bench and they run almost instantaneous. I also thought about the connections messing up because of the servlet, I checked it and everything is fine. It runs like 30 sql's no problem, hangs on the 4 for that specific DB, then runs like 15 with no problem after those. – MJSalinas Mar 13 '14 at 15:02
  • Show the java code? I think the issue is with in that DB in some setting or something like that. The java code that I use is the standard stuff to connect and make calls to a DB the only thing that I am doing "different" is that I connect to other DBs using: SELECT A.ID, B.NAME FROM TABLEA A JOIN OTHERDB..TABLEB ON A.ID = B.ID – MJSalinas Mar 13 '14 at 15:16
  • you might be using same object of (connection/statement) for executing your queries. – Divya Mar 13 '14 at 15:19
  • Ohh ok, I understand what you're saying now. But no, I have a DAO class that has the methods for each action specifically. With in each method the connection is "open" and then a object for the statement is created with in a try catch statement. It also has a finally statement that closes the connection. In the report class it runs a loop that calls this function for every iteration. Thus creating and destroying any objects used (and closing the connection after each use too) – MJSalinas Mar 13 '14 at 15:23
  • 1
    @MJSalinas by your current description, seems like your connection is a field in your DAO class. – Luiggi Mendoza Mar 13 '14 at 16:19
  • yeah might be your connection establishment might be getting affected check for the values of connection and also a step by step process .also check for the stack-trace if you getting fault with DB you might be getting some stacktrace – Divya Mar 13 '14 at 16:50
  • No stack traces at all, when I debug through it it just sits there waiting at the line: ResultSet results = statement.executeQuery(); – MJSalinas Mar 13 '14 at 18:04
  • Let me put it this way, I just added code to the initial class to exclude the use of this one DB and it runs perfect. All other 7 DBs get accessed the same way and it works. It's not a connection or Java problem or else it would of failed on this next available DB. – MJSalinas Mar 13 '14 at 18:19
0

After much fiddling with this DB I was later informed that this particular DB was originally migrated from a MSSQL server 2000 were we are currently using MSSQL 2008 r2. I created a new DB imported all the data from the corrupt DB to the new DB pointed my app at the new one, and it works fine now. Our suspicion is that it is either corrupt or missing stuff that MSSQL 2008 r2 needs.

Thanks for all the great help narrowing this down.

MJSalinas
  • 139
  • 1
  • 9