ODBC Hangs

This topic has 10 replies, 4 voices, and was last updated 15 years, 7 months ago by Brahim AlHawwas.

Creator

Topic
April 18, 2009 at 2:10 pm #50810
Brahim AlHawwas
Participant
Dear All,

I am facing a scenario in which I have developed an ODBC connection to a DB in which I am storing information from HL7 message. I have separated concerns by splitting the receiving of messages from other systems in a process. The xlt and DB handling is in another process. I have noticed that the later, db handler, process hangs after some time. That is usually two to four days causing the whole process to stop working. I guarantee that there is no loss of messages because of reply generation through TCL in the dbhandler process so if it hangs no reply is generated thus no data is lost.

May any one guess what might be the cause of such hanging. When the DB is shutdown the thread complains but do not hang. The process goes IDLE in the process watch while it is running but hanged.

Thanks in Advanced.
Creator

Topic

Viewing 9 reply threads

Author

Replies
- April 18, 2009 at 6:42 pm #67610
  Charlie Bursell
  Participant
  My guess is you are not properly testing for a valid connection with each message. There are various reasons why the connection is no longer valid, i.e, TCP/IP, DB Admin kicking you off, etc.
- April 19, 2009 at 7:52 am #67611
  Brahim AlHawwas
  Participant
  Hi Charlie,
  
  I do not think that it is related to any of your scenarios. I am connecting in a while loop that would sleep between every trial and tries certain number of times then replies with negative acknowledgment if it could not connect. I am also handling three SQL exceptions explicitly and everything, i.e. all the code in catch statement, generally.
  
  Do not forget that the whole process hangs and not only the DB handler thread.
- April 19, 2009 at 1:52 pm #67612
  Jim Kosloskey
  Participant
  What O/S, what release of Cloverleaf(R), are you using the Healthvision provided DataDirect Drivers, what DB?
  
  email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.
- April 19, 2009 at 2:06 pm #67613
  Brahim AlHawwas
  Participant
  Hi Jim,
  
  My OS is Redhat and the DB is Oracle and I am calling Functions from my schema. I am using cloverleaf 5.6 and DataDirect ODBC from Healthvision.
- April 19, 2009 at 5:32 pm #67614
  Charlie Bursell
  Participant
  The process includes the thread and the DB drivers. If any of them hang, by definition, the process will also hang since it cannot get back to the command thread
- April 27, 2009 at 9:53 am #67615
  Brahim AlHawwas
  Participant
  Hi Charlie,
  
  Sorry for being late but I did not receive the email of your reply. I am wondering what might cause such hang to either the process or the thread or even to a lower level the TCL proc. I do not have any infinite loop that would terminate based on condition that might not happen and no other loop might do so in my proc. I can not see why this is happening. The watcher of the process shows engine IDLE.
  
  By the way is there a way to check if the thread is down and start it automatically if we could not overcome this issue. Can I do that in Alert so it would execute the TCL proc assigned to an action. I did not even try to look at alerts.
  
  I am thinking about automating the whole thing from A-Z and I have a target to have my interface running for a month at least without any interruption. That it not even a single mouse click.
- April 27, 2009 at 2:01 pm #67616
  Charlie Bursell
  Participant
  It would be hard to pprovide more without more info. I suggest you contact Support with this problem.
  
  Yes, you could set up an alert to restart the thread.
- May 1, 2009 at 10:23 pm #67617
  Ed Mastascusa
  Participant
  Hello Brahim,
  
  We see similar chronic “hanging” problems. In our case the root cause is some design issues with the remote database. Our stored proc “exec” gets deadlocked / blocked in the remote database and never returns. We know this because we have echos immedaitely prior and after the SQLExecDirect call. While the SQLExecDirect call is hung all the other threads in the process stop processing messages.
  
  We are using the odbc 6.0 package in TCL. Our remote DB is MS-SQL.
  
  There are some things you can do to mitigate the problem if this is similar to what you see.
  
  1) We quarantine the ODBC threads in a separate site so that when the problem occurs fewer threads are affected.
  
  2) The only immediate “fix” for us is to have the DBA kill the query that is deadlocking with us. This usually results in the SQL exec finishing successfully and normal execution resumes. (In our case if we abort and repeat the query we immediately deadlock again.)
  
  When we have this issue, we generally cannot just issue a “pstop” command to kill the thread – we have to hcienginestop the entire site, and usually hcienginestop goes all the way to using SIGKILL in order to complete.
- May 2, 2009 at 1:48 am #67618
  Jim Kosloskey
  Participant
  Ed,
  
  We also use Stord Procedures and also with Sql Server, DB2, and Oracle.
  
  We have not experienced the lockup of which you speak.
  
  A difference is we do not use SqlExecDirect we use SqlExec (after preparing the statement).
  
  Our reading has indicated to us this method is more reliable (especially with
  
  SqlServer) than using SqlExecDirect and is supposedly higher performing.
  
  You might give that a try.
  
  email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.
- May 3, 2009 at 7:18 am #67619
  Brahim AlHawwas
  Participant
  Hi Jim,
  
  I do not even know what is the difference between SqlExecDirect and SqlExec. I will go with the latter one as I was using the former. I actually notice that I get the echo back from SQLAllocHandle but not SqlExecDirect. I think you are hitting the cause of the problem. I would be doing that soon. I will send my feedback as well.
  
  Is there any harm in replacing between these two functions.
Author

Replies

Viewing 9 reply threads

The forum ‘Cloverleaf’ is closed to new topics and replies.