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

Oracle sqlplus Login Failure Handling in Shell Script

 Product: Oracle RDBS

Command: sqlplus, shell script

Overview

I would like to document how to create a shell script that will report whether the username credential that stores in a file can successfully login to Oracle DB, or filed.

Without specifying any sqlplus parameter, sqlplus will always return exit code of 0 to the shell script.  I have seen people uses text processing utility to search for the login failure command to detect login failure, but they should use parameter "/l" to do that.

Moreover, in a shell script, I will suggest to turn off the login banner to make the output less verbose by using parameter "/s"

Instruction

Suggested command is "sqlplus /s /l username@TNS"

To automate the password, my suggested command is "echo [password] | sqlplus /s /l username@TNS"

Later, the shell script can uses the OS return code's build-in parameter $? with following meaning:

• 0 - login successful
• 1 - login failed

SSL Setup - Java https, smtp, MS SQL Server

Technology: Java using SSL Certificate, or TLS1.2 - 1.3 encryption

Product: web server (https), SMTP email, force encryption MS SQL Server

There are several steps involve in setup SSL for various servers besides following standard

1. Create new SSL certificate for the virtual hostname used by the applications

2. Import the SSL certificate to the application servers

2.1. For Java, you need to import into Java keystore. For OpenJDK or Oracle Java (JRE), it is located in [java_jre_home]\lib\security, or [java_jdk_home\jre\lib\security.

2.2. For Linux or Windows binary programs, you need to import into OS. For RHEL, the SSL certificate file will be /etc/ssl/certs

3. Configures the application server to enable SSL, and indirectly specified the SSL certificate to use

4. Configures the application client to enable SSL, and optionally

4.1. Java - optionally specifies SSL keystore if not using default

Now, let's pick an application that leverages multiple technologies, and gives real picture what needs to be configured to enable SSL

Cognos Analytics report server

This reporting server has following functionality (typical, but could be more) that will has SSL encryption:

1. Web server - it is severing the report in html, so it is a web server

2. Web client - it is able to forward its traffic to a customer facing web server (IIS, Apache httpd, IBM httpd), so for this communication, it is a web client

3. SMTP email client - its interactive report and scheduled report can be sent out as email attachment (pdf, html)

4. DB client - it needs a "Content Store" DB to stores user credential and report template. It can has additional audit DB to store additional application data for audit functionality.  Any DB that it is querying to display the reports are all act as a DB client

5. Authentication client - OOTB LDAP (Windows only) server, custom LDAP, Okta, IBM OpenConnect, etc authentication that it supports, it is acting as client

Therefore, for above 5 typical server & client functionality, SSL can be enabled and encrypts the traffic between remote servers and clients.

For all the above functionality, you need to understand that the product is a pure Java J2EE application.  Therefore, all the SSL configuration will follow Java (standard) and IBM documentation (application specific).

Several and multiple SSL certificates involve in this encryption setup:

1. Java keystore located in [cognos_home]\configuration\certs\CAMKeystore (default password NoPassWordSet)

1.1. CAMKeystore keeps the SSL cert for web/https access for Cognos Analytics server. If there are multiple virtual hostname (such as internal access, intranet access, internet access), then there are multiple SSL certs (unless the SSL cert contains multiple virtual hostname)
1.2. CAMKeystore keeps the SSL cert for Cognos Analytics server's databases access, e.g. Content DB, audit DB, data store DB. Example is MS SQL Server force encryption. Each remote DB server uses different SSL cert, and CA root
1.3. CAMKeystore keeps the SSL cert for SMTP server, which used for email of report, or email notification functionality. Example is smtp protocol with STARTTLS that enforce SSL, or smtps protocol. Typically only 1 SMTP server, so only 1 SSL cert
1.4. CAMKeystore keeps the SSL cert for client side https with remote customer users facing web server
1.5. CAMKeystore keeps the SSL cert for remote authentication server, e.g. LDAP , ActiveDirectory, and SSO. Each authentication server has its own SSL cert

Procedure:

1. Determine the root certificate for all 5 components above.  Each server will have its own SSL cert, and might not use the same CA root certificate

2. Verify in [cognos_home]\configuration\certs\CAMKeystore that the CA root certificate is already there, and will be recognize

2.1. Follows Cognos Analytics doc: DLS_SSL_CertImportTool.bat (Windows), or sh (Linux) - https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=server-enabling-secure-tls-connection-your-email

3. Even if it is in CAMKeystore, verify that it is not expired

4. If not there, or expired, then export the CA root cert from remote server

5. Import into CAMKeystore, which is the IBM Java keystore

6. For DB used in data store, import the SSL cert into [cognos_home]\ibm-jre\jre\lib\security\cacerts

6. Follows various IBM Cognos Analytics document to enable SSL encryption

6.1. For Cognos Analytics 12.0.0 SMTP - https://www.ibm.com/docs/en/cognos-analytics/12.0.0?topic=server-enabling-secure-tls-connection-your-email

6.2. For Cognos Analytics 11.2.0 Content DB, audit DB, logging DB as database client - https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=communications-enabling-ssl-db2-informix-databases

6.3. For data store as DB client - https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=communications-enabling-ssl-db2-informix-databases

Tools

There are various DOS utilities that can troubleshoot SSL communication, but none for Java.  Some sample Java programs are available, but you have to compile, as well as familiar with SSL logging parameters that you have to manually specify.

Tools for

1. Windows: openssl

2. UNIX, Linux - openssl, curl

3. Java SMTP - Oracle javamail-samples.zip that based on Oracle javax.mail.jar library that you need to download. It contains sample SMTP with STARTTLS sample code that you need to modify the Java program to specify SMTP port (default port 25)

4. Java MS SQL Server client - Microsoft provided sample https://learn.microsoft.com/en-us/sql/connect/jdbc/connection-url-sample?view=sql-server-ver16

Tool - opemnssl for SMTP

For SMTP that uses STARTTLS to enable encryption, uses following command to troubleshoot SSL issue

openssl s_client -starttls smtp -crlf -connect [remote email server hostname]:[smtp port]

This relies on OS SSL certificate store, so not applicable for Java SMTP troubleshooting

Tool - opemnssl for SMTPS

Same as above, but uses SMTPS SSL encryption, not SMTP + STARTTLS

openssl s_client  smtps -crlf -connect [remote email server hostname]:[smtp port]

Tool - curl for SMTP

curl is preinstalled in Linux.  It can login to SMTP email server, and send out email.  Therefore, it is useful to ensure the remote SMTP server is properly configured to allow your application server to send out email. This uses SMTP + STARTTLS protocol

curl -v  smtp://email-smtp.us-east-1.amazonaws.com:587  --user "login-user-name:password-to-be-filled" --mail-from "[username that allow to be used to send out" --mail-rcpt "email addr to receive the email"  -T emailcontent.txt --ssl

Tool - curl for SMTPS

Same as above, but uses SMTPS instead of SMTP + STARTTLS

curl -v  smtps://email-smtp.us-east-1.amazonaws.com:587  --user "login-user-name:password-to-be-filled" --mail-from "[username that allow to be used to send out" --mail-rcpt "email addr to receive the email"  -T emailcontent.txt

Tool - Test-NetConnection

PowerShell Test-NetConnection can be used to verify the application server machine can reach remote SMTP/DB/LDAP/Okta/SSO/web server.  This will give you a quick indication that the firewall, router, DNS, AWS Security Group, etc are configured to allow them to talk before you perform application side of testing, such as openssl, or curl above

test-netconnection [remote server hostname] -port [remote server port]

Example for MS SQL Server: test-netconnection [remote MS SQL Server virtual hostname] -port 1433

Example for web server: test-netconnection [remote web server virtual hostname] -port 443

Example for Oracle DB: test-netconnection [remote Oracle virtual hostname] -port 1521

Tool - Java

Enable following SSL debug parameter when running sample MS SQL Server, Oracle sample JavaMail program, etc

Example for using Oracle JavaMail sample called smtpsend.java: java  -cp .;..\javax.mail.jar;..\javax.activation-api-1.2.0.jar  -Djavax.net.debug=ssl:handshake smtpsend

SAP IPS - setup.exe doesn't recognize Product Key

Product: SAP IPS
Version: 4.1 - 4.2

Symptom

Run setup.exe, and enter a valid product key. Installer returns following error

Error
An error has occurred.

The product key is not valid. INS00140

Checked installer log in /tmp/yyyy.mm.dd.hh24.mi.ss/setupengine.log for key validation failure. It is showing following

20:58:18.743 RawArgFormatter - property not set yet: shared.library.keycode.props-4.0-core$ProductKey

20:58:25.692 [validate_ProductKey]argument:  -keycode ********  -version 142 -property BOE.EnableBOE

20:58:26.056 [run_external_executable] cmdLine: cd "/opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions/"; /opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions//isKeycodeValid  -keycode ********  -version 142 -property BOE.EnableBOE

20:58:26.056 [validate_ProductKey]keyTypes [BOE.EnableBOE:142] is bad

Solution

For for full path of program "isKeycodeValid" shown in above log, then run following

chmod u+x <full path from log above>/isKeycodeValid

You should be able to manually test this is success

cd "/opt/download/sap/bods42sp14pl21/ips42sp9pl7/dunit/product.minibip-4.0-core-32/actions/"

isKeycodeValid  -keycode <non case sensitive product key>  -version 142 -property BOE.EnableBOE

Cognos Analytics 11.1.x - 11.2.x - Installer failed with JRE libraries missing or not compatible

Product: IBM Cognos Analytics
Version: 11.1.0 - 11.2.2
OS: Linux, AIX, PowerPC, all UNIX distributions
Related IBM KB: https://www.ibm.com/support/pages/jre-libraries-are-missing-or-not-compatible

Problem

When executing  the Cognos installer on a Linux,Aix server the installer fails to run the following error:

Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...

Launching installer...

JRE libraries are missing or not compatible....
Exiting...."

Symptom

The installer consistently fail to run with below false alarm:

• JRE libraries are missing - not applicable as the installer is downloaded many times from IBM as a valid zip file, as well as across many different version of Cognos from 11.1.0 - 11.2.2
• JRE not compatible - not possible, as the installer bundled IBM Java in /tmp/install.dir.[nnnnn]/Linux/resource/jre/bin

(login as UNIX cognos user that created to own the application)

$ echo $TMP

[empty]

$ grep noexec /etc/fstab

UUID=c7aac85c-4dfd-4e40-bd6c-bf57c8284c0c       /       xfs     defaults,noexec

$ echo "echo 012345" > /tmp/test_exec.sh

$ sh /tmp/tst_exec.sh

/tmp/tst_exec.sh: Permission denied

$ /tmp/install.dir.[nnnnn]/Linux/resource/jre/bin/java -version

/tmp/install.dir.[nnnnn]/Linux/resource/jre/bin/java: : Permission denied

Cause #1

/tmp mount point is mounted without execute permission for security reason (checks /etc/fstab with value noexec).

It is confirmed by tst_exec.sh that created above that it failed to run

Cause #2

A wrong installer for other OS is downloaded.  For Intel Linux, you should expect following output

$ file analytics-installer-3.6.46-linuxx86.bin

analytics-installer-3.6.46-linuxx86.bin: data

Following is expected for Intel x86 64-bit Linux

$ file /tmp/install.dir.*/Linux/resource/jre/bin/java

/tmp/install.dir.15939/Linux/resource/jre/bin/java: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, not stripped

Following is for PowerPC Linux, not Intel x86

$ file /tmp/install.dir.*/Linux/resource/jre/bin/java

/tmp/install.dir.15939/Linux/resource/jre/bin/java: ELF 64-bit LSB executable, 64-bit PowerPC or cisco 7500, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=c07e00748dd7b5ca30b6feb0e2d7c911cec024d7, not stripped

Resolving the Problem

Option #1: Identify a directory that user is allow to run the installer program, i.e. java.  Specify Cognos installer OS environment variable IATEMPDIR to that location before execute the installer

$ export IATEMPDIR=~/tmp

$ analytics-installer-3.6.46-linuxx86.bin

Option #2: Coordinate with Linux SA to remove "noexec" from /etc/fstab, or provides another mount point that doesn't contain "noexec" security option. Assumes the mount point is called /tmp2, then set Cognos installer OS environment variable IATEMPDIR to that mount point before execute the installer

$ export IATEMPDIR=/tmp2

$ analytics-installer-3.6.46-linuxx86.bin

Oracle Timezone: Clarification of 2 Timezone Setting

Product: Oracle Database
Version: All
OS: All

History

Many years ago, Oracle database relies on OS clock, as well as no date column type that can store timezone setting (similar to MS SQL Server today).

Timezone aware column type, TIMESTAMP WITH TIME ZONE, introduced much later, but many DBA and developers don't have a good knowledge about OS timezone.

Date DB Function

In Oracle 12c to 21c, DB has following 4 date functions, which has different behavior on timezone

• sysdate - Uses OS timezone
• E.g. select sysdate from dual;
• E.g. insert into [user table] values (sysdate);
• systimestamp - Uses OS timezone
• E.g. select systimestamp from dual;
• E.g. insert into [user table] values (systimestamp);
• current_date - uses DB session timezone, similar to sysdate
• E.g. select sessiontimezone from dual;
• E.g. insert into [user table] values (sessiontimezone);
• LOCALTIMESTAMP
•  - uses DB session timezone, similar to systimestamp
• E.g. select sessiontimezone from dual;
• E.g. insert into [user table] values (sessiontimezone);

Timezone Configuration At DB Level

I won't discuss OS timezone configuration, as this post is mainly clarifying DB level timezone setting that confused many DBA or developers.

In above DB functions, the DB session's timezone depends on sessiontimezone. Default is DB timezone, which set by following:

• ALTER DATABASE SET TIME_ZONE = 'US/New_York';
• CREATE DATABASE ..... TIME_ZONE = 'US/New_York';

At user level, you can configure it using

• ALTER SESSION SET TIME_ZONE='US/Eastern';

You can check its current value by using:

• sessiontimezone - uses DB session timezone, similar to sysdate
• E.g. select sessiontimezone from dual;

Troubleshooting Highlight

When verifying it, always display the sessiontimezone together with 1 systimestamp, and 1 current_date, e.g.

select sessiontimezone , sysdate, current_date from dual;

This will ensure that you cover both OS and DB timezone, and displaying DB timezone value

Note: systimestamp will display OS timezone value

Ref: Oracle 19c DB Time Functions: https://docs.oracle.com/en/database/oracle/oracle-database/19/nlspg/datetime-data-types-and-time-zone-support.html

Oracle No more data to read from socket

Product: Oracle Database
Version: All

I often found from StackOverflow.com that people are posting following error from their application:

No more data to read from socket

The application could be Java, Springframework, etc, and they often blame the application problem, as the error is captures by the application, e.g. Java application/daemon.

Often people will suggest possible cause, or possible resolution.  Some people will even suggest not related resolution which confused the entire IT community.

The full Oracle error is below:

ORA-17410 No more data to read from socket

There are several possible causes, but many people don't put when each is applicable.  The issue is 100% not due to the application, but problem with the infrastructure (network) or Oracle database:

Cause #1: Intermittent Network issue

Behavior #1: Application will fail with this error intermittently

Behavior #2: Point of failure will be random, and won't be in the same table/index/SELECT SQL, as network failure is intermittent

Behavior #3: Program should failed when pull data into the application, such as SELECT, but should not fail with CREATE TABLE, CREATE INDEX, DELETE, DROP, ALTER

Behavior #4: Any application will be affected by intermittent network issue, instead of just 1 application

Behavior #5: Perform a few GB size file transfer between the application and remote DB server to simulate intermittent network issue. The duration must be longer than the SQL execution time. E.g. if it is running a SELECT that take 5 min (then failed), then the file transfer time must be more than 5 min, such as 10 min. So the test is not actually based on file size, but file x'fer time that last as long as the failed SQL

Problem Simulation: Simulate a 5 - 30 min long file transfer to confirm the issue is network related. The source and target server used to simulate file transfer must include the application server with the remote DB server

Workaround: Once confirmed, you can move the DB and application into the same machine to eliminate network issue.  The network admin can take longer time to provide a permanent workaround

Cause #2: Intermittent DB Failure

Behavior #1: Application could fail with this error randomly or consistently

Behavior #2: Oracle alert log will contain more detail error message

Behavior #3: Oracle alert log could throw ORA-00600 that DBA needs to raise ticket with Oracle Support due to DB patch/bug

Behavior #4: Even putting both application and Oracle database into the same machine, this error will continue to exists intermittently

Problem Simulation: Capture the failed SQL, and run it manually using SQL Developer or sqlplus.  You might need to create a shell script (PowerShell for Windows) to run it few 1000s time and last for 1 hr to simulate the error

Workaround #1: If it is ORA-00600 Oracle error, then try it with higher or lower Oracle database version as temporary workaround. There are potential Oracle bug that affect specific version and patch.

Workaround #2: There could be other OS issue that affected the Oracle database. Move Oracle database to another different machine (OS)

Workaround #3: Check RAM and storage to ensure no low storage, or extremely high RAM usage when the application hitting the error

Cause #3: Wrong JDBC driver

Behavior #1: Java application consistently failed and not intermittent

Problem Simulation: Create a simple Java program and run the same SQL by using the exact JDBC driver.  This should consistently failed

Workaround: Replace the Oracle JDBC driver with Oracle's

Cause #4: Special data or table structure that is not supported

Behavior #1: Application is creating table, or inserting data that is more advance, e.g. Spatial.  The program will consistently failed for the same SQL

Behavior #2: This is not intermittent.  If you think it is intermittent, then capture all the of failed SQL, and look for the pattern.  They must use some special data type that is not varchar2, number, date, long, clob, blob, function, partition

Problem Simulation: Capture the SQL, and create a simple Java program to run the same SQL. The Java program will consistently fail

Workaround #1: Rewrite the Java program on this SQL.  It could be wrong Java object that is not compatible

Workaround #2: Very old JDBC driver that doesn't support that new feature can be another cause.  Replace with newer Oracle JDBC driver

Cause #5: Firewall Timeout - idle connection clearing

Behavior #1: Some enterprise firewall can forcefully close idle connection, and leads to long running SQL's DB connection will dropped by firewall server after it hit its idle timeout value

Behavior #2: Find out the time between connection dropped and SQL is fired.  The time is constant, such as 30 sec

Workaround #1: Network admin to review the firewall rule, and exclude TCP port 1521 connection to & from Oracle database server

Workaround: Replace the Oracle JDBC driver with Oracle's

Cause #6: Router Timeout - idle connection clearing

Behavior #1: Some enterprise router can forcefully close idle connection, and leads to long running SQL's DB connection will dropped by firewall server after it hit its idle timeout value

Behavior #2: Find out the time between connection dropped and SQL is fired.  The time is constant, such as 30 sec

Workaround #1: Network admin to review the router rule, and exclude TCP port 1521 connection to & from Oracle database server