Operations using BLOB and CLOB fields

Technote (troubleshooting)


Problem(Abstract)

When using BLOB fields in Framework Manager there are some limitations imposed upon their use by the underlying database. Due to the potential size of a BLOB field databases tend to prevent use of BLOBs in joins, grouping, scalar functions, and some aggregate functions.

Symptom

There are several scenarios where this issue may occur. Based on the different circumstances there are various error messages that may be encountered.

QE-DEF-0186 General error.

Trace back:
WPBIBusMethod.cpp(190): WPDataRetrievalException: CCL_CAUGHT: WPBIBusMethod::run
WPReportExecutionMethod.cpp(174): WPDataRetrievalException: CCL_RETHROW: WPReportExecutionMethod::checkRequestForExceptions
WPExecuteRequestThread.cpp(169): WPDataRetrievalException: WPExecuteRequestThread::checkException
WPEngine.cpp(946): WPDataRetrievalException:
WPController.cpp(263): WPDataRetrievalException: CCL_RETHROW: WPController::executeRendering()
LWDataRetrievalEngine.cpp(305): WPDataRetrievalException: CCL_RETHROW: LWDataRetrievalEngine::prepare
LWDataRetrievalEngine.cpp(1838): WPDataRetrievalException: CCL_THROW: LWDataRetrievalEngine::runQSQuery
Source/QE_QsApi.cpp(3337): QSException: CCL_THROW: QE

or

QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions'.
UDA-SQL-0446 Oracle returned an error message.
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

or

BMT-MD-0003 UDA-SQL-0220 A database key or unique index is required to access blob data.

or

RQP-DEF-0403 Using a blob column in this query requires that the query subject [Namespace Name].[Query Subject Name] must have either a key or a unique index.


Resolving the problem

While these operations cannot be performed using the BLOB field directly it is possible to achieve some of these capabilities by assigning a unique key to serve as a handle for the BLOB itself. The unique key can be used in place of the BLOB to create joins and group the BLOB field itself. To create a unique key you must use Determinants in Cognos 8 or Dimensional Information in IBM Cognos ReportNet. Details on creating this information in your model can be found in the Framework Manager User Guide provided with the product installation.

Unfortunately the following cannot be resolved using a unique identifier field:

  • Use of scalar functions such as substring() or position().
  • Aggregate functions such as total() or average().
  • Query set operations such as union, intersect, and except.

In such a case it may be necessary to cast the BLOB data to the appropriate data type for the desired function. The ability and methods for performing such a data type case vary between database vendors and versions. Consult your database software documentation to identify the capabilities of your database and the methods required to cast the BLOB to another data type.

Note that stored procedures within Framework Manager will not project the CLOB or BLOB data types. To use these types with stored procedure output they must be cast to another data type before the procedure returns the result set.

For Oracle BLOB or CLOB fields one available option is to use the to_char() function to convert the fields to a string data type (typically varchar2). However, it is still recommended to consult your software documetation for the most up-to-date recommendations on working with CLOB and BLOB types.

 

Steps:The Framework Manager User Guide provided with the product documentation will provide a more complete set of steps and information regarding Determinants and Dimensional Information. It is recommended that you review this document to obtain a more complete understanding of this subject area.

If your query subject contains data that represents multiple levels of cardinality then it is recommended that you also include these other levels in the Determinants or Dimensional Information to control relationship behavior and related grouping when the query is used in conjunction with other query subjects.

To specify Dimensional Information in Cognos ReportNet

  1. Select the query subject and, from the Actions menu, click Specify Dimension Information.

  2. Drag a column to act as a key from the Available items box to the Levels box. If dimensional information already exists then it may be possible to reuse an existing level to define a unique key for the BLOB or CLOB fields.

  3. If additional columns are required to generate a unique key then drag these from the Available items box to the Key box of the new level. Note that the columns must define a unique key for the BLOB data.

  4. Select the Unique Key check box for the unique key level.

  5. Drag the BLOB or CLOB query items from the Available items box to the Attributes/Facts box of the new level to assign them as attributes of the unique key.

  6. Drag the Level from the Levels box to the Hierarchies box to position it within the scope of an existing hierarchy or to create a new hierarchy if one does not already exist.

To specify Determinants in Cognos 8

  1. Click the query subject you want and, from the Actions menu, click Edit Definition.

  2. Click the Determinants tab.

  3. To add a new determinant click the Add link.

  4. Drag one or more query items from the Available items box to the Key box to specify a key for the new determinant. These cannot be the BLOB or CLOB fields from the query subject.

  5. Select the Uniquely Identified check box to define the new determinant as a unique key. Note that the combination of keys selected for the determinant must actually be a unique key for the BLOB fields. The attributes for the unique key will automatically include the BLOB or CLOB fields from your query subject.

Cross reference information
Segment Product Component Platform Version Edition
Business Analytics Cognos 8 Business Intelligence Query Studio
Business Analytics Cognos 8 Business Intelligence Report Studio
Business Analytics Cognos ReportNet ReportNet

Historical Number

1039627

Rate this page:

(0 users)Average rating

Document information


More support for:

Cognos 8 Business Intelligence
Framework Manager

Software version:

8.3, 8.4, 8.4.1, 10.1, 10.1.1

Operating system(s):

Linux, Windows

Reference #:

1342987

Modified date:

2014-01-22

Translate my page

Machine Translation

Content navigation