SQL Statement Prefixes for DIALECT Directive

DIALECT statement prefixes enable you to specify DB2 to PostgreSQL conversion details for individual EXEC SQL statements, including PostgreSQL statements that conform to DB2 syntax. In this way, you can apply certain overrides and conversion routines to DB2-compatible PostgreSQL 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 PostgreSQL rather than DB2. When compiling an application with the DIALECT directive set to MAINFRAME and the TARGETDB directive set to one of POSTGRESQL, SGSQLAWS, or PGSQL, by default HCOPG assumes all statements are DB2 statements and converts them to PostgreSQL statements using the specifications provided by defined HCOPG data type mappings, SQL name mappings, and SQLCODE mappings. HCOPG also applies qualifiers for unqualified tables and views based on the use of the SQL(QUALIFIER) compiler directive option. Also by default, HCOPG applies no conversions to existing PostgreSQL statements, submitting them directly to the PostgreSQL database.

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

The choice between DIALECT=MAINFRAME and DIALECT=MIXED depends on whether the majority of statements should be processed as DB2 or PostgreSQL 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 HCOPG.
[NODIALECT]: The SQLStatement is neither converted nor qualified by HCOPG.

SQLStatement

Any DB2 statement, or DB2-compatible PostgreSQL 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 HCOPG.

Example 2

The QUALIFIER compiler directive option is set.

EXEC SQL [NODIALECT] SQLStatement END-EXEC

The SQLStatement is neither converted nor qualified by HCOPG.