Datamanager 10.2.1: SQL query fails when used TRIM() and "cognos sql" checked.

[LEO]

Hi,

Environment:
Datamanager 10.2.1 64 bit
OS: Windows2008 64bit
Database:DB2 for i5/OS

When i execute a sql queries from data manager10.2.1, containing TRIM() in sql query and check the "Cognos SQL" option. It fails to execute.

Error is:

[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0204 - TRIM_BOTH_SPACES in *LIBL type *N not found.

I removed the TRIM() function from query, it executed successfully.
I removed "Cognos SQL" option and kept TRIM() function, it executed successfully.
Instead of TRIM(), i kept LTRIM( RTRIM(Col)) in the query, it executed successfully.

Could anybody help why this may be happening and where i may need to do the changes in COGDMOD.ini for TRIM function.

Since on removal of "Cognos SQL", the query executes fine, the problem i guess is with COGDMOD.ini or some other conf file which COGNOS server is using to build the sql query for execution.

[MFGF]

Hi,

I get the same issue when trying to use trim() in a Cognos SQL query. In my case I tried it against SQL Server and MS Access databases - it fails with similar errors for both.

You might want to log this with IBM Support?

MF.

[LEO]

hi,

I checked for the logging of the driver.

We are getting the queries that are going.

Original query being run: select PLSOUR,LTRIM(PLSOUR) from APL

SQL query with Successful LTRIM() function calls are generating the below statement in log.

8/7/2013  9:32:08.53 AM    ODBC              32-bit  P=BFC  T=1BC  input statement text is: select "PLSOUR", LTRIM("PLSOUR") from "APL".len: 108


Original query being run: select PLSOUR,TRIM(PLSOUR) from APL

SQL query with UnSuccessful TRIM() function calls are generating the below statement in log.

8/7/2013  9:55:56.49 AM    ODBC              32-bit  P=BFC  T=1BC  input statement text is: select "PLSOUR", trim_both_spaces("PLSOUR") from "APL".len: 130

It's not clear from where it is converting TRIM() to "trim_both_spaces" in the query.