Back to Contents

ODI Adaptation Guide For Genero db 3.6x, 3.8x

Runtime configuration

Database concepts

Data dictionary

Data manipulation

BDL programming

Runtime configuration

Install Genero db and create a database

1. Install Genero db on your computer.

   By default, Genero db is configured to run alone on a networked machine, having client applications hosted on other machines. Here are some tips to setup Genero db , you should change some configuration parameters:

   • If applications are co-located on the same machine as the database server: By default, Genero db uses nearly all the memory available on the computer. To share the memory with applications, you must change the MEMORY_OVERRIDE parameter in $ANTSHOME/Server/config.txt.

   • You can also use the networking=IPC communication protocol in the data source definition to get better performance.

   • For production use, a caching RAID controller on which to do logging is highly recommended. For maximum data integrity, LOG_MODE should be set to DURABLE, and LOGPATH should point to a different disk than the disk containing data. If LOGPATH does not point to a disk managed by a RAID controller, LOG_MODE should be set to OSDURABLE. See the database server documentation for more details.

   • Genero db by default relies on local-area-network multicast to enable clients and servers to find each other. If you have a firewall, you must allow UDP connections on port 12345 for the multicast address 255.0.0.37. If you want to disable multicast search: on the server side, set MULTICAST_ENABLED=FALSE in $ANTSHOME/Server/config.txt. On the client side, for Unix platforms, you must set overridebroadcast=yes in the ODBC data source definition. For Windows platforms, check the "Override Multicast" option in the ODBC data source configuration (click the "Advanced" button, then click "Connection Method" in the "Networking" section).

2. Set up an ODBC data source, called mydb.
   
   The defaults ODBC data source settings need to be adapted:

   • Set overridebroadcast=yes if you don't need multicast, as described in (1).

   • During the installation of Genero db, the data source might have been created with a login and password of the SYSTEM user. If you leave these default user and password entries, anyone can connect to the database as the SYSTEM super user. You must clean the user and password ODBC parameters. This will force client programs to specify a login and password to connect to the database. 

   • By default, the Genero db client re-connects automatically to the server if the connection is lost. This is a useful feature as long as the SQL session is stateless . However, if you create temporary tables or if you change session parameters with ALTER SESSION, the session context will be silently lost if a re-connect occurs. To get an SQL error when DB connection is lost, you can deny the client to re-connect by setting automaticfailover=no in the ODBC parameters. Note that the Genero db client does not re-connect automatically if a transaction was started. In such case the program gets and SQL error.

   • Typical Genero FGL applications connect only once to the database server. By default, the Genero db client uses connection aggregators, which re-use connection resources and server bindings for new connections. This is useful when the same client process opens and closes many connections, but is unnecessary overhead in typical Genero FGL applications.
     Additionally, using aggregation implies multithreading. UNIX signal handling are not thread safe. Since the runtime system uses the UNIX signal() function, you must disable aggregation by setting noaggregates=0 in the ODBC data source definition. On Windows platforms, the noaggregators property can only be changed with the registry editor, under the ODBC.INI key.

   • By default the ODBC client library uses a large buffer to pre-fetch rows from the server. This gives very good performance but consumes memory. If your application uses many cursors fetching large result sets, you can reduce the memory footprint with the fetchsize ODBC.INI parameter. On the other hand, the Genero db client maintains a statement pool by default. When you FREE a cursor or statement in your programs, the underlying ODBC statement handle structure goes to the statement pool for future reuse. You can disable the ODBC statement pool by setting the disableStmtPool ODBC.INI parameter to Yes. When this parameter is set, fetch buffers are  freed as well.

3. Create a database user dedicated to your application. You can use the antscmd tool.
   
       $ antscmd -d mydb -u SYSTEM -p SYSTEM
       mydb> CREATE USER appadmin IDENTIFIED BY "password";
   
   You must grant privileges to this user:
   
      mydb> GRANT CREATE TABLE TO appadmin;
      mydb> GRANT CREATE VIEW TO appadmin;
      mydb> GRANT CREATE SYNONYM TO appadmin;
      mydb> GRANT CREATE PROCEDURE TO appadmin;
      mydb> GRANT CREATE SEQUENCE TO appadmin;

4. Create the application tables.
   
   Do not forget to convert Informix data types to Genero db data types. See issue ODIADS100 for more details.
   
   Check for reserved words in your table and column names.

Prepare the runtime environment

1. If you want to connect to a remote Genero db server from an application server, you must have ODBC properly configured on your application server.

2. Verify if the ODBC environment is correct.
   
        $ antscmd -d dns-name -u appadmin -p password

3. Verify the environment variable defining the search path for shared libraries. On UNIX platforms, the variable is specific to the operating system. For example, on Solaris and Linux systems, it is LD_LIBRARY_PATH.

4. Check the database client character set (characterset ODBC parameter) The DB locale must match the locale used by the runtime system (LANG).

5. Set up the fglprofile entries for database connections to your data source.

6. Create normal application users and define the schema to be used.
   
   With Genero db, a schema is created when creating a user. If the APPADMIN user creates the tables, the schema for application tables will be "APPADMIN".
   
   In order to make application tables visible to normal DB users, you can specify a default schema for normal users by adding the DEFAULT SCHEMA clause in CREATE USER:
   
      mydb> CREATE USER username IDENTIFIED BY password DEFAULT SCHEMA appadmin;
   
   You can also use the following FGLPROFILE entry to make the database driver select a default schema after connection:
   
      dbi.database.dbname.ads.schema = "name"
   Here <dbname> identifies the database name used in the BDL program ( DATABASE dbname ) and <name> is the schema name to be used.
   
   If needed, database users can be authenticated as Operating System users. In order to create a DB user authenticated by the operating system, use the IDENTIFIED EXTERNALLY clause in CREATE USER:
   
      mydb> CREATE USER username IDENTIFIED EXTERNALLY;
   
   The OS users will be able to connect to the database if the $ANTSHOME/Server/ants.rhosts file contains an entry to identify the OS user. See the Genero db documentation for more details.
   
   Warning: Pay attention to the user name, which is case-sensitive. You must specify the user name in double quotes; otherwisem, the name defaults to uppercase letters.

7. Grant privileges to application users:
   
   By default the tables created by the appadmin user cannot be modified by the application users; you must first grant privileges:
   
      mydb> GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO username;
   
   You can do this for all existing and future users by specifying PUBLIC as the grantee:
   
      mydb> GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO PUBLIC;
   
   If the database has stored procedures, you must also grant execute permission to application users:
   
      mydb> GRANT EXECUTE ON procname TO username;
   
8. In order to connect to Genero db, you must have a database driver "dbmads*" installed.

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 / TIME / TIMESTAMP; by default, date/time formats follow the ISO standard (2005-01-30). You can control date/time format with the ANTS locale settings (ANTS_DATE_FORMAT, ANTS_TIME_FORMAT, ANTS_TIMESTAMP_FORMAT).

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. You can subtract or add a integer to a DATE column.
• 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 it 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 is 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 (the number of days since 1899/12/31) as dates is supported by Genero db in a SELECT INTO statement but not in a WHERE clause. Check your code to detect the use of 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 a date:
   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 results 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 that are Genero db reserved words. Genero db reserved keywords are listed in the Genero db documentation. 
Another solution is to enclose the table/column name in double quotes. Double-quoted table/column names are case-sensitive. If this double-quoted syntax is used, all subsequent references to this table/column must be in the same double-quoted format.

ODIADS004 - ROWIDs

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

Warning: Genero db ROWIDs can be used to identify a unique row during the lifetime 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:

Warning: Genero db ROWIDs are not fully compatible with Informix ROWIDs.

It is recommended that you review the code and remove any usage of ROWIDs, as their usage is not portable to other databases and may lead to problems when the code runs against any other databases. (For example, Oracle has ROWIDs, but they are CHARs instead of 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 other than zero for a serial column. Using an explicit value automatically increments 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 in Informix.

Solution:

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

CREATE [TEMP] TABLE with a SERIAL column works as in 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 version 3.60 supports the same OUTER joins syntax as Informix.

Warning: Genero db version 3.4 does not support Informix-style OUTER joins.

Genero db also 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) )

Solution:

None required.

ODIADS007a - Database concepts

Most BDL applications use only one database instance (in the meaning of Informix). But Informix servers can handle multiple database instances, while Genero db servers manage only one database instance. However, Genero db can manage multiple schemas.

      SELECT * FROM stores.customer

Solution:

With Genero db, you can create as many  users as database schemas are needed. You typically dedicate a database user to administer each occurrence of the application database (i.e. schema in Genero db).

Any user can select the current database schema with the following SQL command:

      SET SCHEMA "<schema>"

Using this instruction, any user can access the tables without giving the owner prefix, as long as the table owner has granted privileges required to access the tables.

Genero db users can be associated to a default schema as follows:

      CREATE USER "<username>" IDENTIFIED ...
             DEFAULT SCHEMA "<schema>"

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

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

       dbi.database.<dbname>. ads. schema = "<schema>"

Warning: double-quoted schema/user names are case-sensitive.

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 involved 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 release the locks immediately after execution. SELECT statements can set shared locks according to the 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.

The following transaction control instructions have been implemented in Genero db:

• Lock wait mode: SET LOCK MODE TO ...
• Isolation level: SET TRANSACTION ISOLATION LEVEL ...
• Explicit exclusive lock: SELECT ... FOR UPDATE

Solution:

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

• SET LOCK MODE ...
• SELECT ... FOR UPDATE

Warning: The SET ISOLATION TO ... Informix syntax is replaced by SET TRANSACTION ISOLATION LEVEL ... in Genero db.

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 column-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 [OF column-list]

• 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 in Informix, but the rows are locked when the cursor is opened, not when the first row is fetched.

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 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

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.

Warning: Genero db 3.60 has implemented a BOOLEAN type which can store the following values (case-insensitive): TRUE/FALSE or 1/0. You can't use the 't' or 'f' character values instead.

Solution:

We don't recommend the use of the BOOLEAN Genero db datatype.

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 bytes (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 (USING) and output (INTO).

ODIADS012 - Constraints

Constraint naming syntax:

Both INFORMIX and Genero db support primary key, unique, foreign key, default and check constraints, but the constraint naming syntax is different : Genero db expects the "CONSTRAINT" keyword before the constraint specification and INFORMIX expects it after.

UNIQUE constraint example:

Primary keys:

Like INFORMIX, Genero db creates an index to enforce PRIMARY KEY constraints (some RDBMS do not create indexes for constraints). 

Unique constraints:

Like INFORMIX, Genero db creates an index to enforce UNIQUE constraints (some RDBMS do not create indexes for constraints).

Warning: Using CREATE UNIQUE INDEX is silently converted to a unique constraint. To drop an index created as CREATE UNIQUE INDEX, you must do an ALTER TABLE DROP CONSTRAINT.

Warning: When using a unique constraint, INFORMIX allows only one row with a NULL value, while Genero db allows several rows with NULL!

Foreign keys:

Both INFORMIX and Genero db support the ON DELETE CASCADE option. To defer constraint checking, INFORMIX provides the SET CONSTRAINT command while Genero db provides the DISABLE CONSTRAINTS hint.

Check constraints:

Warning: The check condition may be any valid expression that can be evaluated to TRUE or FALSE, including functions and literals. You must verify that the expression is not INFORMIX specific.

Null constraints:

INFORMIX and Genero db support not null constraints, but INFORMIX does not allow you to give a name to "NOT NULL" constraints.

Solution:

Constraint naming syntax:

The database interface does not convert constraint naming expressions when creating tables from BDL programs. Review the database creation scripts to adapt the constraint naming clauses for Genero db.

ODIADS013 - Triggers and Stored Procedures

Genero db supports the Informix trigger and stored procedure language.

See Genero db documentation for more details.

Solution:

None required.

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.

Genero db users must be registered in the database. They are created by the database administrator with the following command:
   CREATE USER <username> IDENTIFIED BY <pswd>

or for Operating System authentication:
   CREATE USER <username> IDENTIFIED EXTERNALLY

Note: For defining database users, there is a file in the <install directory of the database>/Server/ants.rhosts . See the Genero db documentation for more information.

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 that you 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, views or synonyms, Genero db users need:
    GRANT CREATE TABLE TO <user>
    GRANT CREATE VIEW TO <user>
    GRANT CREATE SYNONYM TO <user>

Warning: Genero db does NOT provide the Informix CONNECT, RESOURCE and DBA roles.

Solution:

In Genero db, roles can be created with database privileges to simulate Informix system roles.

ODIADS017 - Temporary tables

Informix supports temporary tables with the following statements:

  SELECT ... INTO TEMP tmpname
  CREATE TEMP TABLE tmpname ( ... )

Genero db supports the same temporary table instructions as Informix.

Solution:

None required.

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]

Genero db provides the SUBSTRING( ) function, to extract a sub-string from a string expression:
    SELECT .... FROM tab1 WHERE SUBSTRING(col1,2,2) = 'RO'
    SELECT SUBSTRING('Some text' FROM 6 FOR 3)        -- Gives 'tex'

Genero db 3.60 has implemented the col[x,y] expression but not the col[x] one.

Solution:

The Genero db driver will convert SQL expressions containing Informix substring syntax for you. It is recommended, however, that you replace all Informix col[x,y] expressions with SUBSTRING(col FROM x FOR y-x+1).

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 ...

ODIADS019 - Name resolution of SQL objects

Informix uses the following to identify an SQL object:
  [database[@dbservername]:][{owner|"owner"}.]identifier

The ANSI convention is to use double-quotes for identifier delimiters (For example: "tabname"."colname").

Warning: When using double-quoted identifiers, both Informix and Genero db become case-sensitive. Unlike Informix, Genero db object names are stored in UPPERCASE in system catalogs. That means that SELECT "col1" FROM "tab1" will produce an error if those objects are created without double-quotes; they are identified by COL1 and TAB1 in Genero db system catalogs.

With Informix ANSI-compliant databases:

• The table name must include "owner", unless the connected user is the owner of the database object.
• The database server shifts the owner name to uppercase letters before the statement executes, unless the owner name is enclosed in double quotes.

With Genero db, an object name takes the following form:
   [(schema|"schema").](identifier|"identifier")

Object names are limited to 128 chars in Genero db.

A Genero db schema is owned by a user (usually the application administrator).

Solution:

Check that you do not use single-quoted or double-quoted table names or column names in your static SQL statements. Those quotes must be removed because the database interface automatically converts double quotes to single quotes, and Genero db does not allow single quotes as database object name delimiters.

See also issue ODIADS007a

ODIADS020 - String delimiters and object names

The ANSI string delimiter character is the single quote ( 'string'). Double quotes are used to delimit database object names ("object-name").

Example: WHERE "tabname"."colname" = 'a string value'

Informix allows double quotes as string delimiters, but Genero db doesn't. This is an important distinction, as many BDL programs use double quotes to delimit the strings in SQL commands.

Remark: This problem concerns only double quotes within dynamic SQL statements. Double quotes used in pure BDL string expressions are not subject to SQL compatibility problems. Double-quoted string literals in static SQL statements are converted to single-quoted strings by compilers.

Genero db implements ANSI-compliant SQL syntax and therefore does not support double-quoted string literals; only database object names can be double-quoted.

Solution:

The Genero db driver can automatically replace all double quotes with single quotes.

Escaped string delimiters can be used inside strings like the following:

'This is a single quote: '''
'This is a single quote: \''
"This is a double quote: """
"This is a double quote: \""

Warning: Database object names cannot be delimited by double quotes, because the database interface cannot determine the difference between a database object name and a quoted string!

For example, if the program executes the SQL statement:
    WHERE "tabname"."colname" = "a string value"
replacing all double quotes with single quotes would produce:
    WHERE 'tabname'.'colname' = 'a string value'
This would produce an error since 'tabname'.'colname' is not allowed by Genero db.

Although double quotes are replaced automatically in SQL statements, you should use only single quotes to enforce portability.

ODIADS021 - NUMERIC data types

Informix supports several data types to store numbers:

Genero db supports the following:

Warning: The only difference between DECIMAL and NUMERIC is that NUMERIC guarantees the specified precision, whereas DECIMAL guarantees at least the specified precision.

Warning: A pseudotype is accepted anywhere a regular type name is, but is silently converted into another type which is supported by Genero db.

Solution:

We recommend that you use the following conversion rules:

Warning: Genero db 3.60 DECIMAL can store up to 15 digits, while Informix DECIMAL can store 32. A future version of Genero db will support DECIMAL (p<=32,s).

ODIADS022 - Getting one row with SELECT

With Informix, you must use the SYSTABLES system table with a condition on the table id:

SELECT user FROM systables WHERE tabid=1

With Genero db some statements can be as follows:

PREPARE pre FROM "SELECT USER" EXECUTE pre INTO l_user

Solution:

Check the BDL sources for "FROM systables WHERE tabid=1" and use dynamic SQL to resolve this problem.

ODIADS024 - MATCHES and LIKE in SQL conditions

Informix and Genero db both support MATCHES and LIKE in SQL statements.

MATCHES allows you to use brackets to specify a set of matching characters at a given position:
   ( col MATCHES '[Pp]aris' )
   ( col MATCHES '[0-9][a-z]*' )

In this case, the LIKE statement has no equivalent feature.

Genero db implements the MATCHES operator.

Solution:

None required.

See also: MATCHES operator in SQL Programming.

ODIADS025 - Informix specific SQL statements in BDL

The BDL compiler supports several Informix-specific SQL statements that have no meaning when using Genero db:

• CREATE DATABASE
• DROP DATABASE
• START DATABASE (SE only)
• ROLLFORWARD DATABASE
• SET [BUFFERED] LOG
• CREATE TABLE with special options (storage, lock mode, etc.)

Solution:

Review your BDL source and remove all static SQL statements which are Informix-specific SQL statements.

ODIADS028 - INSERT cursors

Informix supports insert cursors. An "insert cursor" is a special BDL cursor declared with an INSERT statement instead of a SELECT statement. When this kind of cursor is open, you can use the PUT instruction to add rows and the FLUSH instruction to insert the records into the database.

For Informix database with transactions, OPEN, PUT and FLUSH instructions must be executed within a transaction.

Genero db does not support insert cursors.

Solution:

Insert cursors are emulated by the Genero db driver.

ODIADS029 - SQL functions and constants

Almost all Informix functions and SQL constants have a different name or behavior in Genero db.

Comparison list of functions and constants:

Solution:

Warning: You must review the SQL statements using CURRENT / EXTEND expressions.

ODIADS030 - Very large data types

Informix uses the TEXT and BYTE data types to store very large texts or images.

Genero db 3.4 provides CLOB and BLOB data types, and provides TEXT/BYTE synonyms for Informix compatibility.

Solution:

None required.

TEXT & BYTE are supported by Genero db and by the ADS database driver.

ODIADS031 - Cursors WITH HOLD

Informix closes opened cursors automatically when a transaction ends unless the WITH HOLD option is used in the DECLARE instruction. 

By default Genero db keeps cursors open when a transaction ends (however, FOR UPDATE locks are released at the end of a transaction).

Solution:

BDL cursors are automatically closed when a COMMIT WORK or ROLLBACK WORK is performed.

WITH HOLD cursors with a SELECT FOR UPDATE can be supported, if the table has a primary key or a unique index.

ODIADS032 - UPDATE/DELETE WHERE CURRENT OF <cursor>

Informix allows positioned UPDATEs and DELETEs with the "WHERE CURRENT OF <cursor>" clause, if the cursor has been DECLARED with a SELECT ... FOR UPDATE statement.

Warning: UPDATE/DELETE ... WHERE CURRENT OF <cursor> is supported by the Genero db API. However, the cursor must be OPENed and used inside a transaction. 

    DECLARE cur1 CURSOR FOR SELECT * FROM mytable WHERE 1=1 FOR UPDATE
    BEGIN WORK
    OPEN cur1
    FETCH cur1 INTO x,chr
    UPDATE mytable SET mycol2 = "updated" WHERE CURRENT OF cur1
    CLOSE cur1
    COMMIT WORK

Solution:

Check that your programs correctly put WHERE CURRENT OF <cursorname> inside a transaction.

ODIADS033 - Querying system catalog tables

Both Informix and Genero db provides system catalog tables, however the table names and structure are different.

Genero db provides the standard views for system catalog: INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.COLUMNS, and so on.

Solution:

Warning: No automatic conversion of Informix system tables is provided by the database interface.

ODIADS034 - Syntax of UPDATE statements

Informix allows a specific syntax for UPDATE statements:

    UPDATE table SET ( <col-list> ) = ( <val-list> )

Genero db supports this syntax.

BDL programs can have the following type of statements:

    UPDATE table SET table.* = myrecord.*
    UPDATE table SET * = myrecord.*

Static UPDATE statements using the above syntax are converted by the compiler to the standard form:

    UPDATE table SET column=value [,...]

Solution:

None required.

ODIADS036 - INTERVAL data type

Informix's INTERVAL data type stores a value that represents a span of time. INTERVAL types are divided into two classes: year-month intervals and day-time intervals.

Genero db does not provide a data type similar to Informix INTERVAL.

Solution:

Warning: INTERVAL data types are not supported by Genero db 3.4.

It is not recommended that you use the INTERVAL data type because Genero db has no equivalent native data type. This would cause problems when doing INTERVAL arithmetic on the database server side. However, INTERVAL values can be stored in CHAR columns.

Remark: Genero db will support INTERVALs in a future version.

ODIADS040 - National characters data types

Informix provides the NCHAR & NVARCHAR data types to store locale-dependent character data, using a specific collation order.

Genero db 3.6 has the same internationalization solution.

Solution:

None required with Genero db 3.6.

ODIADS046 - The LOAD and UNLOAD instructions

Informix provides SQL instructions to export data from a database table and import data into a database table: The UNLOAD instruction copies rows from a database table into a text file; the LOAD instructions insert rows from a text file into a database table.

Genero db does not provide LOAD and UNLOAD instructions.

Genero db provides an Import/Export Utility (impexp) that will convert a specified set of tables, or an entire database, to or from a Comma Separated Value (CSV) external format.

Solution:

In 4gl programs, the LOAD and UNLOAD instructions are supported with Genero db, with some limitations:

Warning: There is a difference when you use Genero db DATETIME columns. DATETIME columns created in Genero db are equivalent to Informix DATETIME YEAR TO SECOND columns.  In LOAD and UNLOAD, all Genero db DATE columns are treated as Informix DATETIME YEAR TO SECOND columns and thus will be unloaded with the "YYYY-MM-DD hh:mm:ss"  format.

Warning: When using an Informix database, simple dates are unloaded using the DBDATE format (ex: "23/12/1998"). As a result, unloading from an Informix database for loading into an Genero db is not supported.

ODIADS047 - The USER constant

Both Informix and Genero db provide the USER constant, which identifies the current user connected to the database server. However, there is a difference:

• Informix returns the user identifier as defined in the operating system, where it can be case-sensitive (UNIX) or not (NT).
• Genero db returns the user identifier that is stored in the database. By default, Genero db converts the user name to uppercase letters if you do not put the user name in double quotes when creating it.

This is important if your application stores user names in database records (for example, to audit data modifications). You can, for example, connect to Genero db with the name 'scott', and perform the following SQL operations:
     (1) INSERT INTO mytab ( creator, comment )
              VALUES ( USER, 'example' );
      (2) SELECT * FROM mytab
               WHERE creator = 'scott';
The first command inserts 'SCOTT' (in uppercase letters) in the author column. The second statement will not find the row.

Solution:

When creating a user in Genero db, you can put double quotes around the user name in order to force Genero db to store the given user identifier as is:

CREATE USER "scott" IDENTIFIED BY <pswd>

To verify the user names defined in Genero db, connect as SYSTEM and list the records of the ALL_USERS table as follows:

CREATE USER john IDENTIFIED BY <pswd>

SELECT user_name FROM table_of_users

USER_NAME
-----------
SYSTEM
JOHN
scott

ODIADS051 - Setup database statistics

Informix provides a special instruction to compute database statistics in order to help the optimizer determine the best query execution plan:

UPDATE STATISTICS ...

Genero db provides the following instruction to collect statistics:

SET GATHERSTATS tablename

Solution:

Replace the UPDATE STATISTICS by multiple SET GATHERSTATS statements (one for each table)

ODIADS053 - The ALTER TABLE instruction

Informix and Genero db have different implementations of the ALTER TABLE instruction. For example, Informix allows you to use multiple ADD clauses separated by commas; this is not supported by Genero db.

Informix:
     ALTER TABLE customer ADD(col1 INTEGER), ADD(col2 CHAR(20))

Genero db:
     ALTER TABLE customer ADD COLUMN col1 INTEGER ADD COLUMN col2 CHAR(20)

Solution:

Warning: No automatic conversion is done by the database interface. There is no real standard for this instruction ( that is, no common syntax for all database servers). Read the SQL documentation and review the SQL scripts or the BDL programs in order to use the database server-specific syntax for ALTER TABLE.

ODIADS054 - SQL Interruption

With Informix, it is possible to interrupt a long running query if the SQL INTERRUPT ON option is set by the Genero program. The database server returns SQLCODE -213, which can be trapped to detect a user interruption.

    MAIN
      DEFINE n INTEGER
      DEFER INTERRUPT
      OPTIONS SQL INTERRUPT ON
      DATABASE test1
      WHENEVER ERROR CONTINUE
      -- Start long query (self join takes time)
      -- From now on, user can hit CTRL-C in TUI mode to stop the query
      SELECT COUNT(*) INTO n FROM customers a, customers b
           WHERE a.cust_id <> b.cust_id
      IF SQLCA.SQLCODE == -213 THEN
         DISPLAY "Statement was interrupted by user..."
         EXIT PROGRAM 1
      END IF
      WHENEVER ERROR STOP
      ...
    END MAIN

Genero db 3.80 supports SQL Interruption in a similar way as Informix. The db client must issue an SQLCancel() ODBC call to interrupt a query.

Solution:

The Genero db database driver supports SQL interruption and converts the native SQL error code -30005 to the Informix error code -213.

Warning: Make sure you have Genero db 3.80 or higher installed. Older versions do not support SQL interruption.

Note that when writing these lines, Genero db 3.80 does not support interruption of a DDL statement or an SQL statement waiting for a lock to be released (such as SELECT FOR UPDATE). Those limitations should be removed in a later Genero db version.

ODIADS100 - Data type conversion table

ODIADS101 - GeneroDB Sql Error management

For a general idea on how Four J's ODI handles SqlErrors in the BDL language, check the following links:

Error Handling
SQL Errors
STATUS
SQLSTATE
SQLERRMESSAGE
SQLCA Record
Portability: SQLCA

GeneroDB has some specific rules that need to be highlighted:

1. Normally, you should use SQLSTATE, because this is the ANSI/ISO standard. SQLSTATE defines unified codes so that you can write programs for different RDBMS. However, only some RDBMS - and only in their recent versions - support SQLSTATE. GeneroDB is not one of them. So we would suggest that you keep using SQLCA.SQLCODE (which gives the Informix error code).

• The native Genero db error code is stored in SQLCA.SQLERRD[2] register
• The native Genero db error message is stored in SQLERRMESSAGE operator
• The generic Informix error code is stored in SQLCA.SQLCODE register

So far, we convert the following native Genero db errors:

2. There's no one-to-one error conversion even from Informix to GeneroDB. If you take a look at the exhaustive list of Informix error:s
   http://www-306.ibm.com/software/data/informix/pubs/library/ierrors.htm

Many different formerly Informix errors can be returned by the ODI driver; For example,  fetch on open cursor, commit on unopened transaction, ...

If an unexpected problem happens on the ODBC driver end (could not create temporary table, ...), the driver will return:

List of known errors the GeneroDB ODBC driver can return:

3. If an unknown error comes from the DB Server and therefore is not mapped as an Informix error, you'll get:

As said previously, you can always check the native SQL error in the SQLCA.SQLERRD[2] register or in the FGLSQLDEBUG output.

Example:

Here is a suggestion to trap an unknown error (-768 or -6372):

    MAIN
      WHENEVER ERROR CONTINUE
      CONNECT TO dsn_connectstring
      IF STATUS <> 0 THEN
         DISPLAY "ERROR: Connection to the database failed."
         DISPLAY SQLCA.SQLCODE, ": ", SQLCA.SQLERRD[2], "-" , SQLERRMESSAGE
         EXIT PROGRAM 1
      END IF
      WHENEVER ERROR STOP
    END MAIN