Issue with multiple reports/pages

[sbelli]

I'm working on a series of reports that the business wants to have run within the same "report" based on the prompt page. So currently, I've started with the first three of these reports and using conditional rendering at the page level, I was able to get each report to generate when all three are selected or just one or two.

But then the business came back with an additional column for the reports. Now, when one or two are selected, they run fine. But when I select all three then I get the following error:


172.24.117.25:10085   1159170   2013-05-21 15:51:18.253   -5         0   0   4627   QOS   836   3   Audit.RTUsage.QOS      <message code="-28" severity="error" title="QE-DEF-0459 CCLException" type="general">RQP-DEF-0177 An error occurred while performing operation &apos;sqlOpenResult&apos; status=&apos;-28&apos;.
UDA-SQL-0114 The cursor supplied to the operation &quot;APICursor::OpenResult&quot; is inactive.
UDA-SQL-0530 Could not start a gateway thread to execute the request asynchronously
</message>


Obviously this appears to be an SQL/database error. But what I don't understand is that if any of the reports are run as just one selection, they work fine. Even a combination of two of the reports works fine. Why is this only occuring if I select all three reports? Am I hitting some sort of memory limit within Cognos (or perhaps in DB2)?

Thanks for any help!

[CognosPaul]

UDA-SQL does indicate that the error is coming from the database.

Thought 1: There is an "Open asynchronously" checkbox in the data source settings; make sure that's checked.
Thought 2: Depending on your version of Cognos, there may be memory issues. Does the error persist if you reboot the server?

[sbelli]

Paul, thanks for the suggestions. We changed the "Open asynchronously" checkbox and then I got another error:

An error occurred while performing operation 'sqlAOpen' status='-9'.

The server runs on AIX and was restarted last night. We've opened up a support ticket with IBM to see if they had any suggestions, but, of course, I'm open to any thing else you or others on the forum might have.

Thanks!

[CognosPaul]

sqlAOpen is an evil, horrible error that should be shot.

What version of Cognos are you running?

Restart Cognos.

Run a list consisting of a single field with no filters. Are you getting this error?
Run each of your reports, do any of them cause this error?
You mentioned you're using DB2. Are you using native functions or Cognos? Is Cognos doing any local processing?
Are you using any hand-written SQL?

[sbelli]

sqlAOpen is an evil, horrible error that should be shot.

What version of Cognos are you running?

Restart Cognos.

Run a list consisting of a single field with no filters. Are you getting this error?
Run each of your reports, do any of them cause this error?
You mentioned you're using DB2. Are you using native functions or Cognos? Is Cognos doing any local processing?
Are you using any hand-written SQL?

We're running Cognos 8.3.

We've got plenty of other reports running with and without filters and we don't get the error. And if I run each of the three reports that I'm working on separately, again, we don't get the error. It only comes when I try to run all three back to back within the same output (ie., all generated off the same prompt page).

I'm not using any functions that are specific to DB2. I'm not sure what you mean by Cognos doing local processing, but there's some pretty lengthy, heavy filters with Case statements and joins, etc. that I'd qualify as being more "hand written SQL" just because they aren't straight filter on one field type things.

I just think there's something going on in terms of Cognos or its interaction with the database that's causing too much memory to be used and maybe it's just throwing the SQL error out because it can't finish the query. Just a guess, but if it isn't happening for one or two reports and only is happening with all three reports, that's what I'm thinking.

[CognosPaul]

My guess is that some of the filters are causing Cognos to use local processing. Which means that it needs to dump the data from DB2 into the Cognos server and then run an additional query on top of that data set. If the local processing involves filters, DB2 might be choking on the load. I've also seen issues with the way Cognos translates certain Cognos functions into native DB2 functions.

First thing to do, go to tools->show generated sql. Are there multiple unconnected select statements? Is the select missing any filters?

Does the report fail instantly when you try to run it, or only after a wait? If it's after a wait, open the Cognos server and keep an eye on the bibustkmain processes. Run a trace while it's running to see what the db is doing.

Try gradually simplifying the report, is there any point in which removing a filter (or several) allows the report to run?