ODIADB001 - DATE and DATETIME data types

INFORMIX provides two data types to store dates and date and time information:

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

ADABAS provides the following data types to store date and time information:

• DATE = for year, month, day storage.
• TIME = for hour, minute, second storage.
• TIMESTAMP = for year, month, day, hour, minute, second, fraction storage.

String representing date time information :

INFORMIX is able to convert quoted strings to DATE / DATETIME data if the string contents matches environment parameters (i.e. DBDATE, GL_DATETIME). As in INFORMIX, Adabas can convert quoted strings to date time data in accordance with the DATE TIME FORMAT database parameter.

Date arithmetic:

• INFORMIX supports date arithmetic on DATE and DATETIME values. The result of an arithmetic expression involving dates/times is a number of days when only DATEs are used and an INTERVAL value if a DATETIME is used in the expression.
• ADABAS does not allow you to use date / time values in arithmetic expressions, you must use SQL functions instead.
• INFORMIX automatically converts an integer to a date when the integer is used to set a value of a date column. ADABAS does not support this automatic conversion.
• Complex DATETIME expressions ( involving INTERVAL values for example ) are INFORMIX specific and have no equivalent in ADABAS.

Date Time Format in ADABAS :

ADABAS supports multiple standards to convert strings representing date and time values. You can set the DATETIME format with the CONTROL utility, in the Configuration/Session folder.

Solution :

ADABAS has the same DATE data type as INFORMIX ( year, month, day ). So you can use ADABAS DATE data type for Informix DATE columns.

ADABAS TIME data type can be used to store INFORMIX DATETIME HOUR TO SECOND values. The database interface makes the conversion automatically.

INFORMIX DATETIME values with any precision from YEAR to FRACTION(5) can be stored in ADABAS TIMESTAMP columns. The database interface makes the conversion automatically. Missing date or time parts default to 1900-01-01 00:00:00.0. For example, when using a DATETIME HOUR TO MINUTE with the value of "11:45", the ADABAS TIMESTAMP value will be "1900-01-01 11:45:00.0".

Warning: For compatibility with Informix DATETIME format, you must set the ISO date time format in the database configuration parameters.

Warning : Using integers as a number of days in an expression with dates is not supported by ADABAS. Check your code to detect where you are using integers with DATE columns.

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 creat_date >'", adate,"'"
is not portable, use a question mark place holder instead and OPEN the cursor USING adate :
   LET stmt = "SELECT ... FROM customer WHERE creat_date > ?"

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 TODAY / CURRENT /  EXTEND must be reviewed and adapted to the native syntax.

ADABAS provides the following functions to perform date / time arithmetic :

ADDATE( <date or timestamp expression>, <expression> )
SUBDATE( <date or timestamp expression>, <expression> )
DATEDIFF( <date or timestamp expression>, <date or timestamp expression> )
DAYOFWEEK( <date or timestamp expression> )
WEEKOFYEAR( <date or timestamp expression> )
DAYOFMONTH( <date or timestamp expression> )
DAYOFYEAR( <date or timestamp expression> )
MAKEDATE( <expression>, <expression> )
DAYNAME( <date or timestamp expression> )
MONTHDATE( <date or timestamp expression> )
ADTIME( <time or timestamp expression>, <time expression> )
SUBTIME( <time or timestamp expression>, <time expression> )
TIMEDIFF( <time or timestamp expression>, <time or timestamp expression> )
MAKETIME( <hours>, <minutes>, <seconds> )
YEAR( <date or timestamp expression> )
MONTH( <date or timestamp expression> )
DAY( <date or timestamp expression> )
HOUR( <time or timestamp expression> )
MINUTE( <time or timestamp expression> )
SECOND( <time or timestamp expression> )
MICROSECOND( <expression> )
TIMESTAMP( <expression>[, <expression> ] )
DATE( <expression> )
TIME( <expression> )

ODIADB003 - Reserved words

Even if ADABAS allows SQL reserved keywords as SQL object names ( "create table table ( column int )" ), you should examine your existing database schema, and check that you do not use ADABAS SQL reserved keywords.

Solution :

See ADABAS documentation for reserved keywords.

ODIADB004 - ROWIDs

INFORMIX rowids are implicit INTEGERs columns managed by the database server.

ADABAS tables have the SYSKEY column. This column is automatically created by ADABAS as a "CHAR(8) BYTE" column which contains the logical address of the row, for example : fffe0000000000001.

Solution :

ROWID keywords are automatically converted to SYSKEY by the database interface. However, BDL program logic should be reviewed in order to use the real primary keys.

All references to SQLCA.SQLERRD[6] must be removed because this variable will not hold the ROWID of the last INSERTed or UPDATEd row when using the ADABAS interface.

ODIADB006 - Outer joins

The syntax of OUTER joins is very different in INFORMIX and ADABAS :

In INFORMIX SQL, outer tables are defined in the FROM clause with the OUTER keyword :

SELECT ... FROM cust, OUTER(order)
 WHERE cust.key = order.custno

SELECT ... FROM cust, OUTER(order,OUTER(item))
 WHERE cust.key = order.custno
   AND order.key = item.ordno
   AND order.cdate > today

ADABAS expects the (+) operator in the join condition. You must set a (+) after columns of the tables which must have NULL values when no record matches the condition :

SELECT ... FROM a, b
 WHERE a.key = b.key (+)

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

When using additional conditions on outer tables, the (+) operator has to be used also. For example :

SELECT ... FROM a, OUTER(b)
 WHERE a.key = b.akey
   AND b.colx > 10

Must be converted to :

SELECT ... FROM a, b
 WHERE a.key = b.akey (+)
   AND b.colx (+) > 10

Solution :

The database interface can convert most INFORMIX OUTER specifications to ADABAS outer joins.

Prerequisites :

1. In the FROM clause, the main table must be the first item and the outer tables must figure from left to right in the order of outer levels.
      Example which does not work : "FROM OUTER(tab2), tab1 ".
2. The outer join in the WHERE part must use the table name as prefix.
      Example : "WHERE tab1.col1 = tab2.col2 ".

Restrictions :

1. Statements composed of 2 or more SELECT instructions are not supported.
     Example : "SELECT ... UNION SELECT" or "SELECT ... WHERE col IN (SELECT...)"

Remarks :

1. Table aliases are detected in OUTER expressions.
      OUTER example with table alias : "OUTER( tab1 alias1)".
2. In the outer join, <outer table>.<col> can be placed on both right or left sides of the equal sign.
      OUTER join example with table on the left : "WHERE outertab.col1 = maintab.col2 ".
3. Table names detection is not case-sensitive.
      Example : "SELECT ... FROM tab1, TAB2 WHERE tab1.col1 = tab2.col2".
4. Temporary tables are supported in OUTER specifications.

ODIADB007a - Database concepts

ODIADB008a - Data consistency and concurrency management

Data consistency involves readers which want to access data currently modified by writers and 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, ...).

INFORMIX

INFORMIX uses a locking mechanism to manage data consistency and concurrency. When a process modifies data with UPDATE, INSERT or DELETE, an exclusive lock is set on the affected rows. The lock is held 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 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 :

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

Defaults :

• The default isolation level is read committed.
• The default lock wait mode is "not wait".
• The default locking granularity is per page.

ADABAS

As in INFORMIX, ADABAS uses locks to manage data consistency and concurrency. The database manager sets exclusive locks on the modified rows and shared locks when data is read, according to the isolation level. The locks are held until the end of the transaction. When multiple processes want to access the same data, the latest processes must wait until the first finishes its transaction.  The lock granularity is at the row level. For more details, see ADABAS documentation.

Control :

• Default is WAIT, can be changed to NOWAIT when doing a SELECT FOR UPDATE.
• Isolation level can be changed even for a SELECT FOR UPDATE statement.
• Locking granularity : Row level.
• Explicit locking : SELECT ... FOR UPDATE

Defaults :

• The default isolation level is Cursor Stability (readers cannot see uncommitted data, no shared lock is set when reading data).

Solution :

For portability, it is recommended that you work with INFORMIX in the read committed isolation level, to make processes wait for each other (lock mode wait) and to create tables with the "lock mode row" option.

See INFORMIX and ADABAS documentation for more details about data consistency, concurrency and locking mechanisms.

ODIADB008b - SELECT FOR UPDATE

A lot of BDL programs use pessimistic locking in order to avoid several users editing the same rows at the same time.

   DECLARE cc CURSOR FOR
         SELECT ... FROM tab WHERE ... FOR UPDATE
   OPEN cc
   FETCH cc <-- lock is acquired
   ...
   CLOSE cc <-- lock is released

In both INFORMIX and ADABAS, locks are released when the cursor is closed or when the transaction ends.

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

By default, ADABAS locks rows when using "FOR UPDATE" keywords.

ADABAS supports the "NOWAIT" option in the SELECT to give the control back immediately if the row is already locked by another process.

ADABAS does not release the locks when the cursor is closed.  Locks are released when the transaction ends.

Solution :

Warning : The database interface is based on an emulation of an Informix engine using transaction logging. Therefore, opening a SELECT ... FOR UPDATE cursor declared outside a transaction will raise an SQL error -255 (not in transaction).

You may have to review the program logic if you use pessimistic locking based on NOT WAIT mode.

ODIADB009a - Transactions handling

INFORMIX and ADABAS handle transactions differently. The differences in the transactional models can affect the program logic.

INFORMIX native mode (non ANSI) :

• DDL statements can be executed (and canceled) in transactions.
• Transactions must be started with BEGIN WORK. Statements executed outside of a transaction are automatically committed.

ADABAS :

• DDL statements can be executed (and canceled) in transactions.
• Beginning of transactions are implicit; two transactions are delimited by COMMIT [WORK] or ROLLBACK [WORK].

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

Solution :

The INFORMIX behavior is simulated with an auto commit mode in the ADABAS interface. A switch to the explicit commit mode is done when a BEGIN WORK is performed by the BDL program.

Regarding the transaction control instructions, the BDL applications do not have to be modified in order to work with ADABAS.

See also ODIADB008b

ODIADB009b - Avoiding long transactions

Some BDL applications do not care about long transactions because they use an INFORMIX database which does not handle transaction logs (transactions are not stored in log files for potential rollbacks).

With ADABAS, using transactions is mandatory and every database modification is stored in transaction logs.

BDL applications must prevent long transactions when connected to a database using logging. For example, if a table holds hundred thousands of rows, a "DELETE FROM table" might cause problems.

Solution :

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

ODIADB011a - CHARACTER data types

As INFORMIX, ADABAS provides the CHAR and VARCHAR data types to store character data.

INFORMIX CHAR type can store up to 32767 characters and the VARCHAR data type is limited to 255 characters.

ADABAS CHAR and VARCHAR both have a limit of  4000 characters.

Solution :

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

Warning: Check that your database schema does not use CHAR or VARCHAR types with a length exceeding the ADABAS limit.

ODIADB012 - Constraints

Constraint naming syntax :

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

UNIQUE constraint example :

Primary keys :

ADABAS does not create an index when defining PRIMARY KEY constraints. Warning : ADABAS primary key constraints do not allow NULLs; make sure your tables do not contain NULLs in the primary key columns.

Unique constraints :

Like INFORMIX, ADABAS creates an index to enforce UNIQUE constraints (some RDBMS do not create indexes for constraints). Warning : ADABAS unique constraints do not allow NULLs; make sure your tables do not contain NULLs in the unique columns.

Foreign keys :

Both INFORMIX and ADABAS support the ON DELETE CASCADE option.

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 :

As in INFORMIX, ADABAS does not allow you to give a name to "NOT NULL" constraints.

Solution :

Constraint naming syntax :

Warning : 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 ADABAS.

ODIADB013 - Triggers

INFORMIX and ADABAS provide triggers with similar features, but the trigger creation syntax and the programming languages are totally different.

INFORMIX triggers define which stored procedures must be called when a database event occurs (before | after  insert | update | delete ...), while ADABAS triggers can hold a procedural block.

ADABAS provides specific syntax to define triggers. See documentation for more details.

Solution :

INFORMIX triggers must be converted to ADABAS triggers "by hand".

ODIADB014 - Stored procedures

Both INFORMIX and ADABAS support stored procedures, but the programming languages are totally different : INFORMIX provides the SPL language while ADABAS procedures must be written in SQL-PL language.

Solution :

INFORMIX stored procedures must be converted to ADABAS "by hand".

ODIADB016a - Defining database users

INFORMIX users are defined at the operating system level, they must be members of the 'informix' group, and the database administrator must grant CONNECT, RESOURCE or DBA privileges to those users.

ADABAS users are created in the database with the "CREATE USER" statement by database administrators (users from the DBA class).

Solution :

Setup the ADABAS environment for each user as described in the documentation.

ODIADB016b - Setting privileges

INFORMIX and ADABAS user privileges management are quite similar.

ADABAS provides user groups to define a set of privileges.

Solution :

Make sure ADABAS users have the right privileges to access the database.

ODIADB017 - Temporary tables

INFORMIX temporary tables are created through the CREATE TEMP TABLE DDL instruction or through a SELECT ... INTO TEMP statement. Temporary tables are automatically dropped when the SQL session ends, but they can also be dropped with the DROP TABLE command. There is no name conflict when several users create temporary tables with the same name.

Remark : BDL reports create a temporary table when the rows are not sorted externally (by the source SQL statement).

INFORMIX allows you to create indexes on temporary tables. No name conflict occurs when several users create an index on a temporary table by using the same index identifier.

As in INFORMIX, ADABAS D 11 supports temporary tables by using the "TEMP" prefix before table names.

Example:

    CREATE TABLE TEMP.tt1 ( k INTEGER, c CHAR(20) )
    INSERT INTO TEMP.tt1 ( k, c ) VALUES ( 1, 'aaaa' )
    CREATE TABLE TEMP.tt2 AS SELECT * FROM TEMP.tt1

But ADABAS does not support INDEX creation on temporary tables.

Solution :

The ADABAS database interface automatically converts INFORMIX "CREATE TEMP TABLE" and "SELECT ... INTO TEMP" instructions by adding the "TEMP" prefix before the table name.

Warning : As ADABAS does not allow  INDEX creation on temporary tables, you must review your programs and search for "CREATE INDEX" statements. Additionally, as the runtime system uses temporary tables with index creation in REPORTs using the "ORDER BY" directive, you must review you reports and use "ORDER EXTERNAL BY" instead.

ODIADB018 - Substrings in SQL

INFORMIX SQL statements can use subscripts 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]

.. while ADABAS provides the SUBSTR( ) function, to extract a substring from a string expression :
    SELECT .... FROM tab1 WHERE SUBSTR(col1,2,2) = 'RO'
    SELECT SUBSTR('Some text',6,3) FROM DUAL       -- Gives 'tex'

Solution :

You must replace all Informix col[x,y] expressions by SUBSTR(col,x,y-x+1).

Warning : In UPDATE instructions, setting column values through subscripts will produce an error with ADABAS :
    UPDATE tab1 SET col1[2,3] = 'RO' WHERE ...
is converted to :
    UPDATE tab1 SET SUBSTR(col1,2,3-2+1) = 'RO' WHERE ...

Warning : Column subscripts in ORDER BY expressions are also converted and produce an error with ADABAS :
    SELECT ... FROM tab1 ORDER BY col1[1,3]
is converted to :
    SELECT ... FROM tab1 ORDER BY SUBSTR(col1,1,3-1+1)

ODIADB019 - Name resolution of database objects

Case sensitivity in object names:

INFORMIX database object names are not case sensitive in non-ANSI databases.

   CREATE TABLE Tab1 ( Key INT, Col1 CHAR(20) )
   SELECT COL1 FROM TAB1

ADABAS database object names are case sensitive. When a name is used without double quotes, it is automatically converted to uppercase letters. When using double quotes, the names are not converted :

   CREATE TABLE tab1 ( Key INT, Col1 CHAR(20) )
       => Table name is "TAB1", column names are "KEY" and "COL1"

   CREATE TABLE "Tab1" ( "Key" INT, "Col1" CHAR(20) )
       => Table name is "Tab1", column names are "Key" and "Col1"

The ADABAS user group concept:

With non-ANSI INFORMIX databases, you do not have to give a schema name before the tables when executing an SQL statement.

    SELECT ... FROM <table> WHERE ...

In an ADABAS database, tables always belong to a owner (the user who created the tables). When executing a SQL statement, a owner name must be used as the high-order part of a two-part object name, unless the current user belongs to the same usergroup as the owner.

Solution :

Case sensitivity in object names:

Avoid the use of double quotes around the database object names. All names will be converted to uppercase letters.

Use USERGROUPs:

If you want to allow several users to use the database tables without giving the owner name, create usergroups and create users as members of the usergroup.

Warning : Defining database users through user groups will merge both privileges and db object name resolution. All users of a usergroup have the same privileges. Privileges can only be granted to usergroups, not directly to users who are members of usergroups.

ODIADB020 - String delimiters

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 ADABAS doesn't. This is important since many BDL programs use that character to delimit the strings in SQL commands.

Remark : This problem concerns only double quotes within SQL statements. Double quotes used in pure BDL string expressions are not subject to SQL compatibility problems.

Solution :

The ADABAS database interface can automatically replace all double quotes by single quotes.

Escaped string delimiters can be used inside strings as follows :

     '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 by single quotes would produce :
   WHERE 'tabname'.'colname' = 'a string value'
This would produce an error since 'tabname'.'colname' is not allowed by ADABAS.

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

ODIADB021a - NUMERIC data types

INFORMIX provides several data types to store numbers :

ADABAS provides the "FIXED[(p[,s])]" and "FLOAT[(p[,s])]" numeric data types. Columns of type "FIXED" store values with a fixed point

ADABAS maps ANSI data types to native types as described in this table:

Therefore, all INFORMIX numeric data types are compatible, except the following :

Solution :

SQL scripts to create databases must be converted manually. Tables created from BDL programs do not have to be converted: the database interface detects the MONEY data type and uses the DECIMAL type for ADABAS.

ODIADB022 - Getting one row with SELECT

With INFORMIX, one must use the system table with a condition on the table id :

   SELECT user FROM systables WHERE tabid=1

With ADABAS, you have to do the following :

   SELECT user FROM DUAL 

Solution :

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

ODIADB023 - Considering the ANSI mode with INFORMIX

INFORMIX allows you to create databases in ANSI mode, which is supposed to be closer to ANSI databases like ADABAS.

Here are some issues from the INFORMIX documentation :

• Some actions, like CREATE INDEX, will generate a warning but will not be forbidden.
• Buffered logging is not allowed to enforce data recovery. (Buffered logging provides better performance)
• The table-naming scheme allows different users to create tables without having to worry about name conflicts.
• Owner naming is required ( SELECT ... FROM "owner".table ) Must quote the owner name to prevent automatic translation of the owner name into uppercase; SELECT .. FROM owner.table -> SELECT .. FROM OWNER.table => table not found in database!
• Default privileges differ; when creating a table, the server grants the table owner and the DBA only. This also happens for the 'Execute' privilege when creating stored procedures.
• Default isolation level is REPEATABLE READ
• An error is generated if any character field is filled with a value that is longer than the field width.
• DECIMAL(p) (floating point decimal) is automatically converted to DECIMAL(p,0) (fixed point decimal).
• Closing a closed cursor generates an SQL error.

It will take more time to adapt the BDL programs to the INFORMIX ANSI mode than using the ADABAS interface to simulate the native mode of INFORMIX.

Solution :

Do not plan to use the ANSI mode of INFORMIX in order to simplify the adaptation to ADABAS.

ODIADB024 - MATCHES and LIKE in SQL conditions

INFORMIX supports MATCHES and LIKE in SQL statements, while ADABAS supports the LIKE statement only.

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.

The following substitutions must be made to convert a MATCHES condition to a LIKE condition :

• MATCHES keyword must be replaced by LIKE.
• All '*' characters must be replaced by '%'.
• All '?' characters must be replaced by '_'.
• Remove all brackets expressions.

Solution :

Warning : SQL statements using MATCHES expressions must be reviewed in order to use LIKE expressions.

ODIADB025 - INFORMIX specific SQL statements in BDL

The BDL compiler supports several INFORMIX specific SQL statements that have no meaning when using ADABAS.

Here is a list of those statements :

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

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

ADABAS does not support insert cursors.

Solution :

Insert cursors are emulated by the ADABAS database interface.

ODIADB029 - SQL functions and constants

Both INFORMIX and ADABAS provide numerous built-in SQL functions. Most of INFORMIX SQL functions have the same name and purpose in ADABAS ( DAY(), MONTH(), YEAR(), UPPER(), LOWER(), LENGTH() ).

Solution :

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

You can create user defined functions ( UFs ) in the ADABAS database.

ODIADB030 - Very large data types

Both INFORMIX and ADABAS provide special data types to store very large texts or images.

ADABAS recommends the following conversion rules :

Solution :

Very large character data types are not supported yet by the ADABAS interface.

ODIADB031 - Cursors WITH HOLD

INFORMIX provides the WITH HOLD option to prevent cursors being closed when a transaction ends.

Solution :

This feature is fully supported by ADABAS.

ODIADB033 - Querying system catalog tables

As in INFORMIX, ADABAS provides system catalog tables (systables,syscolumns,etc.) in each database, but the table names and their structure are quite different.

Solution :

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

ODIADB034 - Syntax of UPDATE statements

INFORMIX allows a specific syntax for UPDATE statements :

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

or

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

Solution :

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

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

ODIADB036 - INTERVAL data type

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

ADABAS does not provide a data type corresponding to the INFORMIX INTERVAL data type.

Solution :

Warning: The INTERVAL data type is not well supported because the database server has no equivalent native data type. However, you can store into and retrieve from CHAR columns BDL INTERVAL values.

ODIADB039 - Data storage concepts

An attempt should be made to preserve as much of the storage information as possible when converting from INFORMIX to ADABAS. Most important storage decisions made for INFORMIX database objects (like initial sizes and physical placement) can be reused for the ADABAS database.

Storage concepts are quite similar in INFORMIX and in ADABAS, but the names are different.

The following table compares INFORMIX storage concepts to ADABAS storage concepts :

ODIADB040 - National characters data types

INFORMIX : NCHAR & NVARCHAR
ADABAS : ?

Solution :

Warning : National character data types are not supported yet.

ODIADB046 - The LOAD and UNLOAD instructions

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

ADABAS does not provide LOAD and UNLOAD instructions.

Solution :

LOAD and UNLOAD instructions are supported.

Warning : There is a difference when using ADABAS TIME and TIMESTAMP columns; TIME columns created in the ADABAS database are similar to INFORMIX DATETIME HOUR TO SECOND columns. In LOAD and UNLOAD, all ADABAS TIME columns are treated as INFORMIX DATETIME HOUR TO SECOND columns and thus will be unloaded with the "hh:mm:ss"  format.

Warning : When using an INFORMIX database, simple dates are unloaded with the DBDATE format (ex: "23/12/1998"). Therefore, unloading from an INFORMIX database for loading into an ADABAS database is not supported.

ODIADB051 - Setup database statistics

INFORMIX provides a special instruction to compute database statistics in order to improve query optimization plans :

     UPDATE STATISTICS [options]

ADABAS provides the following equivalent:

     UPDATE STATISTICS <tabname>
   | UPDATE STATISTICS COLUMN <tabname>.<colname>
   | UPDATE STATISTICS COLUMN ( <colname>, [...] ) FOR <tabname>

Solution :

The database interface does not convert this statement; you must execute the UPDATE STATISTICS instruction using the syntax expected by the database server.

ODIADB052 - The GROUP BY clause

INFORMIX allows you to use column numbers in the GROUP BY clause

     SELECT ord_date, sum(ord_amount) FROM order GROUP BY 1

ADABAS does not support column numbers in the GROUP BY clause.

Solution :

Use column names instead.

Warning : This is not possible when doing SELECT ... UNION ... SELECT.

ODIADB053 - The ALTER TABLE instruction

INFORMIX and ADABAS use different implementations of the ALTER TABLE instruction.

For example:

INFORMIX allows you to use multiple ADD clauses separated by commas. ADABAS does not expect braces and the comma separator :

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

Solution :

Warning : No automatic conversion is done by the database interface. 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.

ODIADB100 - Data type conversion table