SQL Statement Prefixes for DIALECT Directive

Restriction: This topic applies to Windows environments only.

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 or the QUALIFIER parameter on a BIND PACKAGE or BIND PLAN command. 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

[NOPLAN]: The SQLStatement is not written to a DBRM even if SQL(DBRMLIB) is specified, but instead treats the statement as dynamic SQL at runtime. When used with other prefixes, [NOPLAN] must be specified first.
[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 not set, and there is no specified QUALIFIER parameter.

EXEC SQL [NOPLAN] SQLStatement END-EXEC

The SQLStatement is converted but not qualified by HCOSS; it is not written to a DBRM, not written to a bind script file even when the BIND compiler directive option is set, and not used to create a stored procedure at compile time even when ACCESS compiler directive option is set.

Example 2

The QUALIFIER compiler directive option is set.

EXEC SQL [MAINFRAME] SQLStatement END-EXEC

The SQLStatement is converted and qualified by HCOSS.

Example 3

The QUALIFIER compiler directive option is set.

EXEC SQL [NOPLAN][MAINFRAME] SQLStatement END-EXEC

The SQLStatement is converted and qualified by HCOSS but is not written to a DBRM.

Example 4

The QUALIFIER compiler directive option is set.

EXEC SQL [NODIALECT] SQLStatement END-EXEC

The SQLStatement is neither converted nor qualified by HCOSS.

Example 5

The BIND or DBRMLIB compiler directive is set, along with the QUALIFIER compiler directive.

EXEC SQL [NOPLAN][NODIALECT] SQLStatement END-EXEC

The SQLStatement is neither converted nor qualified by HCOSS and is not written to a bind file or DBRM.