32-bit ODBC Connectivity

[shinymcshires]

Forum members-

I'm on build 9.0.3.173, using Excel 2003. I have been in contact with our financial software company and our VAR in regards to this issue, which has been ongoing from installation. I'll try to keep this as succinct as possible.

Our financial system is a legacy unidata database (Build 6.1 to be specific). The ODBC driver is 32-bit. After reading the Administrator's Guide, and based on the version of TM1 we have installed, I believe axnet is in use in order to enable the data connection. Please correct me if I am wrong.

The issue at hand is after executing two TI processes using the same ODBC connection, TM1 is unable to connect to the database. I am however, able to connect to the database through other means (Excel/Access/Query), as is evident in the System Tray, the VIADOBC (application that creates the ODBC DSN) application is active and running. When the TI process that uses this ODBC connection is executed, the system tray icon does not appear, which leads me to believe that axnet is being used instead of the native ODBC connection (system dsn), although the same executable process that appears when a db connection is made through Access/Excel. The financial software vendor provides limited support to the ODBC application, so they are not able to provide much direction in this case. They believe that the ODBC user licenses is the reason for the limited data connectivity; meaning that the first ODBC connection does not disconnect cleanly, and therefore the financial db believes that it is still active. After the second ODBC connection is established, all further attempts to connect are rejected due to insufficient user licences.

What my question boils down to is - Does anybody know how the TI process hands off the ODBC connection? There must be some kind of configuration that I can modify so that the ODBC connection is closed cleanly.

I have already attempted to see if the issue was on the db server side and the native ODBC application. The software vendor created a script that could be run to reset the ODBC connection from the server side. When I ran the script after the TI process was unable to connect to the db, it did not solve the issue. There is also a way to "reset" any hanging ODBC connections via the native ODBC application. That did not solve the issue either. The only work around that our VAR suggested was to stop and restart the TM1 services. This is a bit of a pain, as the recalculation time until TM1 is available again is a bit long.

The financial system is on a Red Hat Enterprise Linux ES release 3 (Taroon Update 6) Kernel 2.4.21-37.ELsmp on an i686, and TM1 is installed as a service on a MS Server 2003 server. if that helps.

The Administrator Guide suggested reading the Security Guide for more information on axnet, but I did not see any information on axnet in that guide.

I appreciate any feedback that fellow board members may have. If there is anymore information that I can provide, please let me know. Thank you.

[wissew]

Richard,
Is the TM1 server 32 or 64 bit? Axnet is only needed for running on Windows 64bit as a 64bit server.
I have run into problems like this when the TI crashes and doesn't disconnect. The other possibility is for the legacy dba to allow your ID to have a higher number of connections. It sounds like you have a single connection. With some databases the connection is not dropped until the chore is completed. If this is the case than scripting you process to run in a single TI may be a solution.
Hope one of these will work.

Wes

[shinymcshires]

I installed an application called Process Explorer on the TM1 server. There is a function that shows the dependent DLL's for the tm1sd.exe service. Below is the DLL dependency list for the tm1sd.exe service:

tm1sdexe.JPG (175.14 KiB) Viewed 4126 times

There is an ODBC32.DLL that has a Function "SQLDisconnect" that is highlighted. I had a thought that perhaps it might be this driver that is causing the issue. I'm know that the VBA for TM1 is protected, so there's probably no chance of altering that. The other thought is that there would be something on the TM1 configuration file, but the only ODBC configurations I saw on the guide was for TM1 installed on a UNIX machine.

To answer Wes -

I believe I was mistaken- the TM1 server is a 32 bit, so axnet isn't being used. You bring up a good point about increasing the number of connections. It would be worth a call to our software vendors. Actually, our VAR crammed 4 TI processes in 1 as a work-around, but since then I have built several more TI processes that access the legacy db, just to different tables.

I will update with the reply from the software vendor. Thanks for the reply! I appreciate it!

[lotsaram]

I have had this problem before connecting to U2 via ODBC. Provided the TI ran successfully with no errors the ODBC link was disconnected successfully, however if there were any errors then the session did not disconnect (although the process completed and everything appeared fine in TM1 land.) The U2 backend was set up for only 2 connections per login so if this happened more than 2x it was no longer possible to connect due to the hanging connections. The system was pretty old and didn't have idle connection termination or such like. The only solutions were to either restart the TM1 service or get a U2 DBA to green screen in to the U2 administration module and manually terminate the connections, in the end restarting the server was easier most of the time (but it was a small system that only took 5 min to load.)

[shinymcshires]

Just got a reply back from our financial software vendor. No dice on increasing the number of connections. Guess I'll have to stick with the status quo. Thanks for your replies!