Cognos error - RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'

[rkumar-5]

Hi All,

I have a strange Cognos error that PTC is having a hard time diagnosing. I have Cognos installed correctly and double checked all of the db.properties. We have Windchill cluster environment 10.2 M030 CPS 09. Cognos is installed in other node which is not a part of Windchill cluster.

I can also successfully login to Cognos's web UI and update the model/load reports. Authentication works fine as well.

1. All QML reports are working fine.

2. Infoengine reports with less data is working fine but when we are trying to run report with (50k rows), it's working for 200 rows, it's failing with below error:-

Original Error: RQP-DEF-0177 An error occurred while performing operation 'sqlScrollBulkFetch' status='-232'.  UDA-SQL-0528 The XML parser returned the following message: "General XMLParser Error!".  UDA-CUR-0000 Unable to fetch the row.  RSV-SRV-0042 Trace back:  RSReportService.cpp(729): QFException: CCL_CAUGHT: RSReportService::processImpl()  RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchWait_Request  RSASyncExecutionThread.cpp(848): QFException: RSASyncExecutionThread::checkException  RSASyncExecutionThread.cpp(305): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRun_Request  RSASyncExecutionThread.cpp(904): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRun_Request  Execution/RSRenderExecution.cpp(591): QFException: CCL_RETHROW: RSRenderExecution::execute  Assembly/RSDocAssemblyDispatch.cpp(323): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly  Assembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assemble  Assembly/RSDocAssemblyDispatch.cpp(417): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward  Assembly/RSReportPagesAssembly.cpp(178): QFException: CCL_RETHROW: RSReportPagesAssembly::assemble  Assembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly  Assembly/RSPageAssembly.cpp(314): QFException: CCL_RETHROW: RSPageAssembly::assemble  Assembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly  Assembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assemble  Assembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly  Assembly/RSTableCellAssembly.cpp(151): QFException: CCL_RETHROW: RSTableCellAssembly::assemble  Assembly/RSDocAssemblyDispatch.cpp(417): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForward  Assembly/RSDocAssemblyDispatch.cpp(367): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly  RSResultSetIterator.cpp(1472): QFException: CCL_RETHROW: RSResultSetIterator::retrieveData  QFSPartialDataset.cpp(80): QFException: CCL_RETHROW: QFSPartialDataset::GetEdgeIterator  Source/QEDmResultSet.cpp(75): QFException: CCL_THROW: QE

Appreciate If anyone can provide any suggestion/inputs on this.

Regards,

rajivranjan.kumar@allegion.com

3177968382

[BineshKumar1]

This error just says the Cognos is not able to get result from the datasource through XML4Cognos. It is very generic error.

• Do you have query governors set in Cognos? like Query Execution Time limit or Maximum number of rows retrieved. Also for testing disable the local cache for the queries used
• Do you have any query limits set in wt.pom.paging.snapshotQueryLimit  or wt.pom.queryLimit? What if you run an Info*Engine task that has more than 200 rows as result from a web browser or IE task editor? May be a basic task to fetch all the documents

Thank you

Binesh Kumar

[rkumar-5]

Thanks a lot Binesh for your response.

Can you pls suggest how to set query governors/disable local cache in Cognos?

QueryLimit is set to unlimited(-1). If we run full report(50k rows) from web browser, it fails to display and throws "timeout error". PTC said we cannot have such long open session. At present, we are downloading the report(in XML to XLS format) on WC Server (20 MB in Size), it takes 15 minutes to download the report. Now we are trying to run the same Infoengine task through Cognos.

Thanks in Advance,

Rajiv

[BineshKumar1]

It is set in query hits in Cognos Query properties. In the query explorer, navigate to query properties.

But I don't this that is the problem here, 15 minutes for 50k rows sounds really bad from a performance standpoint.  You need to identify were the bottleneck is - info*engine query / server/ database ? If you have PSM installed, it has a Business Transaction for " Cognos Interactions" , you can use this to identify the transaction captured through URI  /servlet/XML4Cognos.

Also try profiling the action on the server database and see whether you can optimize the query.

Thank you

Binesh

[satre-2]

in cognos GUI error code is RSV-SRV-0042 Trace back: as per below article, issue is related to ttime-out

http://www-01.ibm.com/support/docview.wss?uid=swg21340994

See below article for increasing asynctimeout.

http://www-01.ibm.com/support/docview.wss?uid=swg21385378

Can you check what is error in cogserver.log file The error messages in the GUI usually do not make any sense (apart from the SQL type errors)

Also, what king of data you are querying from info engine task. 15 minutes for 50K rows seems to be too much. can you share the info engine task ?

Thanks

Shreyas

[rkumar-5]

Hi Binesh/Shreyas,

Thanks for your valuable suggestions. We tried them but still the same.

Earlier, we were doing lot of calculations inside infoengine task xml with the OOTB/custom attributes and creating new attributes for all change objects (ECR/ECN/MCN) for our report analysis. Now, we have planned to remove all calculations from infoengine task and do all of them on cognos side.

Regards,

Rajiv

[BineshKumar1]

Hello Rajiv,

i would vote for that. Recently we have created some complex reports based on  a couple of types of WC Packages with some workflows tasks assigned to it. We found that Cognos handle the reporting calculation much efficiently and you can take a lot of load off from the foreground MS.

You can create queries in Cognos based on Windchill Data Sources and use Cognos functions to analyze and present it in the way you want.  This queries could be based on multiple task templates or report tasks from Windchill.This has positives as well as negative.  A lot of existing videos are available in youtube around Cognos and I found them really handy.

Thank you

Binesh

[jkulkarni]

Hello Rajiv,

I faced exactly same error while running any of cognos report. Please check MS logs as it will give exact error of failure. My issue got resolved after adding IP of Cognos server

in wt.auth.trustedHosts property in wt.properties. Also map ip address of gateway server in host file of cognos host server if you are using distributed architecture of cognos.

Exact error from MS log will help to drill down your issue.

Thanks,

Jayesh