IBM Support

Few Tips for migrating Cognos Reports developed in Oracle Compatible with IBM DB2

Technical Blog Post


Abstract

Few Tips for migrating Cognos Reports developed in Oracle Compatible with IBM DB2

Body

Abstract: This article describes some best practices for migrating existing Cognos projects developed in Framework Manager with Oracle to work with IBM DB2. This article assumes that the reader is familiar with IBM Cognos reporting, Cognos Framework Manager and has the required DB skills.

 

Overview

Framework Manager is the IBM Cognos Business Intelligence modeling tool for creating and managing business-related metadata used in BI analysis and reporting.
 
Framework Manager uses the concept of projects which contains objects that you organize according to the business model and business rules of your organization.
Creating Cognos projects involves Query Subjects, procedures, tables, views etc. using IBM Framework Manager. The Project created is then published as a package for the use of Cognos reporting tools  for actual designing the reports using Report Studio, providing a single, integrated business view of any number of heterogeneous data sources.
 
 
In reality, we really shouldn't be designing our reports based on a specific
vendor for the Database. Each vendor will have different internal techniques for how to process the query. There are times when Oracle procedures or queries used in Cognos projects may not directly work with the DB2 RDBMS due to various compatibility issues.
Hence, most of the developers end up re-designing their Cognos projects to make them DB2 compatible.
Based on our experience we will be sharing Best Practices on how to resolve these compatibility issues.
 

Challenges


As a project requirement we had to run the reports developed in Oracle with DB2, resulting in various compatibility issues.
Screenshots below describes the issues which we encountered:  
 
When the procedures or query subjects developed using Oracle are validated in Framework manager with IBM DB2, the connection throws error as shown below:
 

     

 

The above issue exists as we do not support output parameters for stored procedures in DB2. We only support a single result set from the stored procedures.

Experience

 

While investigating the above issue we identified the following solutions.
 

  1.  IBM Cognos does not support the same syntax (as used in Oracle) for output parameters (OUT) in stored procedures for DB2.

 
            To address the problem we modified the syntax of the Oracle stored procedure as follows:

ORACLE

CREATE OR REPLACE PROCEDURE get_test_prc
(
p_Cursor OUT SYS_REFCURSOR,
P_No. IN NUMBER
)

AS

OPEN p_Cursor FOR

 

DB2

CREATE OR REPLACE PROCEDURE get_test_prc
(

IN P_No. NUMBER
)

 

DECLARE p_cursor CURSOR WITH RETURN TO CALLER FOR

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2) IBM Cognos supports single result set for stored procedures with DB2.      

 

To specify OUT parameters of the cursor type in DB2 stored procedures we can use ResultSet objects.

 

A cursor data type and a stored procedure have the following definitions:

 

create procedure proc_result_set ()
language sql
DYNAMIC RESULT SETS 1
begin
  declare mycur cursor with return to caller for
     select firstname, lastname from employee order by lastname, firstnme;
  open mycur;
end!

 

 

 

 

 

Benefit

 

Using the above approach we can minimize the effort of rewriting the Cognos project from scratch having query subjects / stored procedures while migrating from Oracle to IBM DB2.


The only changes needed are implementing the above mentioned syntax changes to Oracle procedure making it IBM DB2 compatible.


The procedure just needs to be re-imported using the Metadata Wizard in IBM Framework Manager with IBM DB2 connection, publishing the package with above changes in Report Studio where the report will be executed successfully with IBM DB2 connection.

 

References

 

Further information can be gathered from the following link

/support/pages/node/319025

 

 

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSCTJ4","label":"Case Manager"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11281148