Troubleshooting
Problem
Inside the 'main' Controller client, user clicks "Reports - Run". User chooses a report (or form) and runs it as a report. This opens up in Excel, and starts to run. An error appears. Problem is intermittent - the report/form runs successfully most of the time. The crash/problem is rare.
Symptom
Excel has stopped working
Cause
Defect (reference APAR PI93949) in Controller.
- This is triggered by the Excel functionality 'multi-threaded calculation'.
Environment
Client device has multiple CPUs/cores.
Resolving The Problem
Fix:
The solution depends on what version you are currently using:
- Controller 10.2.5140.42 (or earlier) - Either:
- Patch to Controller 10.2.5140.45 (or later 10.2.1 version)
- or upgrade to Controller 10.3.1 Interim Fix 2 (also known as 10.3.1100.166) or later.
- Controller 10.3.1.64 (or earlier 10.3.0 version) - Either:
- Patch to Controller 10.3.1.66 (or later 10.3.0 version) *and also* use the server preference called 'MULTITHREAD_OFF' (see below)
- or upgrade to Controller 10.3.1 Interim Fix 2 (also known as 10.3.1100.166) or later.
- Controller 10.3.1100.156 or 10.3.1100.159 (10.3.1 RTM and IF1):
- Patch to Controller 10.3.1 Interim Fix 2 (also known as 10.3.1100.166) or later.
============================================================
Workarounds:
Modify the reports/forms to run with multi-threaded calculation disabled.
- NOTE: disabling Excel multi-threading will cause a slight performance decrease for your reports. This decrease will vary (depending on the report), but in one real-life customer example, the report was slower by approximately 30%.
There are several different methods to disable Excel multi-threaded functionality. Choose the method which is easiest for you:
Method #1 - Use the server preference called 'MULTITHREAD_OFF'
IMPORTANT: This method can only be used with the following versions of Controller:
- 10.2.5140.42 (or later versions of 10.2.1)
- 10.3.1.66 (or later versions of 10.3.0)
- 10.3.1100.166 (or later versions of 10.3.1)
- Therefore it needs to be enabled for each individual database (for example 'production', 'test', 'development') that you want to apply it to.
- Variable Name = MULTITHREAD_OFF
- Variable Value = TRUE
Steps to enable MULTITHREAD_OFF:
The server preference is enabled on a per-database basis.
1. Launch the Controller client
2. Choose the database (where you wish to make the change)
3. Logon as an administrator
4. Click "Maintain - Configuration - General"
5. Click 'Server Preferences'
6. Create a new entry:
Setting this value to TRUE will disable multi threading in Excel processes (once the user is being logged on to Controller)
7. Press 'Save'
8. Exit Controller
9. Re-launch the Controller client
10. Test.
Method #2 - Only disables multi-threaded calculation for Reports:
Modify each individual reports/form to run with multi-threaded calculation disabled.
- NOTE: The methods to achieve this (described below) do not work if a user runs a 'standard report' and then sends this to Excel (in other words, changes a standard report to appear in XLS or XLSX format). For more details, see the end of this Technote.
1. Click "Reports - Create"
2. Select an existing report inside Excel, and open this in Excel
3. Click "Excel Start button - Excel Options"
4. Click "Advanced"
5. UNTICK the box 'Enable multi-threaded calculation':
6. Inside the Controller Link menu, click "Save Report":
7. Repeat the above steps for all reports (so that all reports have multi-threaded calculation unticked).
Method #3 - Disables multi-threaded calculation for both Reports and Forms:
IMPORTANT NOTES:
- The following steps are based on guidance from a third-party (non-IBM) source. Customers should read the steps carefully, and understand the processes involved, before performing the actions. IBM is not responsible for any inadvertent problems caused by following the advice of these third-party (non-IBM) website(s).
- If a user runs a 'standard report' and then sends this to Excel (in other words, changes a standard report to appear in XLS or XLSX format) then this workaround will stop working. For more details, see the end of this Technote.
- The following instructions are based on Excel 2013. They may need to be modified slightly if you are using a different version of Excel.
1. Download the file "DisableMultiThreading.XLAM" from here: http://www.remkoweijnen.nl/blog/2012/06/08/excel-2010-multi-threaded-calculation/
2. Logon to the client device, using the 'bad' end user's Windows account
3. Copy the file "DisableMultiThreading.XLAM" to a sensible folder location
- For example C:\UTILS\DisableMultiThreading.XLAM
5. Click "Add-Ins"
6. Ensure that 'Manage' is set to "Excel Add-ins" and then click 'Go':
7. Click "Browse"
8. Browse to the file (for example C:\UTILS\DisableMultiThreading.XLAM) and add it to Excel
TIP: To check that this has worked:
- Launch Excel
- Click "Excel Start button - Excel Options"
- Click "Add-Ins"
- Check that it looks similar to:
===============================================
IMPORTANT: Be aware that the above workaround methods stop working around the problem if the end user runs a standard report to XLS/XLSX format.
For example, if the user runs this standard report...
...and then changes 'Select Format' to XLSX (which opens the report in Excel), then (from then onwards) the above workarounds will stop functioning.
In other words, by running a standard report to Excel, then (afterwards) the multi-threading is always enabled.
- The only method to get the workaround functioning correctly is for the end user to then exit Controller/Excel (close the main Controller client and Excel) and then re-launch Controller.
===============================================
Related Information
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg22013018