TI and ODBC Cleanup

[George Regateiro]

Is there any documentation as to what TI does when a sql statement fails to an ODBC source? We have a connection into a Deltek GCS premier system that only has 3 available connections through ODBC.

When my processes to this ODBC can run the SQL statement successfully it appears to close all the connections ( I can run the process as many times as I want) and everything is fine.

If the sql statement errors TM1 does not close the connection to the ODBC connection. The fourth time I run the erroring TI TM1 will lock up since it is waiting in a queque for the ODBC and the ODBC thinks all its ports are in use.

Has anyone else experienced similar functionality? I am going to try to force a closing of the ODBC in the Epilog tab, but i thought since I was opening it through their GUI and not coding the connection it should handle the closing of the connection.

[Mike Cowie]

Hi George,

I don't think this is documented because it is most likely a bug. We have seen this before with Oracle as the data source at one of our clients. I think the version was 9.0 or 9.1 (can't remember, but I think Applix at the time saw it in both versions). In this case we were connecting to Oracle using the Oracle (not Microsoft) drivers. After logging inquiry #331602 in April 2007 the response from Applix was:

Thanks for your call yesterday confirming your findings about the ODBC connections being left open after entering SQL code in TurboIntegrator and getting an “Invalid SQL statement” error when hitting the Preview button.

As we discussed, I will escalate this problem with your findings so we may test/confirm and address the issue as a bug.

In the meantime, in order to minimize the impact, I’d recommended making it a practice to create and test the SQL statement directly against the Oracle database to insure it works properly before inserting it into the Query section of the TI process and hitting “Preview”.

Also, as we discussed, (when it’s possible) you should be able to restart the TM1 Server and this should release the ODBC connections that had been left open as a result of this behavior.

Pretty typical response, I'm afraid... I'd have to check to see if a particular release was slated to have a fix (I didn't log the original inquiry), but it does sound like there is a tendency in TI to leave a connection open whenever a "Failed to Execute SQL" error occurs. And, it looks like you either need to clear the connections on the database end or restart the TM1 Server - at least that's what Applix said.

One other thing you could try (no guarantees) to avoid restarting TM1 is to look and see if there are any open ELFODBC.EXE processes on your TM1 Server and, assuming you aren't in the middle of running a TI process, kill those processes. There's a chance this might free up the connections, but I'm not sure - I've used it in the past to force TI to stop trying to run a really long query, so maybe it can help here.

Regards,

[ScottW]

I can confirm that this bug is still present in 9.1 SP3 U1, where the SQL query via ODBC fails the ODBC connection is left open, this can be a real pain during development where the source system only supports a handful of simultaneous ODBC connections. The only workarounds I have found is to either manually close the connections from the host database, or restart the TM1 server.

[rmackenzie]

Speculative suggestion - would running a TI with just an ODBCClose statement help at all ?

[mce]

Hi,

I would be interested in knowing the answer for the question in the last reply?
Does anyone know if this bug has been fixed at any release till 9.5.1?

Regards,