Server hangs on ODBC-import
-
- 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
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
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
- Michel Zijlema
- Site Admin
- Posts: 712
- 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
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
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
-
- 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
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 :
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;
-
- MVP
- Posts: 2831
- 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
Try checking and/or unchecking the "Use Uncicode" button on the Data Source tab of your TI process and see if that helps.
-
- 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
I don't see such a button, I guess that is not present in version 9.1 SP 3
- Michel Zijlema
- Site Admin
- Posts: 712
- 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
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
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
-
- 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
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
UseSQLFetch=T
UseSQLFetchScroll=T
UseSQLExtendedFetch=T
-
- 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
I've also cleared with our IT that there was no change in the network environment
- Michel Zijlema
- Site Admin
- Posts: 712
- 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
These options are not exclusively meant for Oracle, so they were worth a try.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
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
-
- 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
I added the following line to the tm1s-log.properties file
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 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 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.
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
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
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.
-
- MVP
- Posts: 600
- 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
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.
-
- 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
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...
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...
-
- 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
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 ?
I am out of ideas how I can determine what causes the problem and how it can be solved... Who can help ?
-
- MVP
- Posts: 2831
- 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
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.
-
- 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
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.
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.
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Server hangs on ODBC-import
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.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- MVP
- Posts: 2831
- 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
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.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.
-
- 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
In the windows event viewer I only see errors like
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...
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?.
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...
- Michel Zijlema
- Site Admin
- Posts: 712
- 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
Hi Hans,hansstip wrote:In the windows event viewer I only see errors likeI guess that meant that the server hangs without saving the changes but not WHY it hangs...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?.
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...
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
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Server hangs on ODBC-import
Which Windows OS version are you running?
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7