Issue with Dynamic Query Mode

[sthabinash]

The option to use Dynamic Query Mode is presented twice. The first one while creating a new project in FrameworkManager and the next while publishing the package.
If we create a project in FM enabling the 'dqm' option , then while trying to validate a relation between two tables (when the table have columns with datatypes bigint and decimal), we get error ::
"" XQE-DAT-0004 A decimal type with precision 1 and scale 4 is invalid. "
BUT ,
when we dont enable the dqm at first and enable it only when the package is published then everything runs fine !!

[MMcBride]

If you create a FM package based on DQM you are forcing a JDBC connection 100% of the time.
You aren't using ODBC or native client but JDBC drivers.

If you create a normal FM package you are using ODBC or native client - and when you publish the package you select DQM which then forces the package to use JDBC drivers

The error you are having is related to the JDBC drivers you have loaded, this is why your second option works.
When Framework Manager or Transformer (both work the same for right now at least...) is run you are using a local DMBC client.

I know this is sort of confusing so let me toss in an example.

My primary Cognos server is an AIX server, I run an ODBC client on my AIX server.
Framework Manager runs on a Windows Server (my transformer cube build server because building cubes in AIX is a nightmare... sorry different topic...)
On this windows server even though FM is plugged into my AIX server I must have a local ODBC client set up with an identical name.
So my AIX environment has an ODBC source of "Database_01" - when I run reports it uses this connection
When I build a Transformer cube or Framework Manager Model on my windows server I need a Windows ODBC setting of "Database_01" as well or it simply will not run.

I understand this explanation doesn't "solve" your problem, I just wanted to shed a little light on what the different options when using DQM really mean. Hopefully this will help you troubleshoot the issue.

on a side note, we have used DQM for relational reporting and it is broken.
Using it for a Multidimensional datasource seems to work perfectly fine, even using DMR DQM works pretty good, but forcing DQM over a relational package without the Multi-dimensional wraps is chaotic at best

As a result of this I recommend creating a Compatible mode model - building the model the way you want, creating a DMR model and then publishing the package with DQM enabled for that package.

We even had issues creating a DQM package (with the sole desire of creating a DMR model) and when a table was changed (a column was added) we could not update the model, it simply wouldn't refresh the database objects - even deleting them and reimporting them we kept getting "model is out of date" error messages.
IBM recommended we continue to use CQM models and publish DQM packages as needed.
Our datasource for this issue was IBM DB2 10.1 on Cognos 10.1.1