7.1  Differences and similarities in Data Manipulation 

Language

Modifying the application to work with DB2 UDB can be a significant task in the 

conversion process. While a large portion of this step may encompass changing 

code to work with a different development environment, it is likely that additional 

time will be spent on testing the resulting code.

7.1.1  SELECT syntax

This section focuses on the SELECT statement syntax as it is supported in 

MySQL, and attempts to show how MySQL extensions to the SQL standard 

might be implemented in DB2 Version 8. Example 7 1 shows the MySQL syntax 

for the 

SELECT

 statement. A discussion of individual keywords follows.

Example 7 1   MySQL SELECT syntax

SELECT [STRAIGHT_JOIN]

[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]

[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]

[DISTINCT | DISTINCTROW | ALL]

select_expression,...

[INTO {OUTFILE | DUMPFILE}  file_name  export_options]

[FROM table_references

[WHERE where_definition]

[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...

[WITH ROLLUP]]

[HAVING where_definition]

[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]

[LIMIT [offset,] row_count | row_count OFFSET offset]

[PROCEDURE procedure_name(argument_list)]

[FOR UPDATE | LOCK IN SHARE MODE]]

The 

STRAIGHT_JOIN 

keyword forces the MySQL optimizer to join tables in the 

specified order. In DB2 the join order is always determined by the optimizer. The 

optimizer choices can be limited by changing the default query optimization class 

using SET CURRENT QUERY OPTIMIZATION to a lower level. This may still not 

force the optimizer to evaluate the join order as given in the SQL statement. 

However, the DB2 cost based optimizer usually chooses the best access path 

for a given query. For additional information see 9.5.5,  SQL execution plan  on 

page 308.

The options prefixed with 

SQL_ 

are MySQL specific and do not require a DB2 

equivalent.

156 

MySQL to DB2 UDB Conversion Guide