DIALECT

Enables SQL dialect compatibility features for HCOSS in the OpenESQL preprocessor, turning on automatic mapping of functions, special registers, data types, and all other automatic HCOSS conversions, and enabling the use of DIALECT statement prefixes. Converts DB2 SQL statements that are not compatible with SQL Server into equivalent T-SQL statements in most cases.

Syntax:

DIALECT={MAINFRAME | MIXED | NONE} [overrides]

Parameters:

MAINFRAME

All statements that are compatible with DB2 are converted by HCOSS
Unqualified table and view specifications in DB2-compatible statements can be qualified by the SQL(QUALIFIER) directive option
Supports the [NODIALECT] and [MAINFRAME] statement prefixes
When used with the [NODIALECT] statement prefix, conversion and qualification are disabled

MIXED

Performs no DB2 syntax conversion or table and view qualifications
Qualifications can be enabled for individual statements using the [MAINFRAME] statement prefix

NONE

Equivalent to not specifying DIALECT
Performs no DB2 syntax conversions or table and view qualifications

overrides

SQL compiler directives, set to values other than their default values set by DIALECT, each separated by a space. The modified SQL compiler directives specified override the directives set as defaults by DIALECT.

Properties:

Default:

DIALECT=NONE

Dependencies:

Requires that DBMAN be set to ODBC.

When set to MAINFRAME, DIALECT sets the following SQL compiler directive options:

CHECK
CHECKCALLPARAMS
CHECKSINGLETON
CHECKUPDCURSOR
DATE=ISO
DESCRIBEDTCHAR
DESCRIBEVARCHAR49
DETECTDATE=CLIENT
MARS
STRICTPARSE
TIME=ISO

When set to MIXED, DIALECT sets the following SQL compiler directive options:

CHECK
CHECKSINGLETON
CHECKUPDCURSOR
DATE=ISO
DESCRIBEDTCHAR
DESCRIBEVARCHAR49
DETECTDATE=CLIENT
MARS
TIME=ISO

When set to MAINFRAM or MIXED, DIALECT sets the OpenESQL preprocessor to skip over the following EXEC SQL statements, generating a compile-time warning for each instead:

SET CURRENT APPLICATION ENCODING SCHEME
SET CURRENT DEGREE
SET CURRENT [LOCALE] LC_CTYPE
SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
SET CURRENT OPTIMIZATION HINT
SET CURRENT PRECISION
SET CURRENT REFRESH AGE
SET CURRENT RULES
SET CURRENT ENCRYPTION PASSWORD

Scope:

Used at compile time:	Yes
Behavior at run time:	Source file

See Scope - HCOSS SQL Compiler Directive Options for more information.

Advantages:

You do not need to convert DB2 statements to T-SQL.
You can change the default schema when a program is bound, or in some cases, change the default schema at compile time using the QUALIFIER SQL compiler directive option.

Comments:

Both DIALECT=MAINFRAME and DIALECT=MIXED accept both [NODIALECT] and [MAINFRAME] statement prefixes. Using these in the appropriate combinations enables you to effectively switch between the two DIALECT settings on the statement level in a single application. However, you must remove the [NODIALECT] and [MAINFRAME] prefixes from the code compiling with DIALECT=NONE.

You can override the CHECK directive by specifying NOCHECK, or by setting the DB directive to identify your database connection. If the connection you specify does not use Windows authentication, then you must also set the PASS directive specifying a valid user ID and password for the connection.

In the context of HCOSS, CHECK verifies the following:

HCOSS-converted SQL syntax, to determine if the converted syntax is compatible with SQL Server. This provides early detection of incompatibilities between DB2 and SQL Server that have not been resolved by HCOSS.
converted database object names (tables, views, columns and functions) are valid in SQL Server.

To fully enable compile-time checking of converted SQL statements using SQL Server, set the DB, PASS and QUALIFIER directives along with DIALECT=MAINFRAME or DIALECT=MIXED.

When DIALECT=MAINFRAME and more rows are returned from the database than the statement in the application is able to accept, then HCOSS returns:

SQLCODE = -811 SQLSTATE = 21000 SQLWARN4 = W

Example:

SQL(DIALECT=MAINFRAME NOCHECK NODETECTDATE)