ODBC with a 64 bit server

[Catherine]

Hi all,

I know there are some topics on the subject, but some are quite old. And I want to be sure I have correctly understood.

I have a TM1 9.5.2 64bit Server on Windows 2008 x64 Server with Oracle 10i.

Should I definitely install a 64 bit Oracle driver ? Does it exist ? Will I be able to make my process work once it is installed?

Many thanks for your help

[David Usherwood]

Should I definitely install a 64 bit Oracle driver ?
Yes
Does it exist ?
Well, if it didn't, you'd find it hard to install
Will I be able to make my process work once it is installed?
Yes, once you've done the TNSNAMES.ORA gubbins, got a set of credentials, found the table/view and written your query.
Remember that TIs run on the server so you need to check that all the Oracle stuff is set up on the server. What you have on the client isn't relevant.

[moby91]

I have a TM1 9.5.2 64bit Server on Windows 2008 x64 Server with Oracle 10i. Should I definitely install a 64 bit Oracle driver ?

As you are using the 64-bit TM1 9.5.2 server software for Windows x64, you must use a 64-bit ODBC Driver for Windows x64.

Does it exist ?

I recommend you use the 64-bit Oracle Instant Client ODBC Driver for Windows x64 offered by Oracle as a free download:

http://www.oracle.com/technetwork/topic ... 89540.html
Instant Client Downloads for Microsoft Windows (x64)

You will need the Instant Client Basic Package and the Instant Client ODBC Package.

[Catherine]

Thank you very much,
It worked with a 64 bit Oracle driver.

[Martin Ryan]

The server I've got is 64 bit and running Windows 2008 R2. The Express 9.5 install I've got appears to be 32 bit. I'm a bit surprised that it's not 64 bit, but it's installed in Program Files (x86) so I figure it must be 32 bit.

I've installed 32 bit ODBC Oracle drivers, because as I understand it, what matters is what your install of TM1 is, not what the hardware is. Is that correct? Or should I have installed 64 bit drivers, because the hardware is 64 bit?

My problem shows up in that I can open TI processes and preview the data ok, but the server is crashing when I try to run the process with the only information being found below, in the tm1server.log

Code: Select all

15772   INFO   2011-07-26 16:03:45.431   TM1.Process   Process "Create_Contracts_Dimension" executed by user "Cognos Express\Cognos Express Administrator"
15772   INFO   2011-07-26 16:03:45.547   TM1.SQLAPI   Checking Driver Capabilities for database "pippin_reports_revforca" 
15772   INFO   2011-07-26 16:03:45.547   TM1.SQLAPI         Driver supports SQLFetchScroll

Any help appreciated,
Martin

[qml]

I've installed 32 bit ODBC Oracle drivers, because as I understand it, what matters is what your install of TM1 is, not what the hardware is. Is that correct? Or should I have installed 64 bit drivers, because the hardware is 64 bit?

The hardware is not the main ingredient in this dish as almost every modern PC is 64-bit. It's the operating system and the software (and how they interoperate) that is the key here.

Basically, on a 64-bit version of Windows you can work with both 32-bit and 64-bit ODBC drivers (although to manage the 32-bit drivers you may need to use a different tool: %Windir%\SysWOW64\odbcad32.exe; oddly enough, to manage the 64-bit connections you need to use %Windir%\System32\odbcad32.exe). Now, 32-bit software can only work with 32-bit ODBC drivers and similarly, 64-bit software can work only with 64-bit drivers.

So it looks like your choice was the right one if your CX is really a 32-bit package.
What do the 2 different versions of odbcad32 show you?

Also, from your log it looks like CX is able to initiate some communication with the driver, so it's not likely to be a 32/64-bit issue. Maybe the driver or the CX install itself is corrupted? A good old-fashioned bug is not out of the question either.

[mattgoff]

The server I've got is 64 bit and running Windows 2008 R2. The Express 9.5 install I've got appears to be 32 bit. [...] I've installed 32 bit ODBC Oracle drivers, because as I understand it, what matters is what your install of TM1 is, not what the hardware is.

Right.

My problem shows up in that I can open TI processes and preview the data ok, but the server is crashing when I try to run the process with the only information being found below, in the tm1server.log

If TM1 crashes, my advice is to go to support. Since it's TM1 crashing, you're not going to get much from the basic TM1 logs. Support can have you enable some extra debug logging and process your crashdumps. You might also want to see if there's anything in the Event Logs that's useful.

Crashing is Sev1, so you should get a reasonable fast response. But, while you're waiting, you might try adding "UseSQLFetch=T" to your cfg file to "downgrade" the ODBC calls. It's not clear from the docs if this will over-ride whatever the driver says it's capable of, but it's worth a shot. I'd also make sure you're running the latest version of the driver. I seem to remember having a problem way back with a certain Oracle ODBC driver causing crashes that was only fixed with a driver upgrade.

Matt

[Martin Ryan]

Thanks Matt and qml. After a bit of mucking around with ODBC drivers I discovered that it does need to be the 64 bit driver. I think Express is a 32 bit program, but that ICAS (i.e. TM1) is 64 bit. However it sticks it all in the same place under Program Files (x86).

In terms of my server crash problem, after a lot of mucking around I tried unchecking the "Use Unicode" check box and low and behold it all came right. So for some reason the Oracle driver was tripping over the unicode.

Thanks for the assistance,
Martin

[Catherine]

In terms of my server crash problem, after a lot of mucking around I tried unchecking the "Use Unicode" check box and low and behold it all came right. So for some reason the Oracle driver was tripping over the unicode.

I met exactly the same problem (TM1 9.5.2 64bit Server on Windows 2008 x64 Server with Oracle 10i). After installing 64 bit Oracle driver, I was able to see and select my datasource in a process. I wrote ma sql request, and was able to preview the first lines answering my request. But as soon as I tried to go to Variables tab, it made my TM1 server crash. I also tried to uncheck the "Use Unicode" box, and it worked.

Any idea about this problem? Even if I can't use Unicode for one reason, it would be great to have a message !

[tomok]

Any idea about this problem? Even if I can't use Unicode for one reason, it would be great to have a message !

Have you read this entire thread? If so, did you try adding "UseSQLFetch=T" to your tm1s.cfg file?

[mvaspal]

Hi

Besides 'adding "UseSQLFetch=T" to your tm1s.cfg file', I have just found the following entry about Oracle 11g ODBC and Unicode in the Release Notes of Cognos Express 9.5, it may help:

DNS with UNICOEDE and Xcelerator
TI processes importing Unicode data from an Oracle database using the version 11g client/ODBC
driver can fail unless an option is specified when configuring the DSN.
To avoid problems with SqlFreeStmt SQL_CLOSE, check the Enable Closing Cursors option on
the Application tab when configuring the DSN of the Oracle 11g ODBC driver.This Oracle
behavior is documented in the Oracle support website.

I think Martin is right about Express and ICAS server: Express is basically a 32-bit product (Express Manager, Cognos Connection, 8.4 BI server, etc.), however, ICAS is a 64 bit one and so 64 bit ODBC drivers are needed to import data with TI. If you go to folder 'C:\Program Files (x86)\IBM\Cognos Express\Xcelerator' you find 'bin' and 'bin32'. tm1s and tm1sd are in the 'bin' folder and the TM1ExcelService.exe in the 'bin32' as expected.

Hope this helps,
Matyas