Error on converting CQM model to DQM model in Cognos

Question

Ive converted a CQM model to DQM without errors but when I open some (2/10) of the reports - they are giving me an error. The error: "XQE-PLN-0131 The SQL can not be generated because a circular reference is found in the sub-queries" Im not able to figure out as to why a sub query will be circular referenced when the model is converted to DQM.

I am trying to run each query and see the error occurance but still not able to find it.

Answer 1

Trying to troubleshoot sql errors when converting from a CQM to DQM dispatcher is a messy business.

If the report is small, recreate it straight from the new model, tesing along the way.

If the report is big, locating the error can be very time consuming.

I advise this,

First, in your Framework Models, make sure that your Database layer Query Subjects from Data Sources are not named exactly the same as your table names. I simply added an underscore '_' to the end of their name.

This may fix your circular reference issue.

Invariably, you are going to have some reports that still fail. I suggest you follow the trend and set up a secondary dispatcher. Let your primary dispatcher be DQM, but setup a CQM dispatcher. I did this on the same server without any problems.

While the DQM server will have all services activated, the CQM should only have the following set to True in Cognos Configuration->Environment->IBM Cognos Services:

Agent Service Batch Report Service Delivery Service Dispatcher Service Job Service Monitor Service Query Service Report Data service Report Service Relational Metadata Service

All remaining services should be set to 'False'.

In the environment settings, make sure your External Dispatcher URI, Internal Dispatcher URI and Dispatcher URI for external applications has a different Port number than your DQM install. For me DQM is the default 9300, and CQM is using 9301.

Your CQM can point to the same Notification Store, HTAS Store as your DQM.

On your Cognos Gateway, do not add your CQM dispatcher to the list of Dispatcher URI's for Gateway. This would cause requests to go to your CQM dispatcher any time the DQM is busy, which would cause many requests to fail. Requests will be routed to the CQM using Routing Rules.

In Congnos Configuration under "Dispatchers and Services" give your DQM and CQM dispatchers each a distinct 'Server Group' name.

Edit Properties on your Packages , check 'Advanced Routing', then Edit the routing sets. Here you can define new routing sets. You can publish your packages twice, once as DQM packages, and again as CQM packages (distinct name, ie. add _CQM to the name). For your DQM, add a 'Type Routing Sets' of 'DQM Reports'. For CQM, add a 'Type Routing sets' of 'CQM Reports'.

Next, go back to Cognos Administration.

From "Dispatchers and Services" -> "Specify Routing Rules" (icon at the top) Set A package Routing Rule that sends DQM Reports to the DQM Dispatchers Server Group. Set a Routing Rule that Sends CQM reports tot he CQM Dispatchers Server Group. Then Set a Routing Rule that sends ANY Package to DQM Dispatchers Group at the bottom as a Catch-All.

Now you can go into your reports, point them to the DQM package. If they work, keep them there. If they do not, point them to the CQM package, and add them to a list.

Now you can have all your reports working, and converting reports to DQM will not be holding up your migration project. Now go through your list of CQM packages and one at a time recreate them using a DQM package.

If you do this, and make sure all new reports are DQM, eventually you will be done with the CQM packages. When that glorious day comes, you can simply turn off the CQM dispatcher service and you will be 100% DQM 64 bit goodness.