Another Cognos Data Manager Refresh Issue

[gatorfe]

Recently we created a new Cognos 10.2.1 BI test environment. In production we run Cognos BI 10.1.1. We use Data Manager 10.1 in production and installed 10.2.1 in the test environment. We use MS SQL 2008 R2 for our DW. We are extracting data from a DB2 ERP System. We have two companies and have a separate DM catalogs for each.  Both catalogs extract from the same DB2 ERP System. We moved over a copy of our 2 prod sql dbs to the test environment. When we run a refresh for one of the tables in one of the catalogs it gets the following message:

"DM-DBM-0400 UDA driver reported the following on connection SOURCE:

UDA-SQL-0043 The underlying database detected an error during processing the SQL request.

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword CAST not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE."

I only get this in one of the catalogs, the other catalog works.  The syntax is the same for the build between the two catalogs.  I don't get this message in the production environment when I refresh the same table for the specific catalog.  All other tables in both catalogs refresh fine in the test environment.  Has anyone else ever experienced this behavior?  Attached is the log that shows the error.

Thank you in advance for any insight!

[MFGF]

Recently we created a new Cognos 10.2.1 BI test environment. In production we run Cognos BI 10.1.1. We use Data Manager 10.1 in production and installed 10.2.1 in the test environment. We use MS SQL 2008 R2 for our DW. We are extracting data from a DB2 ERP System. We have two companies and have a separate DM catalogs for each.  Both catalogs extract from the same DB2 ERP System. We moved over a copy of our 2 prod sql dbs to the test environment. When we run a refresh for one of the tables in one of the catalogs it gets the following message:

"DM-DBM-0400 UDA driver reported the following on connection SOURCE:

UDA-SQL-0043 The underlying database detected an error during processing the SQL request.

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0199 - Keyword CAST not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH ORDER UNION EXCEPT OPTIMIZE."

I only get this in one of the catalogs, the other catalog works.  The syntax is the same for the build between the two catalogs.  I don't get this message in the production environment when I refresh the same table for the specific catalog.  All other tables in both catalogs refresh fine in the test environment.  Has anyone else ever experienced this behavior?  Attached is the log that shows the error.

Thank you in advance for any insight!

What happens if you open the data source of the build in question and use SQL Helper to run the query? Do you get an error there? Are you sure the same query is being run on each machine? What happens if you copy the SQL into SQLTerm in your 10.1.1 DM Designer and run it? Do you get an error there?

Are you running the same ODBC versions on each server?

MF.