Error when running Native SQL reports

[alaricb@ca.ibm.com]

My team hosts a Cognos BI server. We are in the process of upgrading from 10.1.1 to 10.2.1 fix pack 3 (and changing machines). Some reports that work on our old server fail on the new server with the following error: "RQP-DEF-0177  An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'"

We looked into the issue (link below), and the problem appears to be with SQL queries with SQL Syntax "Native"  in reports accessing multiple data sources. There is a different error when using "IBM Cognos SQL" (DPR-ERR-2056 The Report Server is not responding). When we change the SQL Syntax to "Pass- Through", they work fine. There are many reports that use Native SQL, so we would like to avoid changing them all manually.

This forum claims that Cognos support confirmed this is a logged defect in 10.2: http://www.cognoise.com/index.php?topic=20651.0

Does anyone know how we can resolve this issue?

[MFGF]

My team hosts a Cognos BI server. We are in the process of upgrading from 10.1.1 to 10.2.1 fix pack 3 (and changing machines). Some reports that work on our old server fail on the new server with the following error: "RQP-DEF-0177  An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'"

We looked into the issue (link below), and the problem appears to be with SQL queries with SQL Syntax "Native"  in reports accessing multiple data sources. There is a different error when using "IBM Cognos SQL" (DPR-ERR-2056 The Report Server is not responding). When we change the SQL Syntax to "Pass- Through", they work fine. There are many reports that use Native SQL, so we would like to avoid changing them all manually.

This forum claims that Cognos support confirmed this is a logged defect in 10.2: http://www.cognoise.com/index.php?topic=20651.0

Does anyone know how we can resolve this issue?

Hi,

Can you give us more of the error message? Is there a reference to a specific table?

What database or databases are you using? Does the new server point to the same physical databases(s) or have the database versions been upgraded too? Is your access via client libraries (CQM) or JDBC (DQM)? Does the new server have the same db client libraries and/or jdbc jar files as the old?

I'm not going to ask why you would be cranking out native SQL in reports like this - I would just make the comment that a good analogy would be buying an expensive Ferrari and pushing it everywhere you want to travel... :)

Cheers!

MF.

[alaricb@ca.ibm.com]

Hi MFGF, thanks for your reply.

Here is a screencap of the full error message.


The new server is pointing at the same databases as the old server, the database versions have not been upgraded. We are accessing via JDBC. I've tried with both the old and new jars (db2jcc.jar and db2jcc4.jar).

I just administer the server, I don't question the developers who run reports on it  :P

[MFGF]

Hi MFGF, thanks for your reply.

Here is a screencap of the full error message.


The new server is pointing at the same databases as the old server, the database versions have not been upgraded. We are accessing via JDBC. I've tried with both the old and new jars (db2jcc.jar and db2jcc4.jar).

I just administer the server, I don't question the developers who run reports on it  :P

Sorry - I'm not seeing your image in the post. Could you post up the text of the error?

Are you sure your only access is via JDBC? Is it a DB2 database? Do you have the correct license file included? Are you sure access is not via the native client? Sorry - lots of questions! :)

I know it's a silly thing to ask, but have you checked that the reports in question are actually valid? I think query parsing and validation has been improved somewhat between 10.1 and 10.2.1 - something as simple as a missing aggregation property on a measure in your report can trigger these kinds of issues. Maybe 10.1 let it slide but 10.2.1 is rather more strict?

Is the server generally accessible on the IBM intranet or is it secured and top secret? ;)

MF.

[MFGF]

Ah! Ok! I can see the image now. Weird!

The underlying error appears to be:

SQL0104N An unexpected token "END-OF-STATEMENT" was found...

which is an error coming back from DB2. It sounds like basically it is complaining about hitting the end of the statement before it expected to. Does the native SQL have line breaks in it? Could it be hitting the first of these and assuming it's reached the end of the statement?

MF.

PS it also looks like you're connectig via the DB2 native client (CLI) rather than via JDBC?

[Lynn]

Do you have a semi-colon at the end of the statement?

[alaricb@ca.ibm.com]

I've got some additional clarification:
-The Database is DB2 9.7.7 with tables, views, MQTs, etc.
-We are correctly licensed
-The database is IBM confidential.
-There is a semicolon at the end of the statement
-I was wrong earlier, our JDBC connection fails (both on old and new server) so we are connected via the native client.
-The connection settings should be identical to those on the old server, as we performed a full content store export and import, and catalogued all of the underlying databases in db2

Regarding validation, we can validate the report (in report studio) on our 10.1 server, but on 10.2 we get the attached error:

[MFGF]

I've got some additional clarification:
-The Database is DB2 9.7.7 with tables, views, MQTs, etc.
-We are correctly licensed
-The database is IBM confidential.
-There is a semicolon at the end of the statement
-I was wrong earlier, our JDBC connection fails (both on old and new server) so we are connected via the native client.
-The connection settings should be identical to those on the old server, as we performed a full content store export and import, and catalogued all of the underlying databases in db2

Regarding validation, we can validate the report (in report studio) on our 10.1 server, but on 10.2 we get the attached error:

Hi,

I don't doubt for a second you are correctly licensed - the reason for asking about the license file is that unless you have the correct file for your version of DB2 and the JDBC jar file you have, it simply doesn't work. :)

I understand you did an export/import to transfer the content, but that doesn't ensure that the DB2 client libraries are the same on the new server as the old. It's worth checking this.

Re the IBM Confidential thing, I will sametime you tomorrow to see if it's something I can get to and you can let me see :)

Cheers!

MF.

[cognosuser101]

For DB2:

Edit the ..\bin\cogdmd2.ini file (for DB2)
and uncomment
 ;Native_SQL_In_CTE="KEEP"

and change to

Native_SQL_In_CTE="PT"

This should work

[alaricb@ca.ibm.com]

Great, that worked.

Thanks cognosuser101!

Thanks to everyone else as well.