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 or the QUALIFIER parameter on BIND PACKAGE and BIND PLAN commands
- Supports the
[NOPLAN],
[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
- Supports only the [NOPLAN] statement prefix
- 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.
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)