Data Module for Dashboard always fails - QueryService 'Unknown'?

[Eric.Seitz]

Hi all!

This is my first post here, sorry if this isn't the right spot.

Briefly: We're having trouble setting up data modules for our Dashboard project. We want to pull in data from a MSSQL source and while we can connect to the server and it displays the table and column headings no data gets pulled through. We keep getting the same error:

Could not validate module: MSR-GEN-0002 Error: "DPR-ERR-2002 Unable to execute the request because there were no connections to the process available within the configured time limit."

We've been troubleshooting this for about a week now. We've tried checking our connection to the server, restarting Cognos, trying different data sources but everything errors out.

The only thing I've been able to find is the QueryService is listed as "unknown" in the Admin console and I'm unable to stop/start it manually.
We checked our Cognos configuration and the QueryService is set to enabled. We've tried toggling it off/on with no luck.

I don't suppose anyone has any recommendations? This has been a pain in the rear!

Thanks all

[cognostechie]

Have you tried creating a Data Source and importing a table in Framework Manager? I am wondering if the database has some kind of security preventing another tool to read the data.

[Eric.Seitz]

I haven't tried framework manager but I did try uploading a .xlsx file to make a data module and it gets the same DPR-ERR-2002 error.

[MFGF]

Hi all!

This is my first post here, sorry if this isn't the right spot.

Briefly: We're having trouble setting up data modules for our Dashboard project. We want to pull in data from a MSSQL source and while we can connect to the server and it displays the table and column headings no data gets pulled through. We keep getting the same error:

Could not validate module: MSR-GEN-0002 Error: "DPR-ERR-2002 Unable to execute the request because there were no connections to the process available within the configured time limit."

We've been troubleshooting this for about a week now. We've tried checking our connection to the server, restarting Cognos, trying different data sources but everything errors out.

The only thing I've been able to find is the QueryService is listed as "unknown" in the Admin console and I'm unable to stop/start it manually.
We checked our Cognos configuration and the QueryService is set to enabled. We've tried toggling it off/on with no luck.

I don't suppose anyone has any recommendations? This has been a pain in the rear!

Thanks all

Hi,

The Query Service is the culprit here - it is required for any data source connections using Dynamic Query (which is the query engine used for anything you create in a Data Module).

How is the Query Service configured in terms of memory? If you're not sure where to look to find out, you'll need to go into the Admin Console and into the properties of the Query Service (You'll find it under System) to see the initial and maximum heap size settings. If you haven't touched these, they will both likely be set to 1Gb, which isn't enough. The bare minimum IBM recommends is a 4Gb initial heap size and an 8Gb maximum heap size. Depending on usage, you may need to tweak these higher too - if you have enough free memory on your Cognos server(s) to accommodate. Here's the official IBM recommendation:

https://www.ibm.com/support/pages/node/293851

It's also possible you may be running into this issue:

https://www.ibm.com/support/pages/dpr-err-2002-unable-execute-request-because-there-were-no-connections-process-available-within-configured-timeout

Once you get to the bottom of why your Query Service is misbehaving, fixing this should mean you can start accessing data.

Cheers!

MF.

[Eric.Seitz]

Hi,

The Query Service is the culprit here - it is required for any data source connections using Dynamic Query (which is the query engine used for anything you create in a Data Module).

How is the Query Service configured in terms of memory? If you're not sure where to look to find out, you'll need to go into the Admin Console and into the properties of the Query Service (You'll find it under System) to see the initial and maximum heap size settings. If you haven't touched these, they will both likely be set to 1Gb, which isn't enough. The bare minimum IBM recommends is a 4Gb initial heap size and an 8Gb maximum heap size. Depending on usage, you may need to tweak these higher too - if you have enough free memory on your Cognos server(s) to accommodate. Here's the official IBM recommendation:

https://www.ibm.com/support/pages/node/293851

It's also possible you may be running into this issue:

https://www.ibm.com/support/pages/dpr-err-2002-unable-execute-request-because-there-were-no-connections-process-available-within-configured-timeout

Once you get to the bottom of why your Query Service is misbehaving, fixing this should mean you can start accessing data.

Cheers!

MF.

Hi MFGF!

Great suggestions, we're looking into the second link you mentioned now.

As for JVM heap sizes we did adjust them prior to posting.
Initial: 4096MB
Limit: 32768MB

We have plenty of room memory wise on our servers, I'd be surprised if that was the issue.

EDIT:

No luck on the second link, we removed the folder content like it's listed but no effect. QueryService is still 'Unknown' and the error still occurs.

[MFGF]

Hi MFGF!

Great suggestions, we're looking into the second link you mentioned now.

As for JVM heap sizes we did adjust them prior to posting.
Initial: 4096MB
Limit: 32768MB

We have plenty of room memory wise on our servers, I'd be surprised if that was the issue.

EDIT:

No luck on the second link, we removed the folder content like it's listed but no effect. QueryService is still 'Unknown' and the error still occurs.

Hi,

There's something somewhere stopping your instance from spawning the Query Service java container, by the sounds of things. How many java.exe processes do you see running?

Here's one possibility:

https://www.ibm.com/support/pages/queryservice-status-displays-unknown-within-ibm-cognos-administration

Thinking of other places to look to do more troubleshooting - try looking in the cognos server log (cognosserver.log) in <install directory>\logs. It may be huge, but look towards the end for the log messages the last time the Cognos instance was starting its services. Do you see any relevant messages pertaining to the Query Service, and if so, does the log provide any more detail about what's going on? Also check the dataset-service.log file to see if there is anything relevant in there.

Also, maybe check the transfer logs - these are created by the installation wizard when you install Cognos, and sometimes there can be errors shown in here where the install was unable to transfer in all the required content into the install location. If there were errors transferring in any of the Query Service components, that might explain things. These are also in the same logs folder - usually named IBM_Cognos_Analytics_Install_<date and time>.log

If you can pinpoint more details from any of these log files, it might help us to understand what's going on with the Query Service.

Cheers!

MF.

[Eric.Seitz]

Hi,

There's something somewhere stopping your instance from spawning the Query Service java container, by the sounds of things. How many java.exe processes do you see running?

Here's one possibility:

https://www.ibm.com/support/pages/queryservice-status-displays-unknown-within-ibm-cognos-administration

Thinking of other places to look to do more troubleshooting - try looking in the cognos server log (cognosserver.log) in <install directory>\logs. It may be huge, but look towards the end for the log messages the last time the Cognos instance was starting its services. Do you see any relevant messages pertaining to the Query Service, and if so, does the log provide any more detail about what's going on? Also check the dataset-service.log file to see if there is anything relevant in there.

Also, maybe check the transfer logs - these are created by the installation wizard when you install Cognos, and sometimes there can be errors shown in here where the install was unable to transfer in all the required content into the install location. If there were errors transferring in any of the Query Service components, that might explain things. These are also in the same logs folder - usually named IBM_Cognos_Analytics_Install_<date and time>.log

If you can pinpoint more details from any of these log files, it might help us to understand what's going on with the Query Service.

Cheers!

MF.

Hi MFGF,

I'm poking around the logs, in cognosserver.log the only error that looks different from the others is as follows:

022-02-01T14:10:34.450-0500   ERROR   com.cognos.pogo.services.DefaultHandlerService   [pool-60-thread-7]            NA      problem reconfiguring handler queryServiceHandler
com.cognos.xqe.config.ConfigFileException: XQE-CFG-0003 Failed to load configuration file file:/D:/Program%20Files/ibm/cognos/analytics/configuration/xqe.config.custom.xml.


The other instances of QueryService just seem to be old messages.

EDIT

After doing some digging I'm growing more confident that this is the source of our issues. The only thing is I'm not sure if I can fix it without reinstalling Cognos which I REALLY want to avoid if I can. I did find a reference to a file called "xqe.config.xml", maybe I could copy that file and rename it to the custom?

[MFGF]

Hi MFGF,

I'm poking around the logs, in cognosserver.log the only error that looks different from the others is as follows:

022-02-01T14:10:34.450-0500   ERROR   com.cognos.pogo.services.DefaultHandlerService   [pool-60-thread-7]            NA      problem reconfiguring handler queryServiceHandler
com.cognos.xqe.config.ConfigFileException: XQE-CFG-0003 Failed to load configuration file file:/D:/Program%20Files/ibm/cognos/analytics/configuration/xqe.config.custom.xml.


The other instances of QueryService just seem to be old messages.

EDIT

After doing some digging I'm growing more confident that this is the source of our issues. The only thing is I'm not sure if I can fix it without reinstalling Cognos which I REALLY want to avoid if I can. I did find a reference to a file called "xqe.config.xml", maybe I could copy that file and rename it to the custom?

Hi,

Hmmmmm. If you have an xqe.config.custom.xml file, that tells us someone has been trying to dabble in the settings of the Query Service. You should never (ever) edit the xqe.config.xml file, so if you want to make any changes to the settings, the approach is to take a copy of xqe.config.xml and name it xqe.config.custom.xml, then make the changes in the custom version. If the custom version exists, Cognos looks at this and ignores what is in xqe.config.xml.

The first thing I'd try is to simply remove xqe.config.custom.xml. Move it to another folder out of the way somewhere (so you can examine what is in there later on). Restart your Cognos instance and see if the Query Service comes up - it should use xqe.config.xml.

Assuming this works, you should then have a fully working Cognos Analytics instance.

For forensic evidence, you can then compare what is in xqe.config.custom.xml (wherever you moved it to) to what is in xqe.config.xml to see what is different. I'm guessing someone made a mistake in the custom file. Then you can start asking around to see who did it and why they felt a change was needed.

Good luck!

MF.

[MFGF]

Hi,

I got to wondering why someone would be messing around in xqe.config.custom.xml. It turns out there is a manual process to fix the Apache Log4j vulnerability that involves editing xqe.config.custom.xml (among other things). I'm guessing someone got this a bit wrong. A link to the process is here:

https://exploringtm1.com/wp-content/uploads/2021/12/mitigate_log4j_ca_upd2.pdf

If it was me, I would play it safe and install an officially patched version of Cognos Analytics. My concern is that if someone got this bit wrong, what else could they have messed up in your instance?

Cheers!

MF.

[Eric.Seitz]

Woah ok this is definitely something I'll bring up to our security expert. Hopefully this security patch will do it.

We dug around our files but annoyingly we're missing the original xqe.config.xml file. Looks like someone either overwrote it with the custom version or our vendor never included the original.

Thanks for all the help, this is fantastic! I feel like we're getting close!

Hi,

I got to wondering why someone would be messing around in xqe.config.custom.xml. It turns out there is a manual process to fix the Apache Log4j vulnerability that involves editing xqe.config.custom.xml (among other things). I'm guessing someone got this a bit wrong. A link to the process is here:

https://exploringtm1.com/wp-content/uploads/2021/12/mitigate_log4j_ca_upd2.pdf

If it was me, I would play it safe and install an officially patched version of Cognos Analytics. My concern is that if someone got this bit wrong, what else could they have messed up in your instance?

Cheers!

MF.

[MFGF]

Woah ok this is definitely something I'll bring up to our security expert. Hopefully this security patch will do it.

We dug around our files but annoyingly we're missing the original xqe.config.xml file. Looks like someone either overwrote it with the custom version or our vendor never included the original.

Thanks for all the help, this is fantastic! I feel like we're getting close!

Let me see if I can find an unchanged version of the xml file. What specific version/build of Cognos Analytics are you using? I have access to an 11.1.7 instance...

MF.

[Eric.Seitz]

Let me see if I can find an unchanged version of the xml file. What specific version/build of Cognos Analytics are you using? I have access to an 11.1.7 instance...

MF.

As it so happens we're running 11.1.7 too!

[MFGF]

As it so happens we're running 11.1.7 too!

I just sent an email to the address you used to register for this site.

Cheers!

MF.

[Eric.Seitz]

I just sent an email to the address you used to register for this site.

Cheers!

MF.

It worked! You're a legend man, our data modules are up and I'm working on our dashboards as we speak. Thank you so much MF!!

[MFGF]

It worked! You're a legend man, our data modules are up and I'm working on our dashboards as we speak. Thank you so much MF!!

That's great news!

Just make sure that putting the original xqe.config.xml back in place hasn't undone steps that were made to fix the Log4j vulnerability. I'd reach out to whoever has been updating your instance to make sure that you're still secure.

Have fun with your modules and dashboards!

Cheers!

MF.