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

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

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