IBM Support

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression" when trying to lock/unlock any period (in "Change-Period locking") caused by APAR PH12550

Troubleshooting


Problem

User clicks "Maintain - Period locking - Change-Period locking". User selects any period, and locks (or unlocks) one of the companies in the list.
  • User clicks 'Save' (to save changes). An error appears.
  • If user tries to perform the same task (lock/unlock periods) in Controller Web, a different error appears.
The problem may appear to be intermittent. This is because it is triggered only after the status of a company has changed (for example by running a 'company reconcile' process).

Symptom

Controller Classic:
Standard Error
Details:
image-20190605074800-1
Number:    5
Source:    FrangoDirect.PerLockHandler.PushPeriodLocks#System.Web.Services
Description:    System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Exception: The statement has been terminated.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
   at Microsoft.VisualBasic.ErrObject.Raise(Int32 Number, Object Source, Object Description, Object HelpFile, Object HelpContext)
   at ControllerServerCommon.RaiseErrSrv.RaiseError(String sUser, Int32 lErrNo, String sErrSource, String sErrDesc, String sErrHelpFile, Int32 lErrHelpContext)
   at FrStatusBT.PerLockBusinessT.PushPeriodLocks(String sGuid, String sUser, RecordSet rsPerlock, String sPerLock, Connection& conCaller, Boolean bUseTransaction, Boolean bSetStatus)
   at Cognos.Controller.Proxy.CCRWS.PerLockBusinessT_PushPeriodLocks(String sGuid, String sUser, DataSet rsPerlock, String sPerLock, Object& conCaller, Boolean bUseTransaction, Boolean bSetStatus)
   --- End of inner exception stack trace ---
   at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.ObjectLateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
   at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
   at Cognos.Controller.Forms.Form.frmPerLockComp.IFile_DoSave()
Controller Web:
image-20190507194131-1
image-20190507194204-2
Error while changing lock status for company xxxxxxxxxxxxxxxxx
Unexpected error

Cause

There are several known causes for similar errors.
  • TIP: See separate IBM Technote #0960392 for more examples.
 
This Technote specifically relates to the scenario where the cause is a defect (reference APAR PH12550) in the following versions of Controller:
  • Controller 10.4.0 RTM (10.4.0.103)
  • Controller 10.4.0 IF1 (10.4.0.105)
This causes corrupt values in temporary database tables.
More Information:
The defect was introduced in 10.4.0 onwards (as part of the new functionality where there is an audit trail of period locking and company locking)
  • It causes the system audit log table "sactrlinfo" to not be cleared (after Company status is changed).
For example, a method to trigger the problem is:
1. User #1 changes the status of a company
  • There are many different ways of doing this, for example by running Company Reconcile
2. User #2 tries to lock/unlock the period for that specific company.
  • Error occurs

Environment

Both of the following are true:
  • Controller 10.4.0 (not any earlier version, for example 10.3.1)
  • The customer has the Controller 'system audit log' functionality enabled.

Diagnosing The Problem

Open the database table "sactrlinfo". Inside there will be some entries which are causing the issue.
Example:
systempart    controlleruser    sourcename    changedate
COMPANY_PERIOD_LOCKING    USER1    Locked_Companies    2019-06-10 14:01:25.093
COMPANY_PERIOD_LOCKING    USER3    Locked_Companies    2019-06-06 15:15:59.387
COMPANY_PERIOD_LOCKING    USER4    SetCompanyReady    2019-06-07 13:48:17.250
COMPANY_PERIOD_LOCKING    USER5    Locked_Companies    2019-06-10 09:53:31.307
COMPANY_PERIOD_LOCKING    USER6    Locked_Companies    2019-06-05 15:15:09.930
COMPANY_PERIOD_LOCKING    USER7    Locked_Companies    2019-06-10 10:20:15.303
COMPANY_PERIOD_LOCKING    USER8    SetCompanyReady    2019-06-07 15:50:45.540
COMPANY_PERIOD_LOCKING    USER9    SetCompanyReady    2019-06-10 14:29:39.560
COMPANY_PERIOD_LOCKING    USER10    Locked_Companies    2019-06-10 08:47:13.073
COMPANY_PERIOD_LOCKING    USER11    Locked_Companies    2019-06-10 08:50:59.597
COMPANY_PERIOD_LOCKING    USER12    Locked_Companies    2019-06-10 13:26:30.100
COMPANY_PERIOD_LOCKING    USER13    Locked_Companies    2019-06-06 17:05:55.307
COMPANY_PERIOD_LOCKING    USER14    SetCompanyReady    2019-06-06 09:50:06.987
COMPANY_PERIOD_LOCKING    USER15    SetCompanyReady    2019-06-07 13:05:49.590
COMPANY_PERIOD_LOCKING    USER16    SetCompanyReady    2019-06-07 16:54:36.760
COMPANY_PERIOD_LOCKING    USER17    Locked_Companies    2019-06-10 14:27:09.250
COMPANY_PERIOD_LOCKING    USER18    Locked_Companies    2019-06-10 12:59:12.270

Resolving The Problem

Fix:
Upgrade to either:
  • Controller 10.4.0 IF2 (10.4.0.107) or a later version of Controller 10.4.0
  • or Controller 10.4.1 (or later).
   
Workarounds:
There are several different methods to workaround the problem.
Method #1 (instant cure of symptom, but issue will likely return later)
Delete the entire contents of the temporary table 'sactrlinfo'.
There are two ways to achieve this:
  • either (a) Ask your I.T. department's database administrator (DBA) to delete the contents of the temporary table 'sactrlinfo' (do not delete the actual table itself!).
  • or (b) Perform a database optimisation.
  
Steps to perform a database optimisation:
1. Ensure no other users logged onto Controller
2. Click "Maintain - User - Single User"
3. Click "Maintain - Database - Optimize"
4. Choose/select all available options *except* do not tick 'Rebuild indexes' (there is no need - this option is for performance improvement reasons)
5. Click "Run".
Method #2
Disable the system audit log functionality entirely.
Method #3 (ideal long-term workaround)
Disable the system audit log functionality only in relation to the locking/unlocking of companies and periods.
IMPORTANT: Be aware that doing this will break the following two functions:
  • Controller Web: Real time update functionality for company lock statuses (in the Controller Web dashboard).
    • If you are not using Controller Web, then you do not have to worry about this!
  • Controller Classic: he new "auditing on company locking" feature that was first introduced in 10.4
    • If you have recently upgraded from 10.3.1, you may not be using this feature anyway.
  
Steps to disable audit log for locking/unlocking of companies/periods:
Ask your I.T. department's database administrator (SQL DBA) to:
1. Locate the Controller database
2. Expand the table: xopen
3. Disable the trigger: trg_saxxopen
image-20190605082448-1

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"Component":"","Platform":[{"code":"PF033","label":"Windows"}],"Version":"10.4.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
21 January 2020

UID

ibm10883764

Page Feedback