IBM Support

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-120'.

Troubleshooting


Problem

Situation # 1: Running any report throws errors. Situation # 2: In Framework Manager, when trying to make a query subject based on two fact tables and a degenerate dimension throws errors.

Symptom

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-120'.

UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.

For Situation # 1 (Report Studio) you may also see the following error:


  • UDA-SQL-0526 The DISTINCT clause is not supported by the database. This operation requires local processing of the data.


For Situation # 2 (Framework Manager) you may also see the following errors(s):

  • UDA-SQL-0462 The OLAP function 'sum' is not supported by the database. This operation requires
    local processing of the data.


Switching to Limited Local processing on the data source and re-testing may then cause new errors:

QE-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-28'.

UDA-SQL-0114 The cursor supplied to the operation "APICursor::OpenResult" is inactive.

UDA-SOR-0009 The key description is invalid.

Cause

For both situations, Functions requiring limited local processing in calculations were used. This is not possible when database only processing is utilized.

Resolving The Problem

Changing the processing from Database Only to Limited Local on the query.

Database Only means that all queries are going to be processed by the database.

Limited Local means the Cognos BI server is allowed to process queries that can not be processed completely on the database.

Steps:

The steps below are just a couple of scenarios that might produce this error

Situation # 1 (Report Studio):
  1. Open the report in Report Studio
  2. Select the query
  3. Under Properties --> Query, change Processing to Limited Local
  4. Save the report


Situation # 2 (Framework Manager):

Switch to Limited Local processing on the data source in the Framework Manager Model and re-test. Be aware that this may cause the additional errors mentioned in the Error messages section for Situation #2 above. If this occurs:
  • Verify that the key values used in the join are not of the data type RAW, which is not supported by Framework Manager. This means Framework Manager is not able to perform the join after loading the data for local processing.
  • Use the cast function to cast the RAW data type to a data type which is supported by Framework Manager.

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Report Studio","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2;10.1.1;10.1","Edition":"Edition Independent","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
26 May 2021

UID

swg21341782