Product: IBM Cognos Business Intelligent
Version: 8.x - 10.x
Cognos BI still uses 32-bit Oracle driver in Windows edition although in their latest version of 10.2. This is so upsetting that Windows has been running in 64-bit since Windows XP more than 10 years ago, and IBM's product management still not keeping up with the technology.
Software wise, I really can't argue what is the benefit of using 64-bit Oracle database driver for reporting, but technology wise, I see it is causing administrator to keep multiple version of the same Oracle software (client version in this case), and confusion in software management, and complication in environment testing and testing, especially the junior IT admin are clueless about 32-bit vs 64-bit testing.
This post is going to hack Cognos BI such that you can manually set the ORACLE_HOME to point to any 32-bit Oracle client or server installation instead of relying on Windows system environment.
It is very common that in mid to small scale environment, there will not be a dedicated Cognos BI server, but the same server will be running other software, especially sharing the server with multiple teams. When there are multiple version of Oracle database server/client installed into the machine, Cognos BI will only use (default) the ORACLE_HOME set in global environment variable.
To override this behavior, you need to simulate what UNIX does:
1. create a local Windows user. Recommend not to use Windows domain user, because if the lost network connection with PDC or backup DC, then Cognos will not able to startup due to authentication error. For this example, let's call this login as cognos_admin
2. grant this user administrator privilege, and remote desktop connection, if needed to use RDC to login
3. login to Windows as cognos_admin user
4. Change user Windows environment ORACLE_HOME
4.1. In Control Panel, click on System and Security
4.2. Click on System
4.3. Click on Advanced system settings on left panel
4.4. Click on Advanced tab
4.5. Click on Environment Variables...
5. Under "User varaibles for cognos_admin" on the top half of screen, click on New... button to add ORACLE_HOME
6. Enter variable name as oracle_home (not case sensitive), and value p:\Oracle\product\11.2.0\client_1
7. Now we can configure the Cognos Windows service to run using above username
8. Press key Windows-R, and type services.msc. This will run Windows Services program
9. Double click on IBM Cognos service to change its setting
10. Click on Log On tab
11. Enter username as cognos_admin and password
Now, stop and start Cognos BI from IBM Cognos Configuration or Windows services. You do not need to close and restart Cognos Configuration to activate the new ORACLE_HOME setting