Cognos 10.2 Upgrade Issue - SQL based queries

[Lynn]

Hi all,
My client has just upgraded the development environment to 10.2. There are a number of reports that have queries with hard coded SQL in them. Yes, we all know this is a bad idea and they are in the process of rectifying this, but it takes time.

In the meantime, they find that previously working reports containing SQL now fail with the error below. These are set to Native SQL Syntax and they run properly directly against the database. This is occurring for Oracle and for DB2 databases. Changing the syntax to pass through allows them to run, but I don't think this is really a solution to the problem. Native syntax should be supported. They are opening a trouble ticket but I wondered if anyone else who upgraded to 10.2 has run into this problem.

Thanks!

UDA-SQL-0107 A general exception has occurred during the operation "prepare".[IBM][CLI Driver][AS] SQL0104N An unexpected token "<END-OF-STATEMENT>" was found following "". Expected tokens may include: ") FETCH ORDER UNION EXCEPT".

[CognosPaul]

That error is coming from the datasource. So Cognos is attempting to run the query in the db. Can you trace the query? The problem is dependent on how it's malformed.

[Lynn]

Cognos support confirmed that this is a logged defect in 10.2.

[CognosPaul]

Well, that's not fun. Did they give more details about what the defect is? A specific way the SQL is written, or is all hand-written SQL in 10.2 problematic?

[norkos]

Native SQL is also problematic if you use Cognos 10.1.1 on IBM DB2. You have to uncheck the WITH clause option under edit governor menu item or you can use the native SQL only in limited local mode.

So the IBM guys solved an issue and created a new one   :o

[Lynn]

I did not handle the trouble ticket phone call, but my understanding is that it was all hand-coded SQL affected rather than isolated to a specific syntax.

Apparently it was logged as a bug just the day before this particular ticket. They have opted to roll back the upgrade here until a fix is available.

[RubenvdLinden]

Do you know anything about a hotfix, or do we have to wait for fixpack 1?

[Lynn]

I have not heard anything yet but will post back when I do.

[MFGF]

Ouch! Not good!! I know IBM best practice discourages the use of hand-crafted native SQL queries, but I think they have shown a little too much fervor in this latest push to get you to comply :)

I hope it gets fixed soon, Lynn!!

MF.