The 

DISTINCTROW 

keyword is as a synonym for 

DISTINCT 

which is supported by 

DB2.

The 

INTO {OUTFILE | DUMPFILE}  file_name  export_options

 selection allows you to 

write data to an outfile quickly without invoking the mysqldump utility. The DB2 

command line processor allows you to direct the output of any SELECT 

statement to an operating system file.

The 

LIMIT [offset,] row_count | row_count OFFSET offset 

keyword translates to 

FETCH FIRST n ROWS ONLY in DB2. An offset to retrieve rows needs to be 

implemented through the WHERE clause if possible.

With the 

SQL_SMALL_RESULT 

or 

SQL_BIG_RESULT 

the query developer can hint to the 

SQL optimizer the size of the expected result set, and therefore influences the 

optimizer access strategy. Example 7 2 shows how the optimizer hint works.

Example 7 2   MySQL SELECT with optimizer hint

mysql>

mysql> select sql_small_result * from t1;

mysql>

DB2 UDB has a similar operator to guide SQL optimizer decisions with a different 

syntax as shown in Example 7 3. Please note that the number of rows is 

exemplary.

Example 7 3   DB2 SELECT with optimizer hit

db2 => select * from t1 optimize for 2 rows

DB20000I  The SQL command completed successfully.

7.1.2  JOIN syntax

The join capabilities of a commercial and industrial strength database 

management system are one of the most significant functions. MySQL supports 

the linguistic elements for JOIN as shown in Example 7 4. Various aspects of 

JOINs are discussed below.

Example 7 4   MySQL JOIN Syntax

table_reference, table_reference

table_reference [INNER | CROSS] JOIN table_reference [join_condition]

table_reference STRAIGHT_JOIN table_reference

table_reference LEFT [OUTER] JOIN table_reference [join_condition]

table_reference NATURAL [LEFT [OUTER]] JOIN table_reference

{OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }

 Chapter 7. Application porting 

157