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

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

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.

Cause

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

    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

    Fix:

    Upgrade to Controller 10.2 or later (which uses native .NET rather than COM for the vast majority of its server objects).

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

    In addition:
    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).

    Related information

    1620550 - ** Troubleshooting ** Entire Controller syste
    APAR PM79557
    APAR PM80390
    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+
    THIRD PARTY - Avoid Long Running Method Calls from a Si
    1631457 - ** Troubleshooting ** All user's Controller s

    Rate this page:

    (0 users)Average rating

    Add comments

    Document information


    More support for:

    Cognos Controller
    Controller

    Software version:

    10.1, 10.1.1

    Operating system(s):

    Windows

    Reference #:

    1621584

    Modified date:

    2014-06-09

    Translate my page

    Machine Translation

    Content navigation