Comparing operators

There is a plethora of function as listed in Table 7 5, Table 7 6, and Table 7 7 

where syntax or implementation can be quite different. The tables attempt to 

summarize these differences and show code snippets for some selected 

functions on how to convert a MySQL function to DB2. In many cases you will 

find the DB2 CASE expression is really helpful when function mapping is 

required.

Table 7 5   MySQL and DB2 UDB operator comparison

MySQL

DB2

Comments

logical NOT as  !  in 

VALUES CASE WHEN 

Implement using CASE 

SELECT list

1!=1 THEN 0 ELSE 1 END

expression and VALUES 

statement

%

MOD

In MySQL % is a synonym 

for modulo

& (bitwise and)

not available. Implement 

Refer to UDF BIT_AND in 

using UDF

Appendix A.1,  Sample 

code for BIT_AND  on 

page 336

logical AND as  &&  in 

CASE

Implement using CASE 

SELECT list

expression and VALUES 

statement

not equal, <> or != in 

SELECT CASE WHEN 

Implement using CASE 

SELECT list: 

1<> 1 THEN x ELSE y 

expression

select 1<>1

END

Function = in

SELECT list:

CASE

Implement using CASE 

select (1=1)

expression and VALUES 

statement

Functions converting date and time

Working with date and time functions and operators uncovers in many cases the 

implementation differences for the various DBMS. Table 7 6 lists some date and 

time related functions. You may also find such functions in the SQL92 and ODBC 

standards.

Table 7 6   Date and Time related functions

MySQL

DB2 UDB

Comments

DATE_FORMAT

various options such as 

Formatting DATE, TIME 

DAYNAME, 

data types

MONTHNAME, etc.

170 

MySQL to DB2 UDB Conversion Guide