ODBC Connection with Framework 10.2.1

[Esperanza]

Our Cognos server is in 64bits
We want to connect to our Oracle db via ODBC.
Since Framework 10.2.1 is in 32 bits we installed the Oracle client for 32 bit and made our ODBC connection to our Oracle db.
System DSN with Oracle Client12Home1
The ODBC test was successful.

In framework, the connection failed all the times.
see file.
Any Idea?

[sunosoft]

I am not sure why DSN is required to connect oracle database.

Oracle connection will work with TNS entry only.

add the TNS entry of the database in tnnames.ora file on each cognos dispatcher server. You might also need to add the same entry to tnsnames.ora file in the machine from where you are accessing the FM.

Anybody please correct me if I am wrong.

If you want to go for DSN only, please make sure you are creating the DSN in 32 bit odbc administrator and not in 64 bit.
By default 64 bit ODBC administrator will be opened. to open 32 bit ODBC administrator follow the below path.

C:\Windows\SysWOW64\odbcad32.exe

[Esperanza]

Yes since Framework is in 32 bit, we use the
C:\Windows\SysWOW64\odbcad32.exe
to make the odbc connection.

Before making the odbc, we installed the oracle client and checked for
tnsnames.ora and sqlnet.ora files.

When you say you are not sure for DSN, what do you mean ?
I want to make an odbc connection, I have no choice, no ?
Thank you in advance.

[Esperanza]

To add more info on my problem, we have only one Cognos server, so one dispatcher.
Framework 10.2.1 manager is on that server.

TNS entry of the database in tnnames.ora is on our Cognos server.

[sunosoft]

Ok. Sorry about the confusion which I created by asking the question.

Just moving back to your original post. You said ODBC connection is tested successfully. What about the data source which you created through cognos connection, is that data source getting tesed successfully ?

You must have provided userid and password while creating DSN on server.

What in case of data source ? Did you provide userid and password while creating data source also ? If not try that, this might resolve your issue.

[sunosoft]

Just saw your DSN screenshot. So thought to add more. Instead of slecting microsoft odbc for oracle, select the oracle driver.
You will find it if scroll down once you get window to select drivers.

I have attached screenshots for your reference. Once this is done.  While creating the data source on cognos connection, provide user id password there also.

[Esperanza]

Humm...Unfortunately I do not see the Oracle in OraClien11g_Home1 Driver in my list. (see file)
I'll have to check that with our DBA.
Are you sure the one you show (picture) in your system is a 32bit Oracle driver ?

All the fields are filled for our Microsoft ODBC for Oracle Driver.
Data Source Name
User Name
Server
When we test in Framework, it recognize the connection (no error on that).
And yes, we tested, with user and password, without and with only the user.
All failed.

The data source created in Cognos Connection are linked to a SQL Database.
Tables are downloaded once a day (From Oracle).Cubes are build with Sql db source.

Now some people here would like a live connection to Oracle (Sales Table) to have info on Sales in real time in Cognos,
to make simple list report (No Cube needed). That is why I am trying to connect with Framwork.

Thank you in advance for all your ideas on that.

[sunosoft]

Yes. Its from 32 bit only. Anyways, it will not matter whether its 32 bit or 64 bit as long as I am just showing you the list of drivers.

Check if you can get installed oracle drivers.

Till then you can try simple oracle database datsource which is only based on presence of your DB TNS entry in tnsnames.ora file on server. You do not need to create DSN(ODBC connection) for it. Looking at your requiremnet, there is not at all problem to go with normal oracle data source.

[Esperanza]

Almost there !!
Maybe because of the reboot this weekend, my ODBC connection (Microsoft ODBC for Oracle Driver) works fine this morning.
But, sniff, sniff, when I try to select the data source in Framework, I got the following error :

RQP-DEF-0177 An error occurred while performing operation 'sqlStart' status='-27'.
UDA-SQL-0104 The feature "start" in the function "{1}" is not supported.
[Microsoft][ODBC driver for Oracle]Driver not capable

Any Idea ? or I really have to check for the native Oracle driver ?
The odd thing is that we could connect our Oracle data to TM1 without any trouble with that driver (on another server).

Thank you in advance !!

[sunosoft]

Not sure. I never used this driver.

[Esperanza]

Solved !
We reinstalled the Oracle Driver 32 bit.
Our DB Admin add to do one more step by installing the following Oracle tool for 32 bit driver:
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Thank you for all your info that help us to find a solution.

[Esperanza]

In resume,
To connect an Oracle db to Framework :

1-Cognos Framework is in 32 bit. You have to install a 32 bit driver.

2-The Microsoft ODBC for Oracle is not working (as per IBM), you have to install the Oracle Driver.

3-You have to download the folowing tool from the Oracle web site
http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html,
so the driver could work correctly.

[sunosoft]

Yes. IBM is right.