1464532 - Error:"Failed to retrieve data from database" while refreshing Crystal Report based off DB2 Mainframe Command Object.

"Failed to retrieve data from database" error while refreshing Crystal Report based off DB2 Mainframe Command Object.

• Crystal reports 2008 (12.2.0.290)
• DB2 9.5
• Data Direct DB2 Wire Protocol 5.3 driver

1. Create a DSN using the Data Direct DB2 Wire protocol driver.
2. Use the DSN to create a report based off DB2 Mainframe database.
3. Create a sample command object as follows:

          SET CURRENT SCHEMA = '{?parameter}'; 
          Select * from table

     4.  Drag and drop some fields in the Details section.

     5.  Refresh the report.

• ODBC drivers doesn't support all the commands that work in database. As ;( semicolon) indicates the end of the statement in crystal Reports and it doesn't run both the statements.

• For example if we run:                                                                                                               
  select * from table1;                                                                                                              
  select * from table2                                                                                                                
  Crystal returns only first statement.                                                                                     
• When we use the native drivers of IBM DB2 we are able to run the statement                                                   
  set current schema='{?parameter}';                                                                                                 
  select * from table

To work around this issue use the statement like:                                                        
select * from '{?parameter}'.table

Crystal Reports , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem