ODBC Connection for Oracle is not Working

[manoj928]

Dear All,

I am trying to connect with Oracle DB using TI process in architect, I mentioned all the values in process form like DSN name, user name, password, and even a SQL, not using Preview i can see default 10 records from table, but when i click on Variable Tab to list out Variables, got a pop up as Error, but with no description , so not sure what the actual error is.

I am using TM 1 9.5.2 and Oracle installed on server is 11g.

Please help me.

Thanks,
Manoj

[rkaif]

I personally never came across this error. Can you see the SQL results when you press the PREVIEW button? If you can see data in the Preview then it means that ODBC connection to Oracle is fine.

Do you get this error with a specific SQL statement?

What happens if you run a simple SQL statement like SELECT * FROM TABLENAME?

May be you want to share the SQL statement which you are running or a screenshot...

[tomok]

Add this line to the tm1s.cfg file:

SQLFetchScroll=T

[jorelb]

I am also having the same issue but on Oracle R12. I added the additional parameter in the tm1s.cfg file and everytime I click on the variable tab, I get the blank error message and it crashes the service. Is there a known fix?

Thank you.

[moby91]

I am also having the same issue but on Oracle R12. I added the additional parameter in the tm1s.cfg file and everytime I click on the variable tab, I get the blank error message and it crashes the service. Is there a known fix?

As Tomok wrote, to resolve the issue add the optional parameter "SQLFetchScroll=T" (or "UseSQLExtendedFetch=T") to your TM1 server configuration file tm1s.cfg and restart your TM1 server.


The issue itself is documented in at least two IBM Technotes, 1440596 and 1455342:

(1)
https://www-304.ibm.com/support/docview ... wg21440596
TM1 Turbo Integrator process crashes server when using Oracle 11g

(2)
https://www-304.ibm.com/support/docview ... wg21455342
Oracle driver problem when importing data to TM1 from an Oracle Instant Client via ODBC on Red Hat Linux 5.3 and Red Hat Enterprise Linux 4.6

[jorelb]

Moby91,

Thanks it work!

[Douard]

Hi

I am working on CX 9.5 and have implemented the UseSQLFetch=T and now I am getting the following line repeated in the tm1s.ctg file each 15/1000 of a second.

"TM1.SQLQPI fetched 1 row"

This is casuing the config file to back up each dat when it reaches 100MB.

Any ideas why it does this in the cfg file and how to stop it?

Thanks

[moby91]

"TM1.SQLQPI fetched 1 row"

This is just a part of the full log message.

What kind of log message do you get ?
A debug log message ?
An info log message ?
An error log message ?

I suppose you get a debug log message, as you configured the Logger of your TM1 Server to log debug level log messages for operations performed by the it's ODBC interface by adding the logger

log4j.logger.TM1.Sql=DEBUG

to your logger configuration file tm1s-log.properties . Open the your logger configuration file tm1s-log.properties located in your database directory in a text editor and disable this logger by adding a hash character ("#") as the first character like

# log4j.logger.TM1.Sql=DEBUG

thus converting the logger in a comment. As the logger configuration file tm1s-log.properties is a dynamic configuration file read at runtime by the TM1 Server, you must not recycle (stop & restart) your TM1 Server. After the edit the TM1 Server's Logger will stop logging these debug level log messages.

[gymbull9]

The crashing can occur when you have commented out portions of your SQL and paste the SQL into the TI containing the commented portions