JDBC data source receiving Errors

[adam_mc]

I have successfully configured a JDBC MySql connection in Congos Administration > Configuration.
And, validated connection.

However, when I try and use this as a source in Framework Manager I am getting the following errors when testing.

QE-DEF-0285 The logon failed.
QE-DEF-0325 The logon failed for the following reason:
RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
<Connection Name>

UDA-SQL-0432 Unable to locate the gateway "cogudajdbc".


Any thoughts will be gratefully appreciated.
Thanks in advance,
Adam.

[MFGF]

I have successfully configured a JDBC MySql connection in Congos Administration > Configuration.
And, validated connection.

However, when I try and use this as a source in Framework Manager I am getting the following errors when testing.

QE-DEF-0285 The logon failed.
QE-DEF-0325 The logon failed for the following reason:
RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
<Connection Name>

UDA-SQL-0432 Unable to locate the gateway "cogudajdbc".


Any thoughts will be gratefully appreciated.
Thanks in advance,
Adam.

Hi,

Is your instance a multi-server install? Sounds similar to the below:

http://www.cognoise.com/index.php?topic=25830.0

If you're using Cognos Analytics (rather than Cognos 10) the JDBC driver needs to be in the Drivers folder on the dispatcher. In Cognos 10 it went in webapps\p2pd\web-inf\lib

Cheers!

MF.

[Karl_LB]

I'm getting the same error in Framework Manager for Cognos 11.0.7 with an Informix 4.10 JDBC driver.
- The Cognos Web interface Administration > Configuration Data Source test works just like Adam states.

The driver is in the correct location.

I'm using a pre-existing Project file with the Data Source already defined and even publishing packages works fine using DQM

Only Testing the Data Source in Framework Manager and running update scripts gives me the error Adam gets.

Anyone have any luck yet?  Next step IBM PMR submission.

[adam_mc]

Hi Karl...

I was never able to resolve this issue, and due to time constraints, I ended up having to resort to creating the connection as ODBC instead.

However, I am now experiencing exactly the same scenario when attempting to connect to an Amazon Redshift database in Framework Manager.
Content Manager source defined in Cognos Administration is connecting without issue.

The JDBC connection string is as follows:

     jdbc:redshift://<server>:5439/<database>?tcpKeepAlive=true

But, in FM I get the following messages below.
I am not a Redshift expert (so my terminology may be wrong), but it appears as if the database defined in my connection contains many schemas.
However, there does not seem to be a way to define the schema within my connection string.
My thought here is that the error indicates "Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
<Connection Name>" and I don't know if this means I need to define the schema somewhere?

UPDATE:
My FM model is CQM.
If I create a new test model in DQM and attempt to connect to my Redshift source, everything seems to work as expected.
Is there any reason for this behavior?
Changing my entire model to DQM would be a significant amount of work.

If anyone has any thoughts they would be gratefully appreciated.
Thanks in advance,
Adam.

When I attempt to test the Data Source in FM:

     QFS-ERR-0138 The data source 'JDBC' is not supported by any provider.

When I attempt to create a query subject and connect to the Content Manager source in FM:

     QE-DEF-0285 The logon failed.
     QE-DEF-0325 The logon failed for the following reason:
     RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
      Amazon Redshift

     UDA-SQL-0432 Unable to locate the gateway "cogudajdbc".







 

[the6campbells]

Connections defined with the JDBC connection type introduced in 10.2.0 cannot be used by CQM.

Dynamic Query requires a JDBC connection type for the following data sources:

IBM BigSQL
Informix
DB2 iSeries JT400
HPE Vertica
Pivotal Greenplum and HDB
Apache Hive
Amazon Redshift and Amazon Aurora MySQL
Snowflake
Cloudera Impala
MySQL
Postgres
MemSQL
MariaDB
SAP Hana, ASE and ASE IQ
Google BigQuery and Google Cloud SQL MySQL
Presto and Teradata Presto
Denodo
Cisco Information Server
Exasol
Mongo BI Connector

[adam_mc]

Thanks the6campbells....

In playing around, it seems as if changing my model from CQM to DQM is actually pretty straightforward.
I just had to define JDBC connections for all the previously defined ODBC connections, change the connection type on the Data Source to be JDBC, and then change the Project level variable.
Time consuming, but much easier than I thought it was going to be!

Everything converted over except one query subject (which it gave me a great notification) and I am sure I can research that and fix.
I have a sandbox to do all this in first before migrating to Production.

However, other than that, what other concerns, specific known issues, and suggested testing/validation would be anyone recommended?

Thanks in advance,
Adam.

[the6campbells]

In terms of the data sources you mentioned in your post (Redshift and MySQL).

While CQM uses the ODBC API to several vendors it has integrated with (Vertica, Teradata, IQ, Postgres, MySQL, ...) Redshift wasn't one of them.

Hence, if you were transitioning a CQM/ODBC application to DQM/JDBC with Redshift, you would observe a significant difference in terms of the queries pushed to Redshift and in turn reduction of local query execution particularly with large result sets etc.

Should you consider switching to MariaDB or MemSQL from MySQL then you would see DQM leveraging improved SQL syntax they offer today which MySQL is planning to offer in the near future.

[vomaringo]

i got the same error with FM 11.0.11 to.
My jdbc db was on a VM in a different network (NAT).
I have switched to bridged network and it works fine.
It seems that jdbc need a special configuration to cross network.

[MFGF]

Hi Karl...

I was never able to resolve this issue, and due to time constraints, I ended up having to resort to creating the connection as ODBC instead.

However, I am now experiencing exactly the same scenario when attempting to connect to an Amazon Redshift database in Framework Manager.
Content Manager source defined in Cognos Administration is connecting without issue.

The JDBC connection string is as follows:

     jdbc:redshift://<server>:5439/<database>?tcpKeepAlive=true

But, in FM I get the following messages below.
I am not a Redshift expert (so my terminology may be wrong), but it appears as if the database defined in my connection contains many schemas.
However, there does not seem to be a way to define the schema within my connection string.
My thought here is that the error indicates "Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
<Connection Name>" and I don't know if this means I need to define the schema somewhere?

UPDATE:
My FM model is CQM.
If I create a new test model in DQM and attempt to connect to my Redshift source, everything seems to work as expected.
Is there any reason for this behavior?
Changing my entire model to DQM would be a significant amount of work.

If anyone has any thoughts they would be gratefully appreciated.
Thanks in advance,
Adam.

When I attempt to test the Data Source in FM:

     QFS-ERR-0138 The data source 'JDBC' is not supported by any provider.

When I attempt to create a query subject and connect to the Content Manager source in FM:

     QE-DEF-0285 The logon failed.
     QE-DEF-0325 The logon failed for the following reason:
     RQP-DEF-0068 Unable to connect to at least one database during a multi-database attach to 1 database(s) in:
      Amazon Redshift

     UDA-SQL-0432 Unable to locate the gateway "cogudajdbc".

Hi Adam,

Any JDBC connection is only ever going to be available in Dynamic Query Mode. Compatible Query is an entirely different engine. The last error you posted - UDA-SQL-0432 Unable to locate the gateway "cogudajdbc" is CQE getting very confused on how to connect to the data. Anything coming from UDA is Compatible Query - which doesn't support JDBC.

You're going to need to convert your existing FM model to DQM if you want to be able to use the JDBC connection.

Probably not what you wanted to hear, sorry.

MF.