ODIADS001 - DATE and DATETIME data types

Informix provides two data types to store date and time information:

• DATE = for year, month and day storage.
• DATETIME = for year to fraction of a second (1-5) storage.

Genero DB provides four data types to store date and time information:

• DATE = for year, month, and day storage.
• TIME = for hour, minute, and second storage.
• TIMESTAMP = for year, month, day, hour, min, second, and fraction of second storage.
• DATETIME = Synonym for TIMESTAMP.

String representing date time information:

Informix is able to convert quoted strings to DATE / DATETIME data, if the string formatting matches the formatting set by environment parameters (i.e. DBDATE, GL_DATETIME).

Genero DB can also convert quoted strings to DATE / DATETIME, however note that the format for dates is 2005-01-30, not 30/01/2005. Review your code for handling of quoted dates.

Date arithmetic:

• Informix supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates or times is a number of days when only DATEs are used, and an INTERVAL value if a DATETIME is used in the expression.
• In Genero DB, the result of an arithmetic expression involving DATE values is a NUMBER of days, with the decimal part representing the fraction of the day ( 0.5 = 12H00, 2.00694444 = (2 + (10/1440)) = 2 days and 10 minutes ) ).
• Informix automatically converts an integer to a date when the integer is used to set a value of a date column. Genero DB does not do this conversion: review your code and change to use a DATE type variable.
• Complex DATETIME expressions ( involving INTERVAL values for example ) are Informix specific and have no equivalent in Genero DB.

Solution:

The Genero DB DATE type is used for Informix DATE data. 

Informix DATETIME data with a precision from HOUR TO SECOND are stored in a Genero DB TIME column. DATETIME data with any other precision is stored in Genero DB TIMESTAMP columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the Genero DB DATETIME value will be "1900-01-01 11:45:00".

Warning: Using integers (number of days since 1899/12/31) as dates is supported by Genero DB in a SELECT INTO but not in a WHERE. Check your code to detect where you are using integers with DATE columns. Also note that SELECT TO_NUM('1900-01-01' AS INT)  will return 0 and not 1. (With Informix & Genero DB, a date of 1900/1/1 when selected into an INTEGER will return 1.)

Warning: Literal DATETIME expressions (i.e. DATETIME 1999-10-12 YEAR TO DAY) are not converted.

Warning: It is strongly recommended that you use BDL variables in dynamic SQL statements instead of quoted strings representing DATEs. For example:
   LET stmt = "SELECT ... FROM customer WHERE create_date >'", adate,"'"
is not portable.  Use a question mark place holder instead and OPEN the cursor by USING adate:
   LET stmt = "SELECT ... FROM customer WHERE create_date > ?"

Note: Most arithmetic expressions involving dates (for example, to add or remove a number of days from a date) will produce the same result with Genero DB.

Warning: DATE arithmetic expressions using SQL parameters (USING variables) are not fully supported. For example: "SELECT ... WHERE datecol < ? + 1" generates an error at PREPARE time.

Warning: SQL Statements using expressions with EXTEND must be reviewed and adapted to the native syntax.

ODIADS003 - Reserved words

SQL object names, like table and column names, cannot be SQL reserved words in Genero DB.

 An example of a common word which is part of the Genero DB SQL grammar is 'level'

Solution:

You must rename those table or column names which are Genero DB reserved words. Genero DB reserved keywords are listed in the Genero DB documentation. 

ODIADS004 - ROWIDs

Genero DB provides ROWIDs, but the data type is different. Informix ROWIDs are INTEGERs, while Genero DB ROWIDs are BIGINT.

Warning: Genero DB ROWIDs can be used to identify a unique row during the life time of the transaction. After the transaction is committed, the ROWID may change.

With Informix, SQLCA.SQLERRD[6] contains the ROWID of the last INSERTed or UPDATEd row. This is not currently supported with Genero DB.

Solution:

It is recommended to review code and remove usage of ROWIDs, as their use is not portable to other databases and may lead to problems running the code against any other databases. (For example, Oracle has ROWIDs, but they are CHARs and not numeric.)

ODIADS005 - SERIAL data type

Informix SERIAL data type and automatic number production:

• The table column must be of type SERIAL.
• To generate a new serial, no value or a zero value is specified in the INSERT statement:
     INSERT INTO tab1 ( c ) VALUES ( 'aa' )
     INSERT INTO tab1 ( k, c ) VALUES ( 0, 'aa' )
• After INSERT, the new SERIAL value is provided in SQLCA.SQLERRD[2].

Informix allows you to insert rows with a value different from zero for a serial column. Using an explicit value automatically increment the internal serial counter, to avoid conflicts with future INSERTs that are using a zero value:
    CREATE TABLE tab ( k SERIAL );  --> internal counter = 0
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 1
    INSERT INTO tab VALUES ( 10 );  --> internal counter = 10
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 11
    DELETE FROM tab;                --> internal counter = 11
    INSERT INTO tab VALUES ( 0 );   --> internal counter = 12

Genero DB supports SERIAL the same as Informix.

Solution:

When using Genero DB, the SERIAL data type works the same as Informix. After an insert, sqlca.sqlerrd[2] holds the last generated serial value.

CREATE [TEMP] TABLE with a SERIAL column works as Informix.

ODIADS006 - Outer joins

The Genero DB syntax for OUTER joins is different from Informix:

In Informix SQL, outer tables are defined in the FROM clause using the OUTER keyword:

SELECT ... FROM a, OUTER(b)
 WHERE a.key = b.akey

SELECT ... FROM a, OUTER(b,OUTER(c))
 WHERE a.key = b.akey
   AND b.key1 = c.bkey1
   AND b.key2 = c.bkey2 

Genero DB supports ANSI syntax joins.

SELECT ... FROM a, LEFT OUTER JOIN b ON a.key = b.key

SELECT ... FROM a 
    LEFT OUTER JOIN  b LEFT OUTER JOIN c
    ON ( (b.key1 = c.bkey1) AND (b.key2 = c.bkey2) ) ON ( (a.key = b.akey) )

This is the preferred way to assign a schema to DB users.

You can also make the database interface select the current schema automatically with the following FGLPROFILE entry:

ODIADS008a - Data consistency and concurrency management

Data consistency involves readers that want to access data currently being modified. Concurrency data access involves several writers accessing the same data for modification. Locking granularity defines the amount of data concerned when a lock is set (row, page, table, and other groupings).

Informix

Informix uses a locking mechanism to handle data consistency and concurrency. When a process changes database information with UPDATE, INSERT or DELETE, an exclusive lock is set on the touched rows. The lock remains active until the end of the transaction. Statements performed outside a transaction are treated as a transaction containing a single operation and therefore release the locks immediately after execution. SELECT statements can set shared locks according to the set isolation level. In case of locking conflicts (for example, when two processes want to acquire an exclusive lock on the same row for modification or when a writer is trying to modify data protected by a shared lock), the behavior of a process can be changed by setting the lock wait mode.

Control:

• Lock wait mode: SET LOCK MODE TO ...
• Isolation level: SET ISOLATION TO ...
• Locking granularity: CREATE TABLE ... LOCK MODE {PAGE|ROW}
• Explicit exclusive lock: SELECT ... FOR UPDATE

Defaults:

• The default isolation level is READ COMMITTED.
• The default lock wait mode is NOT WAIT.
• The default locking granularity is PAGE.

Genero DB

Genero DB does not use the same locking mechanism as Informix to handle concurrency, however it behaves the same way in terms of concurrency.

Informix-specific transaction control instructions have been implemented in Genero DB.

Solution:

You can use the same transaction control instructions and update clauses as Informix:

• SET LOCK MODE ...
• SET ISOLATION / SET TRANSACTION ISOLATION
• SELECT ... FOR UPDATE

Warning: There is a little difference when using SET LOCK MODE TO WAIT n with Genero DB: WAIT n is the same as NOT WAIT.

Warning: The LOCK MODE {PAGE|ROW} is not provided by Genero DB. This is specific to data storage mechanisms and cannot be supported in the Genero DB concurrency model.

ODIADS008b - SELECT FOR UPDATE

Many BDL programs implement pessimistic locking in order to prevent several users editing the same rows at the same time.

  DECLARE cc CURSOR FOR
     SELECT ... FOR UPDATE [OF col-list]
  OPEN cc
  FETCH cc <-- lock is acquired
  CLOSE cc <-- lock is released

• The row must be fetched in order to set the lock.
• If the cursor is local to a transaction, the lock is released when the transaction ends.
  If the cursor is declared "WITH HOLD", the lock is released when the cursor is closed.

Genero DB allows individual and exclusive row locking with:

  SELECT ... FOR UPDATE

• A lock is acquired for each selected row when the cursor is opened (before the first fetch).
• The lock is only released when the transaction ends.
• FOR UPDATE cursors can only be OPENed inside a transaction.

Genero DB locking granularity is at the row level.

To control the behavior of the program when locking rows, Informix provides a specific instruction to set the wait mode:

   SET LOCK MODE TO { WAIT | NOT WAIT | WAIT seconds }

The default mode is NOT WAIT. This is an Informix-specific SQL statement.

Solution:

Genero DB supports SELECT .. FOR UPDATE as Informix, but the rows are locked when the cursor is opened, not when the first row is fetched as Informix.

Ensure that the use of 'FOR UPDATE' is always inside a transaction.

Ensure that you COMMIT the transaction as soon as possible to prevent rows being locked longer than necessary.

ODIADS009a - Transactions handling

Informix and Genero DB handle transactions in a similar manner but with minor differences.

In both Informix and Genero DB, transactions must be started with BEGIN WORK and finished with COMMIT WORK or ROLLBACK WORK.

Statements executed outside of a transaction are automatically committed.

Warning: With Informix in native mode (non-ANSI), DDL statements can be executed (and cancelled) in transactions. Genero DB does not support DDL statements inside transactions.

Solution:

Regarding transaction control instructions, existing applications do not have to be modified in order to work with Genero DB.

Warning: You must extract the DDL statements from transaction blocks.

Transactions in stored procedures: avoid using transactions in stored procedures and allow the client applications to handle transactions, in accordance with the transaction model.

See also ODIADS008b

ODIADS009b - Avoiding long transactions

Some BDL applications do not care about long transactions because they use an Informix database without transaction logging (transactions are not stored in log files for potential rollbacks).

With Genero DB, using transactions is mandatory. Every database modification is stored in a log file.

BDL applications must be written to avoid long transactions when connected to a database with transaction logging enabled.

Solution:

You must review the program logic in order to avoid long transactions.

ODIADS010 - BOOLEAN data type

Informix provides the BOOLEAN data type, as a "Built-in Opaque Data Type". It is used to store boolean values. You can use the character literals 't' for true and 'f' for false.

Genero DB 3.4 does not have a BOOLEAN type.

Solution:

You must review the database creation scripts and the programs. Replace any BOOLEAN column by a CHAR(1).

ODIADS011a - CHARACTER data types

Informix provides the CHAR and VARCHAR data types to store characters. CHAR columns can store up to 32,767 characters; VARCHARs are limited to 255 characters. Starting with IDS 2000, Informix provides the LVARCHAR data type which is limited to 2K characters.

Genero DB provides the CHAR and VARCHAR data types. Both data types support a length of 60000 (or 3000 if the column is indexed). 

String comparison semantics are equivalent in Informix and Genero DB:

• Trailing blanks are ignored when comparing CHAR and VARCHAR values.
• Genero DB treats empty strings as NOT NULL values (like Informix).

Solution:

The database interface supports character string variables in SQL statements for input (BDL USING) and output (BDL INTO).

ODIADS013 - Triggers and Stored Procedures

Genero DB supports the Informix trigger and stored procedure language.

See Genero DB documentation for more details.

ODIADS016a - Defining database users

Informix users are defined at the operating system level. They must be members of the 'Informix' group. The database administrator must grant CONNECT, RESOURCE or DBA privileges to those users.

Solution:

For migration and testing purposes only, you can specify the user name and password in the FGLPROFILE.

For a live system, it is recommended to use the CONNECT TO statement and supply the user name and password, or create database users IDENTIFIED EXTERNALLY. 

ODIADS016b - Setting privileges

Informix and Genero DB user privileges management are similar.

Genero DB provides roles to group privileges which then can be assigned to users. Starting with version 7.20, Informix also provides roles.

Informix users must have at least the CONNECT privilege to access the database:
    GRANT CONNECT TO (PUBLIC|user)

To be able to create tables, Genero DB users need:
    GRANT CREATE TABLE TO <user>

Warning: Informix database privileges do NOT correspond exactly to Genero DB CONNECT, RESOURCE and DBA roles. However, roles can be created with equivalent privileges.

Solution:

Check the Genero DB documentation for a full list of GRANT options available.

ODIADS017 - Temporary tables

Genero DB supports Informix temporary table creation statements:

Solution:

Since Genero DB supports the same temporary table instructions as Informix, there is nothing to do.

ODIADS018 - Substrings in SQL

Informix SQL statements can use substrings on columns defined with the character data type:
    SELECT ... FROM tab1 WHERE col1[2,3] = 'RO'
    SELECT ... FROM tab1 WHERE col1[10]  = 'R'   -- Same as col1[10,10]
    UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
    SELECT ... FROM tab1 ORDER BY col1[1,3]

Warning: In UPDATE instructions, setting column values using subscripts will produce an error with Genero DB:
    UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
is converted to:
    UPDATE tab1 SET SUBSTRING(col1 FROM 2 FOR 3-2+1) = 'RO' WHERE ...

  PREPARE pre FROM "SELECT USER"
  EXECUTE pre INTO l_user