IBM Informix emulation parameters in FGLPROFILE

Emulation of Informix® specific SQL features can be controlled with FGLPROFILE entries.

What are Informix SQL emulation settings used for?

To simplify the migration process to other database servers such as IBM® Informix, the database drivers can emulate some IBM Informix-specific features like SERIAL columns and temporary tables; the drivers can also do some SQL syntax translation.

Avoid using IBM Informix emulations; write portable SQL code instead. IBM Informix emulations are only provided to help you in the migration process. Disabling IBM Informix emulations improves performance, because SQL statements do not have to be parsed to search for IBM Informix-specific syntax.

Emulations can be controlled with FGLPROFILE parameters. You can disable all possible switches one-by-one, in order to test your programs for SQL compatibility.

`dbi.database.dsname.ifxemul`

This is a global switch to enable or disable IBM Informix emulations.

Values can be true or false. Default is true.

dbi.database.stores.ifxemul = false

`dbi.database.dsname.ifxemul.datatype.type`

The 'ifxemul.datatype' switches define whether the specified data type must be converted to a native type (for example, when creating a table with the CREATE TABLE statement).

Where type can be one of char, varchar, datetime, decimal, money, float, real, integer, smallint, serial, text, byte, bigint, bigserial, int8, serial8, boolean.

Default is true for all types.

dbi.database.stores.ifxemul.datatype.serial = false

`dbi.database.dsname.ifxemul.datatype.serial.emulation`

This parameter can be used to control the serial generation technique used by the driver to generate auto-incremented values.

The value can be one of the following:

native uses the database's native sequence generator directly in the table definitions (depends on the db type).
native2 uses a secondary native sequence generator directly in the table definitions (depends on the db type).
regtable uses the SERIALREG table with triggers. It is slower than the native emulation.
trigseq", uses database sequence generator with triggers (not supported by all drivers).

Default is "native".

dbi.database.stores.ifxemul.datatype.serial.emulation = "native"

Serial emulations depend on the type of database server used. See SQL database guides for more details.

`dbi.database.dsname.ifxemul.datatype.serial.sqlerrd2`

Use this parameter to disable the automatic serial retrieval done by the drivers, to fill the SQLCA.SQLERRD[2] register. When this feature is enabled, depending on the type of serial emulation, drivers need to execute an additional SQL query to fetch the last generated serial. If you have implemented your own function to fetch the last serial and you cannot disable the whole serial emulation, set this parameter to false.

Default is true.

dbi.database.stores.ifxemul.datatype.serial.sqlerrd2 = false

See Disabling automatic serial retrieval for SQLCA.SQLERRD[2] for more details.

`dbi.database.dsname.ifxemul.temptables`

This switch can be used to control temporary table emulation.

Defaults is true.

dbi.database.stores.ifxemul.temptables = false

`dbi.database.dsname.ifxemul.temptables.emulation`

This parameter can be used to specify what technique must be used to emulate temporary tables in the database server.

Possible values are "default", "private" and "global".

dbi.database.stores.ifxemul.temptables.emulation = "global"

See SQL database guides for more details.

`dbi.database.dsname.ifxemul.dblquotes`

This switch can be used to define whether double quoted strings must be converted to single quoted strings.

Default is true.

dbi.database.stores.ifxemul.dblquotes = false

If this emulation is enabled, all double quoted strings are converted, including database object names.

`dbi.database.dsname.ifxemul.outers`

This switch can be used to control IBM Informix OUTER translation to native SQL outer join syntax.

Default is true.

dbi.database.stores.ifxemul.outers = false

Note: Consider using standard ISO outer joins in your SQL statements (LEFT OUTER).

`dbi.database.dsname.ifxemul.today`

This switch can be used to convert the TODAY keyword to a native expression returning the current date.

Default is true.

dbi.database.stores.ifxemul.today = false

`dbi.database.dsname.ifxemul.current`

This switch can be used to convert the CURRENT X TO Y expressions to a native expression returning the current time.

Default is true.

dbi.database.stores.ifxemul.current = false

`dbi.database.dsname.ifxemul.selectunique`

This switch can be used to convert the SELECT UNIQUE to SELECT DISTINCT.

Default is true.

dbi.database.stores.ifxemul.selectunique = false

Note: Consider replacing all UNIQUE keywords by DISTINCT.

`dbi.database.dsname.ifxemul.colsubs`

This switch can be used to control column substrings expressions (col[x,y]) to native substring expressions.

Default is true.

dbi.database.stores.ifxemul.colsubs = false

Note: Consider using substring SQL functions instead of [x,y] expressions in SQL.

`dbi.database.dsname.ifxemul.matches`

This switch can be used to define whether MATCHES expressions must be converted to LIKE expressions.

Default is true.

dbi.database.stores.ifxemul.matches = false

Note: Consider using LIKE expressions instead of MATCHES in SQL.

`dbi.database.dsname.ifxemul.length`

This switch can be used to define whether LENGTH() function names have to be converted to the native equivalent.

Default is true.

dbi.database.stores.ifxemul.length = true

`dbi.database.dsname.ifxemul.rowid`

This switch can be used to define whether ROWID keywords have to be converted to native equivalent (for example, OID in PostgreSQL).

Default is true.

dbi.database.stores.ifxemul.rowid = false

Note: Consider using primary keys instead of ROWIDs.

`dbi.database.dsname.ifxemul.listupdate`

This switch can be used to convert the UPDATE statements using non-ANSI syntax.

Default is true.

dbi.database.stores.ifxemul.listupdate = false

`dbi.database.dsname.ifxemul.extend`

This switch can be used to convert simple EXTEND() expressions to native date/time expressions.

Default is true.

dbi.database.stores.ifxemul.extend = true

`dbi.database.dsname.ifxemul.rowlimiting`

This switch can be used to convert Informix SQL row limiting clause

SELECT [SKIP n] FIRST
m

to the native SQL equivalent.

Note: The row limiting clause must not use SQL parameters. Only row limiting clauses using integer constants will be converted.

Default is true.

dbi.database.stores.ifxemul.rowlimiting = true

`dbi.database.dsname.ifxemul.nationalchars`

With some database brands, (in DDL statements only) this switch can be used to convert CHAR(n), VARCHAR(n), LVARCHAR(n) and TEXT data type names to the corresponding national character types of the target database. For example, with Microsoft™ SQL Server, the corresponding national character types are NCHAR(n), NVARCHAR(n) and NVARCHAR(MAX).

Default is false.

dbi.database.stores.ifxemul.nationalchars = true

Note: Consider the following:

The dbi.database.dsname.ifxemul.nationalchars parameter is by default false.
The current locale must be multibyte (typically UTF-8) to get this type name conversion.
The ifxemul.nationalchars switch is ignored, when the data type emulation parameter corresponding to the type (such as dbi.database.dsname.datatype.varchar) is set to false.

For more details, see CHAR/VARCHAR types with SQL Server and TEXT type with SQL Server.