ODBC Connections and locked / stalled threads

[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,

[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

[Steve Rowe]

Thanks for the tips on the cfg, appreciated

[JohnO]

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.