ODBC "Hangs"

[mattgoff]

Anyone else experienced problems with TI ODBC over "slow" connections? I'm trying to eliminate as much data from my replications as possible (let's just say that IBM made it pretty clear that I shouldn't expect any progress/improvement), so I'm establishing direct links from all of my planets to Oracle. The Oracle server is in San Francisco, and I was able to establish connections w/o issue from the UK and France TM1 servers. But I haven't been able to get AU, CN, or SG to work.

All of them test out fine when setting up the connection via the Data Sources (ODBC) applet. But, when I preview the data in TI the system locks up indefinitely. And it locks up HARD, although TM1Top shows up incrementing timer (so it's not a hung server per se), the server has a global lock so nothing else can be done, no one can log in, etc, and the locks never clear. Attempts to cancel via TM1Top fail. The only way to clear the fault is to bounce the daemon. I've tried putting dummy SQL in which should return zero rows, still locks indefinitely.

I'm opening a PMR with IBM, but if anyone has had a similar experience I'd appreciate hearing about it. These boxes are running TM1 9.5.1 x86 and Oracle Instant Client 10.2.0.4.

Thanks,
Matt

[jim wood]

Sorry if I'm teaching you to suck eggs but you do know that when you open a process or preview it the whole query (as I understand it) runs? If it is a large query query on a small pipe it might explain why it's taking so long to come back?

[mattgoff]

Sorry if I'm teaching you to suck eggs but you do know that when you open a process or preview it the whole query (as I understand it) runs? If it is a large query query on a small pipe it might explain why it's taking so long to come back?

Yep, I tried a SELECT * FROM mv WHERE 1=0 just to force results down to the column names only (zero data rows), and TI still locked everything up indefinitely. The pipe isn't that small either-- I'm interacting with the remote servers via RDP after all.... Even if the query was run as normal, I'd only expect a few thousand rows anyway-- it's just GL balances for a specific period.

Matt

[jim wood]

Sorry to labour the point, but have you tried running the query in something like toad or squirrel?

[mattgoff]

Sorry to labour the point, but have you tried running the query in something like toad or squirrel?

Yep, works fine in a 3rd party SQL browser from that box. The issue is definitely TM1-related.

Matt

[tomok]

I'm not guaranteeing this will help your situation but I have found that encapsulating your query inside a stored procedure and then calling the stored procedure instead of the query is faster. I have used this successfully in SQL Server. Don't know about Oracle. Also make sure you turn off Unicode in the TI and also play with the UseSQLFetchScroll parameter.

[jim wood]

Good point about the unicode Tomok. I've also had problems with that in the past. You can only use stored procedures once the process has been developed if I'm not mistaken? Also I don't think Oracle has them, it may have something similar mind.

[qml]

I have seen this behaviour when the database password expired. Oracle probably returns some message to TM1 that TM1 can't decipher and so hangs indefinitely. I'm not saying you're having the exact same problem, but maybe something similar?

[mattgoff]

I'm not guaranteeing this will help your situation but I have found that encapsulating your query inside a stored procedure and then calling the stored procedure instead of the query is faster. I have used this successfully in SQL Server. Don't know about Oracle. Also make sure you turn off Unicode in the TI and also play with the UseSQLFetchScroll parameter.

I'll talk to the DBAs about stored procedures, but since everything works fine on some servers (and via another program on this server), I think that's a long shot. In fact, running a sample query to get balances for Mar-2012 loaded surprisingly quickly on the SQL browser.

I tried disabling Unicode and each of UseSQLFetch=T, UseSQLFetchScroll=T, and UseSQLExtendedFetch=T with no joy.

I have seen this behaviour when the database password expired. Oracle probably returns some message to TM1 that TM1 can't decipher and so hangs indefinitely. I'm not saying you're having the exact same problem, but maybe something similar?

Except this username and query work fine when I build a TI on another server. The username and query also work on the broken server using a 3rd part SQL browser. My guess is this could be some sort of timeout that TM1's not handling correctly.

[mattgoff]

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

[lotsaram]

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.

[mattgoff]

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

[Michel Zijlema]

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

[mattgoff]

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

[LutherPaul]

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.

[qml]

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: Select all

log4j.logger.TM1.Sql=DEBUG