SQL Statement Prefixes for DIALECT Directive

DIALECT statement prefixes enable you to specify DB2 to SQL Server conversion details for individual EXEC SQL statements, including SQL Server statements that conform to DB2 syntax. In this way, you can apply certain overrides and conversion routines to DB2-compatible SQL Server statements, such as qualification of unqualified tables and views, on an as-needed basis.

Over time, your migrated applications can evolve to use syntax that is specific to SQL Server rather than DB2. When compiling an application using the DIALECT=MAINFRAME compiler directive option, by default HCOSS assumes all statements are DB2 statements and converts them to SQL Server statements using the specifications provided by defined HCOSS data type mappings, SQL name mappings, and SQLCODE mappings. HCOSS also applies qualifiers for unqualified tables and views based on the use of the SQL(QUALIFIER) compiler directive option. Also by default, HCOSS applies no conversions to existing SQL Server statements, submitting them directly to the SQL Server database.

When compiling with DIALECT=MIXED, the conversions described above are only performed for statements that have a prefix of [MAINFRAME], and HCOSS assumes that by default all statements are SQL Server statements.

The choice between DIALECT=MAINFRAME and DIALECT=MIXED depends on whether the majority of statements should be processed as DB2 or SQL Server statements.

Syntax:

A statement prefix is coded directly into an EXEC SQL statement as follows:
EXEC SQL [statementPrefix]... SQLStatement END-EXEC

Parameters:

statementPrefix
[MAINFRAME]
The SQLStatement is converted and qualified by HCOSS.
[NODIALECT]
The SQLStatement is neither converted nor qualified by HCOSS.
SQLStatement
Any DB2 statement, or DB2-compatible SQL Server statement.

Dependencies:

Statement prefixes must be used in conjunction with the DIALECT compiler directive option.

Examples:

All of the following examples assume that DIALECT is set to either MAINFRAME or MIXED, and all SQLStatements are DB2 compatible.

Example 1

The QUALIFIER compiler directive option is set.

EXEC SQL [MAINFRAME] SQLStatement END-EXEC

The SQLStatement is converted and qualified by HCOSS.

Example 2

The QUALIFIER compiler directive option is set.

EXEC SQL [NODIALECT] SQLStatement END-EXEC

The SQLStatement is neither converted nor qualified by HCOSS.