Error 42601 in UDB

[Kpganesh]

We have a front end application that accesses DB2 via UDB. When the following query is executed from the front end application,

SELECT * FROM TABLE1 WHERE USER != 'USER1';

we get the error message

'com.bt.cfb.exception.CFBSystemException: Unexpected SQL error while accessing TABLE1 tableCOM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "!" was found following "". Expected tokens may include: "< >
= <> <= ]< ]> ]= >= ^< ^> ^= IN NOT ". SQLSTATE=42601'

which says that the character '!' is not supported. But the same query ran fine in the test machine with the same UDB specifications.

Can someone please tell why this is not working in one UDB while it works fine in another.

 

[juliane26]

well, != is officially no valid SQL.
Which version of DB2 oh which platform where you using when it worked, and what is the version/platform now.

See also

http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0000746.htm

There is a note:

1. The following forms of the comparison operators are also supported in basic and quantified predicates: ^=, ^<, ^>, !=, !<, and !>. In code pages 437, 819, and 850, the forms ¬=, ¬<, and ¬> are supported.

All of these product-specific forms of the comparison operators are intended only to support existing SQL that uses these operators, and are not recommended for use when writing new SQL statements.

So e.g when moving to zOS, they do not support such predicates.
If you keep to standard SQL, you won't have this problem.
Still - the SQL standard coverage is not the same on zOS and LUW, in specific cases like BI functions check the SQL reference.

Juliane