ODBC "Hangs"

sunnypure

Hi Matt,

To get around the ODBC hanging have you thought about either separating the ODBC run from the data load or else encapsulating it within another process?  Just thinking aloud here but what about the following 2 approaches:

1/ Run the ODBC query only once on a server with close proximity to the Oracle DB. and dump it out as a text file.  Then ftp the text file(s) to the satellites and once the ftp is complete then load the text files.

2/Run the ODBC query via a 3rd party tool with a command line interface that you can pass params to and run with ExecuteCommand. Let the 3rd party tool dump a text file export of the query locally then load the text file with TI.

It's more work and moving parts (but not by much) but it gets around anything that might be peculiar to how TM1 handles a slow ODBC.

奪命排骨飯

lotsaram wrote:To get around the ODBC hanging have you thought about either separating the ODBC run from the data load or else encapsulating it within another process?  Just thinking aloud here but what about the following 2 approaches:

1/ Run the ODBC query only once on a server with close proximity to the Oracle DB. and dump it out as a text file.  Then ftp the text file(s) to the satellites and once the ftp is complete then load the text files.

2/Run the ODBC query via a 3rd party tool with a command line interface that you can pass params to and run with ExecuteCommand. Let the 3rd party tool dump a text file export of the query locally then load the text file with TI.

It's more work and moving parts (but not by much) but it gets around anything that might be peculiar to how TM1 handles a slow ODBC.

Yep, that was my next plan.  It looks like the "slow link" was a red herring, so I'm going to leave the extra load on Oracle for now (with all the TM1 servers individually connecting to it) in order to avoid a complicated, ex-TM1 scheme.  The query itself is very light; I get the first row returned in 2-3 seconds and the full transfer is complete in 10ish when I run it locally, a hair longer over the WAN.  I already do automate a bunch of stuff in perl outside of TM1, so if push comes to shove I think I'll end up with something similar to your suggestion (and create a nightmare for the next administrator after I've gone and it breaks....).

Thanks,
Matt

落日清风

mattgoff wrote:qml's comment got me thinking about possible error messages that TM1 might be eating.  On some boxes, I've had to copy in two missing DLLs (mfc71.dll and msvcr71.dll) but I only did it if I got a specific error when I attempted to test the connection in the Data Sources (ODBC) applet (after installing the driver, before I get to TM1).  In this case, it worked fine when I tested it there so I didn't bother copying them in.  After copying in the dlls, it worked!

Thanks for all of the advice.  Sometimes a brainstorming session is all that's needed!

Matt

Hi Matt,

I'm running in the same kind of problem you described in this thread. I have a query joining some 16 million row tables in a SQL Server database, which in MS SQL Server Management Studio runs for about 5,5 minutes to come up with a resultset (the DBA experts did all the optimizations they could think of on this query). If I put this query in a TI and click Preview, the first ten rows will appear in the preview window after about 5 or 6 minutes. But when I try to run the query, the TI process hangs indefinitely (the Time counter is adding up in TM1 Operations Console / TM1Top, but the TM1 service is showing no activity in the Task Manager).

I'm a bit curious about your solution - (from where to) where did you copy those dll's?

Michel

龙创天下

Michel Zijlema wrote:I'm running in the same kind of problem you described in this thread. I have a query joining some 16 million row tables in a SQL Server database, which in MS SQL Server Management Studio runs for about 5,5 minutes to come up with a resultset (the DBA experts did all the optimizations they could think of on this query). If I put this query in a TI and click Preview, the first ten rows will appear in the preview window after about 5 or 6 minutes. But when I try to run the query, the TI process hangs indefinitely (the Time counter is adding up in TM1 Operations Console / TM1Top, but the TM1 service is showing no activity in the Task Manager).

I'm a bit curious about your solution - (from where to) where did you copy those dll's?

I don't recall where I got the DLLs-- I think I just copied them across from my PC.  In our case I'm using Oracle Instant Client, so I put the files in that folder.  I had originally gotten the fix/advice from one of the Oracle support forums.

Matt

wwpsy

Hi All,
My SQL server ODBC connection hangs while running a Stored procedure and then after two minutes an error message pops as below.
"Error: Epilog procedure line (5): Error executing SQL query: "execute dbo.ProcNameABC".

Interestingly, the proc works and then hangs.

Also, I have read that unicode has to be disabled. Can someone guide me on this? I am using TM1 9.5.2


Thanks,
Luther.

zplwf

It looks like the stored procedure fails in your relational database. You will have to check there what is wrong with it (or with your SQL command). If you want to see the query and the error code/description returned to TM1 appear in your TM1 server's message log, you can switch on SQL debug in tm1s-log.properties. Just add the following line to the file.
Code: log4j.logger.TM1.Sql=DEBUG