Server hangs on ODBC-import

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Server hangs on ODBC-import

Post by hansstip » Tue May 06, 2014 9:51 am

Hello Forum,

Since yesterday we see server hang problems when we try to execute a importprocess.
Sometimes it is then needed the user restarts Excel to make cubes accessible to him again (sadly without releasing the TM1 user connection, so that's no practical solution).
Sometimes closing Excel is not enough and the server needs a reboot (because also a manual service stop/start does not respond).
But eitherway the TI-problem persists.

Also when we simply try to open (Edit) the process without running or even when we create a new process it hangs after entering a ODBC-source, username, password, a simple query and clicking on the preview button.
Those ODBC-credentials work fine outside TM1 and have worked before yesterday also fine in TM1.
An ASCII-process seems to work fine, but we would like to avoid a workaround to transfer the data first to an ASCII-file before import, as ODBC-import worked fine before.

We have a few big cubes (750 Mb on disk, 1 Gb in Ram) but for now I have put them offline (by removing them from the datadir and rebooting the server), so only a few small cubes load (250 Mb in Ram), but that doesn't help either...

We use TM1 version 9.1 SP 3, in a few weeks we upgrade to version 10.2.1, but that does not help us at the moment...

Thanks in advance for your help !

Greetings,

Hans van Stippent

User avatar
Michel Zijlema
Site Admin
Posts: 702
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Server hangs on ODBC-import

Post by Michel Zijlema » Tue May 06, 2014 10:05 am

Hi Hans,

Are there any related messages in the TM1 Message log?
Can you make an ODBC connection using another client (non-TI), based on the same connection settings?
If you can, how long does it take before the database returns some records? Could there be a time out issue?

Michel

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Tue May 06, 2014 10:59 am

I see no related messages in tm1server.log
The ODBC connection works fine outside TM1 (Teradata SQL Assistant).
The query returns data in just a second, it's a simple query on 1 table :

Code: Select all

Select   Case When Groep = 1 Then 'Actie' When Groep = 2 Then 'Overig' Else 'Bestelling' End Groep,
         Cast((Actiecode (Format '99999')) As Char(5)) Actiecode, 
         Cast((Datum (Format 'YYYY-MM-DD')) As Char(10)) Datum,
         Bbw, Bbm, Nuw, Num, Buw, Bum, Rtw, Rtm 
From     Ohp.Oh152_ODBC_Waardebon_Omzet
Order By Groep, Actiecode, Datum;

User avatar
tomok
MVP
Posts: 2491
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Server hangs on ODBC-import

Post by tomok » Tue May 06, 2014 12:07 pm

Try checking and/or unchecking the "Use Uncicode" button on the Data Source tab of your TI process and see if that helps.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Tue May 06, 2014 12:17 pm

I don't see such a button, I guess that is not present in version 9.1 SP 3

User avatar
Michel Zijlema
Site Admin
Posts: 702
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Server hangs on ODBC-import

Post by Michel Zijlema » Tue May 06, 2014 1:42 pm

These are difficult things to troubleshoot if there is no data available in terms of logfiles.
Maybe this thread offers some help - see the info of the posts by Moby91 in there.

Could there be some kind of change in the network environment, which is now blocking or redirecting TI when trying to access the ODBC source?

Michel

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Tue May 06, 2014 5:28 pm

I guess that would not work because I do not connect with Oracle but with Teradata. Nevertheless I've tried the following three options separately to no avail ...
UseSQLFetch=T
UseSQLFetchScroll=T
UseSQLExtendedFetch=T

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Tue May 06, 2014 5:32 pm

I've also cleared with our IT that there was no change in the network environment

User avatar
Michel Zijlema
Site Admin
Posts: 702
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Server hangs on ODBC-import

Post by Michel Zijlema » Tue May 06, 2014 5:43 pm

hansstip wrote:I guess that would not work because I do not connect with Oracle but with Teradata. Nevertheless I've tried the following three options separately to no avail ...
UseSQLFetch=T
UseSQLFetchScroll=T
UseSQLExtendedFetch=T
These options are not exclusively meant for Oracle, so they were worth a try.
In the 2nd post of Moby91 in the thread mentioned he also points to the ODBC debug log option - although I'm not sure whether this is available in TM1 9.1. If it is, this could give additional insights on what's happening.

Michel

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Fri May 09, 2014 9:09 am

I added the following line to the tm1s-log.properties file

Code: Select all

log4j.logger.TM1.Sql=DEBUG

That doesn't seem to do anything. When I start a process it still hangs with only a info in tm1server.log that the process is executed.
So I thought maybe it interferes with the other lines in tm1s-log.properties

Code: Select all

log4j.logger.TM1=INFO, S1
etc.
So I commented out all lines in this file apart from the above mentioned line. Still I see no change in tm1server.log. So I restored the original tm1s-log.properties.
I only saw one

Code: Select all

SSL read error. SSL Error: 5
but when I understand another post on the forum correct those errors could be ignored.
I also noticed the server hangs when I try to access Security > Clients/Groups from the Server Explorer.
Do you have any other suggestions ?
Our end users really need the weekly update every friday with actual data to evaluate the business.

Duncan P
MVP
Posts: 587
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: Server hangs on ODBC-import

Post by Duncan P » Fri May 09, 2014 10:05 am

Your best bet is to generate an ODBC trace. This is done outside of TM1 and will let you know exactly what calls TM1 is making. Instructions are here.

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Fri May 09, 2014 10:48 am

Thanks for your info, I tried that but sadly that did not work either :
An empty log-file is created but when I start the import-process nothing is logged.
I tried manually setting the trace flag to 1 and machine wide tracing both to no avail.
I guess that means that the problem occurs before TM1 tries to make an ODBC connection.
As the system also hangs at Security > Client/Groups it would make sense that ODBC is not the problem...

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Mon May 12, 2014 1:32 pm

The system hang at Security > Client/Groups seems to be resolved (for now) but the import problem remains, so maybe it is indeed an ODBC problem.
I am out of ideas how I can determine what causes the problem and how it can be solved... Who can help ?

User avatar
tomok
MVP
Posts: 2491
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Server hangs on ODBC-import

Post by tomok » Mon May 12, 2014 1:51 pm

Have you tried changing the SQL query to return a smaller set of records, or converting the query into a stored view in Terradata instead of the full SQL code? What about the ODBC trace, did you do that? I would say call IBM but version 9.1 is way out of support so they'll just tell you to upgrade. If you are going to upgrade anyway, why not just use another tool to dump the SQL query results to a flat file and use that in the interim.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Mon May 12, 2014 4:08 pm

I have changed the SQL query to something simple like "SELECT 1,2,3;"
A stored view is no option in our environment.
I tried the ODBC trace (see my post on 9 May 10:48) but without any results.
We are going to upgrade in a few weeks but I am afraid that (if it's indeed an ODBC problem) it still remains even after the upgrade.
If we could be sure its fixed in 10.2.1 we can live with importing ASCII dumps as a workaround for a few weeks.

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Server hangs on ODBC-import

Post by jim wood » Mon May 12, 2014 4:43 pm

I'm only asking this as I didn't see anywhere else in thread, but have you checked the windows event viewer log? Is that generating anything? Have any memory dumps been produced when the software crashes?
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

User avatar
tomok
MVP
Posts: 2491
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Server hangs on ODBC-import

Post by tomok » Mon May 12, 2014 5:15 pm

hansstip wrote:If we could be sure its fixed in 10.2.1 we can live with importing ASCII dumps as a workaround for a few weeks.
There are no guarantees but given that the version of TM1 you are on is almost 10 years old it's safe to say there have been a number of changes. Whether any of those changes relate to your issues I don't have a clue. All I can tell you is that I have connected TM1 to a multitude of ODBC data sources over the years and have had very, very few issues that were actually due to a problem with TM1. It's almost alwayssomething with the ODBC driver, or network issues, or the OS of the server. I would suggest you crank up 10.2.1 on a box and test ASAP before you waste any more time.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

hansstip
Posts: 22
Joined: Fri Sep 19, 2008 8:29 am
OLAP Product: TM1
Version: 9.1 SP 3
Excel Version: Excel 2007

Re: Server hangs on ODBC-import

Post by hansstip » Tue May 13, 2014 9:23 am

In the windows event viewer I only see errors like

Code: Select all

The description for Event ID ( 0 ) in Source ( tm1sd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: tm1sd error: 0, C12) Log file exists. Do you want to recover changes?.
I guess that meant that the server hangs without saving the changes but not WHY it hangs...
The upgrade to 10.2.1 is scheduled for mid June, so we have to find an ASCII-import workaround for a few weeks.
I keep my fingers crossed that the ODBC-import works again in 10.2.1.
We have used 9.1 SP 3 for several years without any problems regarding the ODBC-import, so I'm still puzzled what could be the actual problem.
But I guess I'll have to start building that ASCII-import workaround and hope for the best in 10.2.1...

User avatar
Michel Zijlema
Site Admin
Posts: 702
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Server hangs on ODBC-import

Post by Michel Zijlema » Tue May 13, 2014 9:44 am

hansstip wrote:In the windows event viewer I only see errors like

Code: Select all

The description for Event ID ( 0 ) in Source ( tm1sd ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: tm1sd error: 0, C12) Log file exists. Do you want to recover changes?.
I guess that meant that the server hangs without saving the changes but not WHY it hangs...
The upgrade to 10.2.1 is scheduled for mid June, so we have to find an ASCII-import workaround for a few weeks.
I keep my fingers crossed that the ODBC-import works again in 10.2.1.
We have used 9.1 SP 3 for several years without any problems regarding the ODBC-import, so I'm still puzzled what could be the actual problem.
But I guess I'll have to start building that ASCII-import workaround and hope for the best in 10.2.1...
Hi Hans,

The message means that the TM1 server was not shut down normally (likely crashed), because of which not all logged changes in the operational tm1s.log file had been committed. When the server started again it found the open log file and as the service could not write this message to te console (screen) it left the message in the Event log.
So you should wonder what happened here - did the server crash or wasn't it able to commit the changes from the tm1s.log file when shutting down. In the latter case it looks like you have some kind of access/authorization problem. Did anything change in the access rights of the account on which the TM1 server is running? Does this account have sufficient rights on the TM1 Server datadirectory?

Michel

User avatar
jim wood
Site Admin
Posts: 3609
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: Server hangs on ODBC-import

Post by jim wood » Tue May 13, 2014 12:24 pm

Which Windows OS version are you running?
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

Post Reply