Cognos 8.3 failing to connect to data source. Why?

I have installed Cognos BI 8.3 on my machine, which has a Windows 7 OS 64-bit. In IBM's supported environment list for Cognos 8.3, Windows 7 is not listed. On top of that, it says the product will only integrate with 32-bit 3rd party libraries. So, you can already see that I'm already starting on a bad foot. But anyways, this is the machine I have and that is the software I got, so I decided to see if they would work together..

I installed Oracle 10g Express database also in my machine and Apache 2.2 server. Up to there no problems.

Before moving further, I copied ojdbc14.jar to cognos/.../webapps/p2pd/WEB-INF/lib and added the Oracle database in Cognos Configuration. Tested, passed.

With the database started, the Apache server started, and Cognos started, I was successfully able to start Cognos Connection on the computer. So, it looks like the OS is not an issue.

(You can see everything I did by following the steps on this site, which since two days ago seems to be down but I'm thinking will eventually come back up)

Then I decided to try the samples, so I tried to create a data source connection from Cognos 开发者_如何学Pythonto the Oracle database. So, in the admin config console (i.e. Administer Cognos Contents > Configuration), I decided to create a new Oracle data source. I put the credentials, but BAM.. testing fails.

Fail Message:

QE-DEF-0285 The logon failed.
QE-DEF-0323 The DSN(ODBC)/ServiceName is invalid. Either the DSN is missing or the host is inaccessible. 
RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in: 
testDateSourceConnection

UDA-SQL-0031 Unable to access the "testDataSourceConnection" database.
UDA-SQL-0532 Data Source is not accessible: "XE".
ORA-12154: TNS:could not resolve the connect identifier specified

RSV-SRV-0042 Trace back: 
...

From what I have researched, it could be a number of things, but nothing seems to work. Here is what I tried:

1. Adding ODBC driver. Added the Oracle XE driver, even making sure it was done in the 32-bit ODBC manager (i.e. Windows\SysWOW64\odbcad32.exe). That didn't work.

2. Added ORACLE_HOME, LD_LIBRARY_PATH, and TNS_ADMIN to my environment variables.

3. tnsping'ed the XE database and it the ping returned ok.

4. There are no tnsnames.ora duplicates in the computer.

5. I tried and I am able to connect to the database via sqlplus. Did I mention that Cognos Config database test also passed?

6. Installed Oracle XE client. But didn't do much with it because the Oracle server is installed in the computer and also has a client component.

Has anyone come across this problem? I haven't been able to diagnose the problem or make even the slight progress for days. If you would like me to provide more information on any of the solutions I tried, please do ask. If you have a potential solution or, even better, if you have been able to solve this problem before, please let me know how!

Thanks!

One thing to keep in mind : certain portions of Cognos BI are implemented via Java and other portions are implemented via native C++ processes (the BI Bus processes)

Cognos connects to the content store database from the Java process using JDBC.

The BI Bus processes will connect to Oracle using a native oracle client.

So based on your description, I'd say that the problem area is in the native oracle client configuration (or the Cognos service's perspective of it).

You mentioned adding the ORACLE_HOME, LD_LIBRARY_PATH, and TNS_ADMIN environment variables in windows, and the fact that Oracle Utilities like SQLPlus and TNSPing are working for you.

When you added the environment variables, did you add them to the SYSTEM area for environment variables or the User area...?

If you're running Cognos as a service, its going to be a child process of SERVICES.EXE, which is run as system (this process is also not restarted unless you reboot the machine).

I'd try this :

1. make sure the above environment variables are defined for the SYSTEM user (in the system area, not the user area)
2. once you've done this, reboot the machine

See if you can create the database connection now.

If that doesn't work, I'd suggest doing a "run as" of SQLPlus or TNSPING as the SYSTEM user (and see if that works).

In order to use oracle as data source, you must install 32 bit client. 64 bit client will not work. So if you installed oracle express 64 bit, and you don't want to uninstall it, you can just install additional 32 bit client, and make sure to set it as your default home.