User1 opens a spreadsheet (XLS / XLSX file), and logs into Controller Excel link. User1 presses F9 to run the report. This takes some time (e.g. 2:30 minutes). During most of this time, all other user's sessions (e.g. User2, User 3 etc.) work OK.
However, during some of the report running phase (e.g. between approximately 1:45mins and 2:25 mins) User2 cannot perform tasks inside Controller. For example, User 2 tries to open a "Company - Data Entry Reported Values" form - Excel appears to start launching, but the form does not appear until after User1's report has finished running.
In one real-life case, when User 2 tries to open a "Company - Data Entry Reported Values" form - Excel appears to start launching, but the form does not appear.
- Instead, the user is presented with a blank/white Excel screen, with the words "Not Responding" in the Microsoft Excel title bar.
This 'hanging' does not stop until after User1's report has finished running.
NOTE: The hanging does not occur during the entire (e.g. 2:30) minutes it takes to run the report.
- Instead, it only occurs during a portion (e.g. approximately the last third) of the report running time.
There are many potential causes for 'hanging' seen when running Excel-link reports.
This Technote specifically relates to the scenario where the cause is a known limitation (limitation reference APAR PM79557) in the underlying third-party architecture (Microsoft COM+) that Controller uses.
- This limitation will cause hanging when User1 uses other (non-Excel) functions inside Controller (for example publishing a Data Mart).
- For more information, see separate IBM Technote #1631457.
The behaviour is caused by COM+ synchronization issues when exposed to long running processes in Controller (most often seen with specifically large Excel reports).
- The problem is triggered because the actions cause one of the COM+ components (FrSrvFunc.SrvFunc) to try to simultaneously process the Excel report and also communicate with the database for the other users
- Due to the design of Microsoft's COM+ threading model, in some environments/systems these tasks can clash with each other (one job becomes dependant on the other job finishing).
The problem can (potentially) occur in any environment. However, it is more likely to be seen in environments where the Controller application server has a small number of CPUs (e.g. 1 or 2).
In addition, it is also more likely to be seen (and the symptoms more severe) when both of the following are true:
- Spreadsheet report is configured to use Optimise2 (ERO)
- Controller database is configured to use BULKCOMPRESS.
Symptoms may be particularly severe if User1's client device (for example the laptop/desktop PC that is running the spreadsheet via F9) has an old version of Excel Service Pack (e.g. MS Excel 2007 SP2).
- For example (in one environment) by upgrading User1's PC from Excel 2007 SP2 to SP3, this reduced the hanging time from approximately 40-60 seconds to approximately 20-30 seconds.
Resolving the problem
Upgrade to Controller 10.2 or later (which uses native .NET rather than COM for the vast majority of its server objects).
Modify the Controller application server so that its COM+ threading model is changed so that each of its COM+ activities has its own thread.
Steps to change COM+ threading model:
- Obtain a short period of downtime (no users on the system)
- Logon to the Controller application server as a Windows administrator
- Create the following registry key:
4. Reboot the application server (for the changes to take place).
TIP: The easiest method to achieve step 3 is to:
- Launch NOTEPAD
- Paste in the following contents:
Windows Registry Editor Version 5.00
- Save this file as "1621584_COM+_Fix.reg"
- Double-click on "1621584_COM+_Fix.reg" to add its information to the registry.
The symptoms can be reduced by ensuring that:
- The Controller application server has sufficient CPU cores (e.g. at least 4 CPU cores) assigned to it
- The client device(s) have been patched with the latest Microsoft Office service pack (e.g. Excel 2007 SP3).
1620550 - ** Troubleshooting ** Entire Controller syste
1347048 - What is Optimise2 (ERO / Enhanced Reporting O
1588640 - How to reduce "Out Of Memory" errors when usi
THIRD PARTY - Microsoft - Registry key for tuning COM+
1631457 - ** Troubleshooting ** All user's Controller s