ODBC to Oracle - Hanging

[JohnO]

Just wondering if anyone has experience with using ODBC against Oracle ADW. (19c?) and whether there is something obvious to look at before we go down a few different rabbit holes.

I have a process (Development in progress - not in production) which uses an ODBC connection and a query defined within the process. I am having an intermittent issue when I just try to view the process. It generally works (Maybe 30 seconds to open) and sometimes it just hangs indefinitely. So just opening the process in view mode executes the query (Interesting that it does this) and then hangs indefinitely. On the Oracle side the query is triggered but it is being blocked and hangs (we are seeing if we can get someone to get to the bottom of it). In PAW Administration I see the function "ServerOpenSQLQueryEx"

1) On the TM1 side I have set ODBCTimeoutInSeconds=600 - this is not having any impact. Has anyone used this and does it seem to work with Oracle? Should I expect this to stop the TM1 function / process?
2) Even if we kill the Oracle query the TM1 process is not released.
3) If I cancel the process in PAW Administration it doesn't die

Any pointers?

[gtonkin]

Two generic points:
1) Try unticking the Unicode option - had mixed results with this.

2) Sometimes when queries get locked, killing the process does nothing as TM1 is waiting for Oracle/SQL to close. Assuming you cannot get a DBA to kill what I have had to resort to is changing IdleConnectionTimeOutSeconds in the TM1s.cfg to a low number like 5. This seems to sort it in most cases. Once disconnected, set back to 900 or whatever it was. Keep in mind this is likely to disconnect most users.

[Wim Gielis]

What is the query like ?
Could you do a SELECT TOP 1 … just for the preview and the variables, then in the Prolog tab override with the full query ?

[JohnO]

Two generic points:
1) Try unticking the Unicode option - had mixed results with this.

2) Sometimes when queries get locked, killing the process does nothing as TM1 is waiting for Oracle/SQL to close. Assuming you cannot get a DBA to kill what I have had to resort to is changing IdleConnectionTimeOutSeconds in the TM1s.cfg to a low number like 5. This seems to sort it in most cases. Once disconnected, set back to 900 or whatever it was. Keep in mind this is likely to disconnect most users.

I will look at the UNICODE setting when it lets me.

We were able to get someone to kill the Oracle process but IdleConnectionTimeOutSeconds=7200 was already set and it is still going after about 6 hours.

[gtonkin]

I have Architect hang on me without anything running on the server. Does TM1Top still show something running?
Have you tried disconnecting via Architect, Disconnect Clients?

[paulsimon]

Hi

One solution to preventing the query running when you just open the TI is to change the data source to a zero suppressed view on an empty cube that just has a lot of dimensions. You then override this on the Prolog to change the data source to ODBC. That way nothing will run when you just open the process.

(I use this approach for anything that runs against something external such as ODBC or files. Even in the case of files the locking of the file may be different on the Prod compared to Dev environment and opening a process with a file that cannot be found can lose all variable settings. It is also a safer approach to avoid accessing a Dev file from a Prod environment, assuming that the Prolog has something that retrieves the correct path for the environment which then overrides the data source from view to file.)

I would suggest that as well as TM1 settings you also look at the settings on the ODBC DSN that you are using. There may be something there that you can adjust. You may need to work with the Oracle team who can monitor what is happening with the query. If it is doing a Group by and Order by then it may be taking some time to resolve this before it starts returning any results to TM1. It is possible that this long delay is leading to TM1 thinking the ODBC connection has timed out

Regards

Paul Simon

[JohnO]

The process was still running in TM1 this morning. I killed the TM1 task via Windows Task Manager. It wouldn't restart as the port was locked. So I ended up rebooting the windows server.

I have now unticked unicode, maybe that will help. When I get to the bottom of it I will share.