PM94635: Migrating Framework Manager packages, Query Item with ?TRIM? calculations, experienced error SQL0204

APAR status

• Closed as program error.

Error description

• Customer is currently conducting a proof of concept (POC) new
  install to upgrade existing Cognos BI 10.1 release to the new
  Cognos BI 10.2.1. While migrating Framework Manager packages,
  and testing variable character Query Item with ?TRIM?
  calculations, experienced error ?[IBM][System i Access ODBC
  Driver][DB2 for i5/OS]SQL0204 - TRIM_BOTH_SPACES in *LIBL type
  *N not found.?
  

Local fix

• Identified the FM client ￢ﾀﾘcogdmd2.ini￢ﾀﾙ or the BI server ￢ﾀﾘc
  ogdmd2.ini￢ﾀﾙ was not called for DB2 function mappings.  As an i
  nterim solution, provided a workaround by modifying the parent F
  M client ￢ﾀﾘcogdmod.ini￢ﾀﾙ or the BI server ￢ﾀﾘcogdmod.ini￢ﾀﾙ fi
  le to map ￢ﾀﾘTRIM_BOTH_SPACES()￢ﾀﾙ function as ￢ﾀﾘLTRIM(TRIM()).
   A new Database Function for 'TRIM_BOTH_SPACES' was added for th
  e DB2 /400 SQL for the driver CWBODBC.DLL.  [Database Functions
  DRIVER:CWBODBC.DLL] c:trim_both_spaces(%1c)=LTRIM(RTRIM(%1)); c:
  trim_leading_spaces(%1c)=LTRIM(%1); c:trim_trailing_spaces(%1c)=
  RTRIM(%1);  A new Exceptions Value for the driver CWBODBC.DLL to
   use 'C' was also included.  [Exception value Expressions DRIVER
  :CWBODBC.DLL] Trim_Both_Spaces=C Trim_Leading_Spaces=C Trim_Trai
  ling_Spaces=C  Following restart of the BI Cognos Services & FM
  application. Test of the Query Item and verification of the SQL
  generated results in.  Cognos SQL -------------- select trim(TIM
  EDIM.NAMLON) as NAMLON from ....  Native SQL -------------  sele
  ct LTRIM(RTIM("TIMEDIM"."NAMLON")) "NAMLON" from .......   Howev
  er, following further regression testing, it was identified othe
  r mapped functions e.g. CAST are experiencing similar issues.
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * N/A                                                          *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to IBM Cognos Business Intelligence 10.2.1 Fix Pack  *
  * 1                                                            *
  ****************************************************************
  

Problem conclusion

• Code Fix
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  COG FRAMEWRK MG

• Fixed component ID

  5724W12FM

Applicable component levels

• RA21 PSN

     UP