Occasionally stuck making ODBC connection

[Joris]

Hi everyone

We're struggling with the ODBC connection of TM1 (10.2) to our database (IBM Netezza N3001-002). Sometimes (but not very often) a process that opens an ODBC connection hangs. This happens both when using ODBC as source as well as doing it in code in the Prolog.

Some details:
- It doesn't happen often and it’s very difficult to pinpoint if/when it will happen. I wrote a process that opens an ODBC connection, reads a few lines, and closes it, and do that in a loop for 1000 times without a problem. Other times just a single query fails.
- The TM1 logs only show that a process has been started. The corresponding ID is then only seen again in the logs with the message that the process is running for a long time, or mentioned by other processes that are waiting for that ID to finish.
- No trace of the failing connection can be found in any of the logs on the Netezza. I’m not so experienced with TM1, but I am with Netezza. So I’m pretty sure the connection is never actually made.
- The TM1 server and the Netezza are in the same subnet and should communicate more or less directly (no firewall; some switches are possible of course).
- There does not seem to be a relation between the error occurring and the level of activity on either machine.

This seems to be some kind of network problem, but I have no idea how to pinpoint the exact issue.

The big problem is that when this occurs the process hangs indefinitely blocking every following process.

Does anybody recognize this kind of behavior?
Is there a way to make TM1 not block indefinitely but try again?


Thanks in advance for all ideas and suggestions
Joris

[Joris]

Not sure if this gives some information, but I can try: I attached a screenshot of Wireshark during one of the occurances (Wireshark filtered on Source or Destination equals IP TM1 Server). As you can see there is only traffic in one way and it immediatly stops (while a succesfull ODBC connection will result in many more lines).

[Steve Rowe]

Hi, In my experience this usually happens because the destination server is locked performing some kind of major activity. Though you've checked to see if the connection is made is there any reason Netezza would be refusing the connection or locked in some way?

I'm not aware of any to test if the connection is available without actually making the connection.

[Drg]

I think you need more information from tm1 logs:
TM1.ODBC
TM1.Sql
TM1.SQLAPI
TM1.Lock
TM1.Lock.Exception
see more here:
https://www.tm1forum.com/viewtopic.php?t=12170

This example how create extended logs to different file:

Code: Select all

#tm1s-log.properties
log4j.logger.TM1.Lock.Exception=DEBUG, LOCKS
log4j.appender.LOCKS=org.apache.log4j.SharedMemoryAppender 
log4j.appender.LOCKS.File=LOCKS.log 
log4j.appender.LOCKS.MemorySize=5 MB 
log4j.appender.LOCKS.MaxFileSize=50 MB 
log4j.appender.LOCKS.MaxBackupIndex=100 
log4j.appender.LOCKS.TimeZone=local 

[Joris]

Hi, In my experience this usually happens because the destination server is locked performing some kind of major activity.

The Netezza can handle quite some simultaneous load and we also experience it when the Netezza is idle. Also, the fact that WireShark does not show any traffic in the opposite direction makes me think the issue has to be located somewhere earlier.

Code: Select all

#tm1s-log.properties
log4j.logger.TM1.Lock.Exception=DEBUG, LOCKS
... 

Interesting, we'll try this.

[dr.nybble]

Based on your WireShark capture this looks to be a networking or Netezza issue.

You might try setting up pings to different hops on the route to Netezza to check.