Does thread shutdown without msg disposition cause a leak?

[Troy Morton]

AIX 6L, QDX 5.6

I was wondering if a leaked message handle will occur if I shut down a thread from a TPS Tclproc before I return the message with a disposition.

My goal is to stop message processing when an ODBC database connection cannot be made and then continue when its available again.

I’m doing this currently by shutting down the thread when the connection fails.  A cron script runs every few minutes to check the ODBC connection, if the connection is availalbe and the thread is down, it restarts the thread.

I’m wondering if my method could cause leaked message handles since I’m not returning the current message with a disposition before shutting down the thread.

Also, maybe there’s a more elegant way of doing this that someone could share.

Any thoughts?

Troy

Topic

[Tom Rioux]

We use an ODBC call here to perform some translation based on information gathered from our EMPI database.   I gave a presentation on this at last years users conference.   A template of the ODBC was given to me by Jim K. and we have modified it to suit our needs.  

One thing I’ve added is to check to see if we are connected to the database with each message that is being sent.   It is a quick check and so far, in my testing, I haven’t seen any loss in throughput.   The code is:

set ret_val [catch {odbc SQLGetInfo $hdbc SQL_DATA_SOURCE_NAME dbname 100 cnt}]

The database name we are attempting to connect to will be returned in the variable “dbname” if we are still connected.  If we aren’t connected, then the variable will return blank.   I perform a string equal command against the known name of the database we are attempting to connect with.  If the command is false, then we abort the proc, shutdown the thread, and generate an alert.

Since the above command is performed at startup the message is still in the recovery database and gets processed when the thread is brought back up.  We also perform the command before each call of the stored procedure as there may be multiple calls to the database for each message.

I haven’t noticed any memory leaks but then again, the interface hasn’t encountered any situations where it really could be measured.

Hope this helps…

Tom Rioux

[Troy Morton]

Thanks Thomas!  Is the template of ODBC you refer to readily available?  We have developed our own ODBC procs, but it might be good to compare to what others have written.

[Tom Rioux]

Send me an email and I can forward to you all the ODBC stuff that Jim K sent to me (provided he doesn’t mind).   My email is thomasri@baylorhealth.edu

Later…Tom

[Jim Kosloskey]

Tom and all,

I have no problem if the proc and material are shared. That is what clovertech is all about.

In our usage thus far all of the DBAs have wanted a connection and disconnection with each message so I do not need to chack for are we still connected. I have a stub in the proc for where theat code would go when I finally get a DBA that wants a persistent connection.

However, I still do have the issue of what to do when I cannot get connected. I will need to check to refresh my memory or you cna check what Tom has. I will say the proc Tom got from me has been changed.

The intent of the proc was to have a single proc that would be used for all supported DBs and not require the use of the odbc.ini file. So there has been additional code to handle the differences among the various DBs we use here.

The proc is designed to invoke Stored Procedures as that is what we think is the best way for us to do ODBC. We do not believe the SQL statements belong in Cloverleaf – we feel those are business rules and belong with the application.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Tom Rioux]

Just a caveat…..we here, like Jim and his crew, do use the ODBC proc to invoke a stored procedure.   We found that one SQL statement available to us in the ODBC and believe that it provides a quick and simple way to check to make sure we are still connected.   There are probably other ways to do it but this way provides one or two lines to do the job.   If anyone has a cleaner way, then I would love to hear.

Tom

[Charlie Bursell]

Tom:

Good idea but make sure the ODBC statement actually accesses the daabase.

I do the same and for a long time I was just querying the database version.  I later found out that comes from the ODBC driver and not the database.

I now issue a simple query that I know will return no data preferably to a table with a primay key.  This is very fast and insures you are still connected

something like:

select * from mytable where key = 999999999999

[Tom Rioux]

Thanks Charlie…good info.   Since each message has multiple calls to the database, we check for a connection to the database before each SQL statement is performed.   This has actually been tested by accident and it works.   One of the admins took down the wrong test server and we lost access to the database.   I happened to be doing some testing at the time and the thread kept shutting down on me (just as designed!!!).

Thanks….

Tom Rioux

[Author]

Replies