Connection to AWS Redshift (from Cognos 10.2.2)

[JamesR]

Hi All,

Does anyone have any experience in creating a connection to AWS Redshift? I've followed the below, but can't get the JDBC drivers to work...

• Download JDBC 4.2 Driver from AWS
• Place .jar file in each application tier server \webapps\p2pd\WEB-INF\lib directory
• Configure connection as per video https://www.youtube.com/watch?v=GkfE4N16eEg

When I go to test the connection I keep getting an error. When I try the 4.2 version driver I get "XQE-GEN-0002 An unexpected exception occurred: JVMCFRE003 bad major version; class=com/amazon/redshift/jdbc42/Driver, offset=6"

I believe this is something to do with the jar compiled in a newer version of java?

If I try version 4.1 (as in the video) or older, I get "XQE-GEN-0002 An unexpected exception occurred: Failed to load Driver class. This error may be caused by all-in-one JAR packing. Please try again with the lean package: java.io.FileNotFoundException: D:\Program%20Files\IBM\cognos\c10_64\webapps\p2pd\WEB-INF\lib\RedshiftJDBC41.1.2.8.jar (The system cannot find the path specified)"

The files are definitely located in the right place, so I'm puzzled as to what the issue could be with version 4.1.

Any pointers greatly appreciated!

Thanks,

James

[junkcrusader]

Hi,

We have been using the JDBC41 driver, version 1.2.1.1001 successfully.  However, due to AWS certificate changes, we need to upgrade to 1.2.8.

When we switched to RedshiftJDBC41-1.2.8.1005.jar, we ran into the same problem as you. So, have done some testing ...

Believing the "all-in-one" message, I also tried RedshiftJDBC41-no-awssdk-1.2.8.1005.jar.  This didn't work either (same error).

Based on other comments in the internet, I did further testing using my SQLWorkbenchJ installation (although that was using JDBC42) and have found that neither RedshiftJDBC42-1.2.8.1005.jar or RedshiftJDBC41-no-awssdk-1.2.8.1005.jar will work if they are located in a directory containing a space in the path.  Move them to a directory without a space and they both work fine.

Applying this knowledge to Cognos, I don't yet know how to resolve this (without a full reinstall of Cognos which I don't consider an option).

As a workaround, you can use the RedshiftJDBC41-1.2.1.1001.jar driver, but from 23rd October, you'll have to disable SSL by adding ?ssl=false at the end of you connection strings.

[the6campbells]

JDBC is an API specification which JDBC driver vendors follow to create a driver.

The API has changed over time (i.e. 3.0, 4.0, 4.1, 4.2) and in turn are released as part of a new Java release.

Hence, you can use a driver written for say JDBC 3.0 within a newer JRE such as Java 8.

But, you cannot use a JDBC driver using a new JDBC API version such as 4.x in an older JRE as that will cause the JRE to throw a "bad major version" you observed.

When you download an Amazon Redshift JDBC driver, you will see that over time, Amazon has added different JDBC drivers (i.e. one for JDBC 4.0, 4.1, 4.2) etc.   You should use the 4.1 driver.

Amazon released driver updates in August/Sept 2017 starting with 1.2.7.1003 where their logic fails when an application server loads it. Amazon have been informed  that their drivers logic needs to account for this situation. You may want to contact Amazon Redshift support/product management when they expect to publish a fix for their driver so it loads correctly with IBM Websphere/Websphere Liberty Profile.

Hence, the second item you noted which you will see with 11.0.7 and 11.0.6 if you use 1.2.7.1003.

Did you open an PMR with IBM Cognos support?

[JamesR]

Hi both,

Many thanks for taking the time to reply, and apologies for the delayed response.

I eventually worked my way through the drivers and came to the same conclusion as junkcrusader and am using RedshiftJDBC41-1.2.1.1001.jar.

Initially I got a time-out error (progress!), but established this was due to my company firewall, which has now been opened.

Unfortunately I'm still not able to connect to Redshift, when I click the test button on the connection nothing happens. Eventually I'll get an internet explorer 'page cannot be displayed' etc. I believe this is to do with AWS not allowing the connection their end, although I would have expected to continue to see the time-out error instead of nothing?

Either way, it's now with AWS to confirm everything is set up their side...


Thanks,

James