ODBC Connections and locked / stalled threads

Post Reply
User avatar
Steve Rowe
Site Admin
Posts: 2424
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

ODBC Connections and locked / stalled threads

Post by Steve Rowe »

Hi,
Historically we have had issues over the years with failed ODBC connections from TM1 to SQL Servers.

These used to occur very infrequently, a couple of times a year perhaps. What we see is the that the TI process initiates the ODBC connection and then sits there indefinitely waiting for a response. This locked open thread then cause a queue on the server.

The only way to clear the thread is to restart the DB. It doesn't respond in Top / Admin console.

Our belief is that this happens because "network turbulence" (yes that's a real thing!) causes the ODBC connection to fail post connection and then TM1 sits there waiting for a response that never comes. We are able to directly query the same DB using the same ODBC connection from Excel when the failure is happening, so it doesn't seem like a database side locking problem.

I have a suspicion that this only occurs with connections to Microsoft SQL, though I wouldn't bet my house on it.

Just curious if anyone else has been suffering from this and has any insights to share? One of our customers (recently moved to PAoC) has seen a significant increase in this type of failure and it's proving very challenging to establish what is causing the feed to fail.

Cheers,
Technical Director
www.infocat.co.uk
User avatar
gtonkin
MVP
Posts: 1212
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: ODBC Connections and locked / stalled threads

Post by gtonkin »

In days gone by when I had SQL doing something and we could not kill the process I set IdleConnectionTimeOutSeconds=5 in the TM1s.cfg and this more often than not released it.

I have not looked at this one yet but may be of use:
ODBCTimeoutInSeconds

Not sure on the failure but I may have an undocumented parameter that IBM shared with one of my clients where SQL queries run via RunProcess where not behaving.

Edit: This is the parameter that was needed when using RunProcess: DisableParallelODBCSQLQuery
User avatar
Steve Rowe
Site Admin
Posts: 2424
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: ODBC Connections and locked / stalled threads

Post by Steve Rowe »

Thanks for the tips on the cfg, appreciated
Technical Director
www.infocat.co.uk
JohnO
Posts: 106
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.9.x
Excel Version: Office 365 16

Re: ODBC Connections and locked / stalled threads

Post by JohnO »

Steve Rowe wrote: Thu May 23, 2024 2:57 pm
The only way to clear the thread is to restart the DB. It doesn't respond in Top / Admin console.
I had this a couple of years back with an Oracle source. I worked with IBM support. It was not respecting the config timeout setting. Killing the odbcproxy exe will prevent restaring the tm1 service.
Post Reply