Other user's Controller sessions appear to hang ("Not Responding") during some of the time when a different user runs (F9) an Excel-link spreadsheet

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.

• TIP: For more examples, see separate IBM Technote #1620550.

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.
NOTE:

• 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.

Fix:

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:
1. Obtain a short period of downtime (no users on the system)
2. Logon to the Controller application server as a Windows administrator
3. 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

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\COM3\STAThreadPool]
"EmulateMTSBehavior"=dword:00000001

• Save this file as "1621584_COM+_Fix.reg"
• Double-click on "1621584_COM+_Fix.reg" to add its information to the registry.

Workaround:
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).