Migration from Cognos 10.2.2(CQM) to Cognos Analytics(DQM)

[highwingers]

We are migration from Cognos BI to Analytics, and also going form CQM to DQM, database is DB2

I am getting an error from one of the queries which has embedded SQL the error is: XQE-DAT-001 Data source error adapter error: com.ibm.db2.jcc.am.SqlsyntaxErrorException: ILLEGAL SYMBOL "_".

Does anyone know how to resolve this issue from report studio, any other information would be helpful also

[cognostechie]

How did you switch from CQM to DQM?

Not sure about the current versions but when DQM was introduced, all CQM models and reports had to be re-built. Without that, some of them worked bt lot of them had errors. It was declared that DQM requires the content to be re-built, not migrated or upgraded.

[highwingers]

Thanks for replying cognostechie
I believe our architect is doing it from FM

[MFGF]

Hi,

The fact that DB2 is complaining about an illegal symbol "_" makes me wonder if your model has some manually coded SQL that isn't exactly legal? Normally each data query subject would access its underlying table using SELECT * from < table name > and this would leave the query engine free to minimize the query as appropriate. You can, however, code any SQL you like in a query subject. I have a sneaky suspicion this might be the case in your model, and the query isn't coded properly. I'm just guessing, though.

Has your architect run a full validation of the DQM model in Framework Manager? Are there any errors or warnings from the validation? Anything in the XQE logs?

Cheers!

MF.

[highwingers]

Hi MF, thank for replying!
I dont believe our architect run a full validation of the DQM model in Framework Manager. But you are correct the issue was coming from manually coded SQL. I found a way to get the SQL to validate by removing the " ; " from the WITH UR; at the end of the SQL that was written. Took a lot of time to figure that the issue what coming from that.

But I still dont know why that should be an issue

[MFGF]

Hi MF, thank for replying!
I dont believe our architect run a full validation of the DQM model in Framework Manager. But you are correct the issue was coming from manually coded SQL. I found a way to get the SQL to validate by removing the " ; " from the WITH UR; at the end of the SQL that was written. Took a lot of time to figure that the issue what coming from that.

But I still dont know why that should be an issue

The old CQE engine was not very strict in the way it enforced legal syntax. The DQM engine is much stricter, and will (quite rightly) complain when the syntax is not as it should be. You wouldn't usually include a semi-colon at the end of any SQL construct in your model. There are different levels of intervention the query engine will do based on whether you specify the SQL is Cognos SQL, Native SQL or Pass-thru SQL. If the latter, it will just hand off the query to the database without trying to validate it for syntax or to make sure it contains a projection list.

Cheers!

MF.