IBM Cognos Analytics: Content Store JDBC Configuration for Oracle

Product: IBM Cognos Analytics

Version: 10.2.1 - 12.0.0

OS: Windows, Linux

IBM Cognos Analytics documentation about Content Store's configuration using Oracle database is very limited.

This post will list down several configurations

Option 1: Using TNS from tnsnames.ora. Most Recommended

Using TNS name is the most preferred method for following reasons:

1. Changing of Oracle hostname doesn't need to change Cognos configuration, and can be done in real time

2. You can leverage Oracle scan listener functionality in tnsnames.ora

3. You can leverage fail-over Oracle functionality in tnsnames.ora

4. You can leverage standby fail-over Oracle functionality in tnsnames.ora

5. You can troubleshoot Cognos content store connectivity to Oracle DB using tnsping [TNS name] command

6. Supports Oracle RAC through tnsnames.ora

Steps:

1. Delete all entries under Data Access > Content Manager

2. Create new Content Store, and pick "Oracle database (Advanced)"

3. Enter the pre-configured Oracle TNS name from tnsnames.ora under "Database specifier"

4. In this example, "ora21c" is the TNS name entry that already configured in tnsnames.ora

Option 2: Uses URL syntax

Uses a JDBC URL syntax of //[hostname]:[port]/[service name] will leverage Oracle Service functionality, as well as making the entry easy to maintain.

There is not much advantage to this approach compare to above, so I will only list down disadvantage:

1. Changing of Oracle hostname, port, service name will required to shutdown Cognos daemon

2. Not possible to perform connectivity test using exact URL

3. Doesn't support Oracle RAC

4. Doesn't support Oracle standby

5. Doesn't support Oracle fail-over

Steps:

1. Delete all entries under Data Access > Content Manager

2. Create new Content Store, and pick "Oracle database (Advanced)"

3. Enter //[hostname]:[port]/[service name] under "Database specifier"

3.1. hostname - Oracle database server's hostname

3.2. port - Oracle listener's port. Default is 1521

3.3. service name - Oracle listener's service name as shown in "lsnrctl status [listener]"

4. In this example, "//ora-db-21c:1521/ora21c.abc.com" will connects to host ora-db-21c on port 1521, with Oracle service name  ora21c.abc.com

Option 3: Uses long TNS syntax

This is an lengthy and poor readability configuration, but it offers some advantages:

1. You can leverage Oracle scan listener functionality in tnsnames.ora

2. You can leverage fail-over Oracle functionality in tnsnames.ora

3. You can leverage standby fail-over Oracle functionality in tnsnames.ora

4. You can troubleshoot Cognos content store connectivity to Oracle DB using tnsping [TNS name] command

5. Supports Oracle RAC through tnsnames.ora

Disadvantage:

1. Length to type, and potentially make mistake

2. Difficult and time consuming to modify

3. Modification of hostname, port, service name will require to restart Cognos daemon

4. using tnsping to perform connectivity test is challenging for those who doesn't know the syntax that has been long forgotten

Steps:

1. Delete all entries under Data Access > Content Manager

2. Create new Content Store, and pick "Oracle database (Advanced)"

3. Under "Database specifier," enter "(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ora-db-21c)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora21c.abc.com)))"

3.1. HOST - Oracle database server's hostname

3.2. PORT - Oracle listener's port. Default is 1521

3.3. SERVICE_NAME - Oracle listener's service name as shown in "lsnrctl status [listener]"

Option 4: Uses SID

This is the least recommended approach, but remains in Cognos product documentation for the longest time.  The main reason is that Oracle documentation has recommended not to use SID to login for any application since late 1990s.

The syntax is hostname:port:SID, which is very similar to URL syntax above, but it uses ":" instead of "/"

There is not much advantage to this approach compare to above, so I will only list down disadvantage:

1. Changing of Oracle hostname, port, service name will required to shutdown Cognos daemon

2. Not possible to perform connectivity test using exact URL

3. Doesn't support Oracle RAC

4. Doesn't support Oracle standby

5. Doesn't support Oracle fail-over

6. SID might be different than service name that Oracle DBA is familiar, and might cause communication gap, and confusion during connectivity troubleshooting

Steps:

1. Delete all entries under Data Access > Content Manager

2. Create new Content Store, and pick "Oracle database (Advanced)"

3. Under "Database specifier," enter "[hostname]:[port]:[SID]"

3.1. hostname- Oracle database server's hostname

3.2. port- Oracle listener's port. Default is 1521

3.3. SID - Oracle DB's SID, but not service name