DCC Performance Tuning
Hello, we finally moved our huge Insight ETL to a 'DCC -> Custom Data Manager ETL' strategy. All systems 5.0..2 iFix007, Rational Insight 1.1.1.6. We're super happy with DCC in general so far, previously a full ETL would take well over week (counting historical tables). Being able to go back in time to correct ETL errors is nice, it's possible with Cognos but it's a headache. However, there are some performance issues we would like to address:
Are there any tuning guides for DCC? We have a Cognos specialist who can improve DM performance, but we're not sure what to do with DCC. Sometimes I see thread lock errors in the WebSphere profile running it, and some of our jobs are failing, as listed:
CCM - Time Sheets Part 3: Never works, freezes at random point in ETL, is terminated after 1 hour of no progress
RM Facts and RM History - always fail at the same point, after 272000 and 245000 rows in respectively. We might open a PMR about this, our RM jobs in Cognos DM always failed too though of course less gracefully. We ran a full load with DCC and the main RM job succeeded after 26 hours, I'm not sure why those tables aren't working.
Random other tables intermittently fail by seeming to pause in the middle of things in the logs, then being killed after an hour.
It makes me wonder if all this has to do with some kind of SQL Server or WebSphere/Java thread limit or resource starvation. The exact setup directions for 5.0.2 DCC were followed, all configuration changes for WAS were done via the jython script that comes with it. We could possibly increase the Java heap size, but I know that too large can be problematic for garbage collection. The report server is a 12 core 64GB VM, Windows Server 2012, we have room to increase the power of the DCC WAS server if we can, we are just not certain of anything specific we should try adjusting.
2 answers
Comments
Request and Requirement Management History both fail right now, but we do actually want that historical data. If we disable it, wouldn't we not be able to report on state change history and such? Why are those jobs problematic? Some of them run successfully, only Request and Requirement Management History and Timesheets Part 3 fail consistently, even when manually told to run by themselves. Do those jobs create some sort of resource conflict?
Right now I've found one work item that is not being updated; doing a SQL query on RIDW.VW_REQUEST shows it has data that is out of date even though I just successfully ran the CCM - Work Items and a Request Management Facts job. It is not reflecting the latest data from CLM. I am sure there are more like this, but I don't understand the cause of this erratic behaviour.
And should Delete Fact Table Data run before the rest of the Data-Mart jobs? That seems to be what DCC does.
The history jobs collect just historical lookup data that would allow you to write reports that drill down from a metric to the artifacts that contribute to the metric. The actual historical metric is collected by the fact job.
There might be helpful tips in this guide for DCC tuning https://jazz.net/wiki/bin/view/Main/PerformanceTuningDCCForReportBuilder