ODIASA001 - DATE and DATETIME data types

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

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

Sybase ASA provides two data type to store dates :

• DATE = for year, month, day storage.
• TIME = for hour, minutes, seconds, fraction(3) storage.
• TIMESTAMP = for year, month, day, hour, minutes, seconds, fraction(3) 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, Sybase ASA can convert quoted strings representing datetime data in the ANSI format. The CONVERT( ) SQL function allows you to convert strings to dates.

Date time 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.
• INFORMIX automatically converts an integer to a date when the integer is used to set a value of a date column. Sybase ASA does not support this automatic conversion.
• Complex DATETIME expressions ( involving INTERVAL values for example ) are INFORMIX specific and have no equivalent in Sybase ASA.
• Sybase ASA allows the following arithmetic on dates:
    - Date + integer => add n days to the date.
    - Date - integer => substract n days from the date.
    - Timestamp + integer => add n days to the timestamp.
    - Timestamp - integer => substract n days from the timestamp.
    - Date - Date => compute number of days between 2 dates.
    - Timestamp - Timestamp => compute number of days between 2 timestamps.
    - Date + Time => Create a Timestamp combining given date & time.
• INFORMIX converts automatically an integer to a date when the integer is used to set a value of a date column. Sybase ASA does not support this automatic conversion.

Solution :

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

Sybase ASA TIME data type can be used to store INFORMIX DATETIME HOUR TO FRAC(3) values. The database interface makes the conversion automatically.

INFORMIX DATETIME values with any precision from YEAR to FRACTION(5) can be stored in Sybase ASA 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 ASA TIMESTAMP value will be "1900-01-01 11:45:00.0".

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

Warning : It is strongly recommended to 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 > ?"

ODIASA003 - Reserved words

Even if ASA allows SQL reserved keywords as SQL object names if enclosed in double quotes ( "create table "table" ( col1 int )" ), you should take care of your existing database schema and check that you do not use Sybase ASA SQL words.

Solution :

Database objects having a name which is a Sybase ASA SQL reserved word must be renamed.

All BDL application sources must be verified. To check if a given keyword is used in a source, you can use UNIX 'grep' or 'awk' tools. Most modifications can be automatically done with UNIX tools like 'sed' or 'awk'.

ODIASA004 - ROWIDs

When creating a table, INFORMIX automatically adds a "ROWID" column of type integer (applies to non-fragmented tables only). The ROWID column is auto-filled with a unique number and can be used like a primary key to access a given row.

Sybase ASA tables have no ROWIDs.

Solution :

If the BDL application uses ROWIDs, the program logic should be reviewed in order to use the real primary keys (usually, serials which can be supported).

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 Sybase ASA interface.

ODIASA005 - 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 given to 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 will 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

Sybase ASA IDENTITY columns :

• When creating a table, the IDENTITY keyword must be specified after the column data type:
     CREATE TABLE tab1 ( k integer identity, c char(10) )
• You cannot specify a start value
• A new number is automatically created when inserting a new row :
     INSERT INTO tab1 ( c ) VALUES ( 'aaa' )
• To get the last generated number, Sybase ASA provides a global variable :
     SELECT @@IDENTITY
• When IDENTITY_INSERT is ON, you can set a specific value into a IDENTITY column, but zero does not generate a new serial:
     INSERT INTO tab1 ( k, c ) VALUES ( 100, 'aaa' )

INFORMIX SERIALs and MS Sybase ASA IDENTITY columns are quite similar; the main difference is that MS Sybase ASA does not generate a new serial when you specify a zero value for the identity column.

Solution :

You are free to use IDENTITY columns (1) or insert triggers based on the SERIALREG table (2). The first solution is faster, but does not allow explicit serial value specification in insert statements; the second solution is slower but allows explicit serial value specification.  You can initially use the second solution to have unmodified 4gl programs working on Sybase ASA, but you should update your code to use native IDENTITY columns for performance.

With the following fglprofile entry, you define the technique to be used for SERIAL emulation :

   dbi.database.<dbname>.ifxemul.datatype.serial.emulation = {native|regtable}

The 'native' value defines the IDENTITY column technique and the 'regtable' defines the trigger technique.

This entry must be used with :

   dbi.database.<dbname>.ifxemul.datetype.serial = {true|false}

If this entry is set to false, the emulation method specification entry is ignored.

Warning : When no entry is specified, the default is SERIAL emulation enabled with 'native' method (IDENTITY-based).

1. Using IDENTITY columns

In database creation scripts, all SERIAL data types must be converted by hand to INTEGER IDENTITY data types.

Warning : Start values SERIAL(n) cannot be converted, there is no INTEGER IDENTITY(n) in Sybase ASA.

Tables created from the BDL programs can use the SERIAL data type : When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the "SERIAL[(n)]" data type to "INTEGER IDENTITY[(n,1)]".

In BDL, the new generated SERIAL value is available from the SQLCA.SQLERRD[2] variable. This is supported by the database interface which performs a "SELECT @@IDENTITY".

Warning : When you insert a row with zero as serial value, the serial column gets the value zero.
   INSERT INTO tab ( col_serial, col_data, ... ) VALUES ( 0, <data|var>, ... )
Must be converted to :
   INSERT INTO tab ( col_data, ... ) VALUES ( <data|var>, ... )

Warning : SELECT * FROM table INTO TEMP with original table having an IDENTITY column is not supported: The database driver must convert the Informix SELECT INTO TEMP statement into a SELECT INTO #tab + INSERT (see temporary tables) because ODBC does not allow SQL parameters in DDL statements. As MS Sybase ASA does not allow you to insert a row by giving the identity column, the INSERT statement fails.

2. Using triggers with the SERIALREG table

First, you must prepare the database and create the SERIALREG table. You can generate the CREATE TABLE instruction with the gsrl_asa tool provided in the adaptation kit.

In database creation scripts, all SERIAL[(n)] data types must be converted to INTEGER data types and you must create one trigger for each table by using the gsrl_asa tool.

Tables created from the BDL programs can use the SERIAL data type.  When a BDL program executes a CREATE [TEMP] TABLE with a SERIAL column, the database interface automatically converts the "SERIAL[(n)]" data type to "INTEGER" and creates the insert triggers.

Warning : Sybase ASA does not allow you to create triggers on temporary tables. Therefore, you cannot create temp tables with a SERIAL column when using this solution.

Warning : SELECT ... INTO TEMP statements using a table created with a SERIAL column do not automatically create the SERIAL triggers in the temporary table. The type of the column in the new table is INTEGER.

Warning : Sybase ASA triggers are not automatically dropped when the corresponding table is dropped. Database administrators must be aware of this behavior when managing schemas.

Warning : INSERT statements using NULL for the SERIAL column will produce a new serial value, instead of a NULL like INFORMIX does :
   INSERT INTO tab ( col_serial, col_data ) VALUES ( NULL, 'data' )
This behavior is mandatory in order to support INSERT statements which do not use the serial column :
   INSERT INTO tab ( col_data ) VALUES ( 'data' )
Check if your application uses tables with a SERIAL column that can contain a NULL value.

Warning : The serial production is based on the SERIALREG table which registers the last generated number for each table. If you delete rows of this table, sequences will restart at 1 and you will get unexpected data.

ODIASA006 - Outer joins

The syntax of OUTER joins is quite different in INFORMIX and Sybase ASA :

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

Sybase ASA Version 7 supports the ANSI outer join syntax :

SELECT ... FROM cust LEFT OUTER JOIN order
                     ON cust.key = order.custno

SELECT ...
  FROM cust LEFT OUTER JOIN order
                 LEFT OUTER JOIN item
                 ON order.key = item.ordno
            ON cust.key = order.custno
 WHERE order.accepted = 1

Remark: The old way to define outers in Sybase ASA looks like the following :

SELECT ... FROM a, b WHERE a.key *= b.key

See the Sybase ASA reference manual for a complete description of the syntax.

Solution :

The Sybase ASA interface can convert simple INFORMIX OUTER specifications to Sybase ASA ANSI outer joins.

Prerequisites :

1. The outer join in the WHERE part must use the table name as prefix.
      Example : "WHERE tab1.col1 = tab2.col2 ".
2. Additional conditions on outer table columns cannot be detected and therefore are not supported :
      Example : "... FROM tab1, OUTER(tab2) WHERE tab1.col1 = tab2.col2 AND tab2.colx > 10".
3. Statements composed of 2 or more SELECT instructions using OUTERs 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.

ODIASA007a - Database concepts

As in INFORMIX, an Sybase ASA engine can manage multiple database entities. When creating a database object like a table, Sybase ASA allows you to use the same object name in different databases.

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

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

Sybase ASA

As in INFORMIX, Sybase ASA 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 or the lock timeout occurred. The lock granularity is at the row or table level. For more details, see Sybase ASA's Documentation, "Accessing and Changing Data", "Locking".

Control :

• Lock wait mode : Can only be set to on or off, and a timeout can be specified, with:
   SET TEMPORARY OPTION BLOCKING = { ON | OFF }
   SET TEMPORARY OPTION BLOCKING_TIMEOUT = n
• Isolation level : Can be set with:
   SET TEMPORARY OPTION ISOLATION LEVEL = {1|2|3|4}
• Locking granularity : Row level.
• Explicit locking : SELECT ... FOR UPDATE

Defaults :

• The default isolation level is Read Committed ( 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 Sybase ASA documentation for more details about data consistency, concurrency and locking mechanisms.

When using SET LOCK MODE and SET ISOLATION LEVEL instructions, the database interface sets automatically the native database session options.

ODIASA008b - 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 ... FOR UPDATE
  OPEN cc
  FETCH cc <-- lock is acquired
  CLOSE cc <-- lock is released

• A transaction must be started before opening cursors declared for update.
• The row must be fetched in order to set the lock.
• The lock is released when the transaction ends (if the cursor is not declared "WITH HOLD") or when the cursor is closed.

Sybase ASA allows individual and exclusive row locking by using the FOR UPDATE clause, as Informix.

• Individual locks are acquired when fetching the rows.
• When the cursor (WITH HOLD) is opened outside a transaction, locks are released when the cursor is closed.
• When the cursor is opened inside a transaction, locks are released when the transaction ends.

Sybase ASA's locking granularity is at the row level, page level or table level (the level is automatically selected by the engine for optimization).

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 WAIT. This as an INFORMIX specific SQL statement.

Solution :

SELECT FOR UPDATE statements are well supported.

Warning : Sybase ASA locks the rows when you open the cursor. You will have to test SQLCA.SQLCODE after doing an OPEN.

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

Warning : The SELECT FOR UPDATE statement cannot contain an ORDER BY clause if you want to perform positioned updates/deletes with WHERE CURRENT OF.

You must review the program logic if you use pessimistic locking because it is based on the NOT WAIT mode which is not supported by Sybase ASA.

ODIASA009 - Transactions handling

INFORMIX and Sybase ASA handle transactions in a similar manner.

INFORMIX native mode (non ANSI) :

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

Sybase ASA :

• Transactions are started with "BEGIN TRANSACTION [name]".
• Transactions are validated with "COMMIT TRANSACTION [name]".
• Transactions are canceled with "ROLLBACK TRANSACTION [name]".
• Transactions save points can be placed with "SAVEPOINT [name]".
• Sybase ASA supports named and nested transactions.
• By default transactions are started implicitly as in the ANSI specification.
  This behavior can be changed with:
     SET TEMPORARY OPTION CHAINED = OFF 
• DDL statements are not supported in transactions blocks.

Transactions in stored procedures : avoid using transactions in stored procedure to allow the client applications to handle transactions, according to the transaction model.

Solution :

INFORMIX transaction handling commands are automatically converted to Sybase ASA instructions to start, validate or cancel transactions.

The database driver sets the "CHAINED" option to OFF when connecting to the server.

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

ODIASA011 - CHARACTER data types

As in INFORMIX, Sybase ASA 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.

Sybase ASA CHAR and VARCHAR both have a limit of  32767 characters.

Sybase ASA provides the LONG VARCHAR data type to store large character strings. Only the LIKE operator can be used for searches. LONG VARCHAR columns cannot be used in classic comparison expressions (as col = 'value').

Solution :

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

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

Warning : TEXT values cannot be used as input or output parameters in SQL statements and therefore are not supported.

ODIASA012 - Constraints

Constraint naming syntax :

Both INFORMIX and Sybase ASA support primary key, unique, foreign key, default and check constraints. But Sybase ASA does not support constraint naming syntax:

UNIQUE constraint example :

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

ODIASA013 - Triggers

INFORMIX and Sybase ASA provide triggers with similar features, but the programming languages are totally different.

Warning : Sybase ASA does not support triggers on temporary tables.

Solution :

INFORMIX triggers must be converted to Sybase ASA triggers "by hand".

ODIASA014 - Stored procedures

Both INFORMIX and Sybase ASA support stored procedures, but the programming languages are totally different :

• INFORMIX stored procedures must be written in SPL.
• Sybase ASA stored procedures must be written in Sybase ASA SQL.

Solution :

INFORMIX stored procedures must be converted to Sybase ASA "by hand".

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

Before a user can access an Sybase ASA database, the system administrator (DBA) must declare the application users in the database with the GRANT statement. You may also need to define groups in order to make tables visible to other users.

Solution :

See Sybase ASA documentation for more details on database logins and users.

ODIASA016b - Setting privileges

INFORMIX and Sybase ASA user privileges management are quite similar.

Sybase ASA provides user groups to grant or revoke permissions to more than one user at the same time.

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

Sybase ASA provides local (SQL session wide) temporary tables by using the DECLARE LOCAL TEMPORARY TABLE statement. 

Warning : The "SELECT ... INTO TEMP" statement is not supported in Sybase ASA.

Warning : Index creation is not supported on temporary tables in Sybase ASA, but the connector doesn't raise error. You have not to remove the index creation on temp tables in your 4gl programs, but keep in mind that index will not be created.

Solution :

INFORMIX temporary tables instructions are converted to generate native Sybase ASA temporary tables .

ODIASA018 - 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 Sybase ASA provides the SUBSTR( ) function, to extract a substring from a string expression :
    SELECT .... FROM tab1 WHERE SUBSTRING(col1,2,2) = 'RO'
    SELECT SUBSTRING('Some text',6,3) FROM DUAL       -- Gives 'tex'

Solution :

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

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

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

ODIASA019 - Name resolution of SQL objects

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

Identifiers have a maximum length of 128 bytes and are composed of alphabetic characters ( _, @, #, $ are considered as alphabetic characters) or digits. The first character must be alphabetic.

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

As INFORMIX, Sql Server Anywhere allows to use double quotes as string delimiters, if the QUOTED_IDENTIFIER session option is OFF, the default is ON:

    SET TEMPORARY OPTION QUOTED_IDENTIFIER = OFF

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

Solution :

When the ifxemul.dblquotes option is set, the Sybase ASA database interface converts all double quotes to single quotes in SQL statements. The database driver does not set the QUOTED_IDENTIFIER option implicitly.

ODIASA021 - NUMERIC data types

Sybase ASA offers numeric data types which are quite similar to INFORMIX numeric data types. The table below shows general conversion rules for numeric data types :

Solution :

In BDL programs :

When creating tables from BDL programs, the database interface automatically converts INFORMIX data types to corresponding Sybase ASA data types.

Database creation scripts :

• SMALLINT and INTEGER columns do not have to use another data type in Sybase ASA.
• For DECIMALs, check the precision limit. Always use a precision and a scale.
• Convert MONEY columns to DECIMAL(p,s) columns. Always use a precision and a scale.
• Convert SMALLFLOAT columns to REAL columns.
• Since FLOAT precision is ignored in INFORMIX, convert this data type to FLOAT(15).

ODIASA022 - Getting one row with SELECT

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

   SELECT user FROM systables WHERE tabid=1

With Sybase ASA, you can omit the FROM clause to generate one row only:

   SELECT user

Solution :

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

ODIASA024 - MATCHES and LIKE in SQL conditions

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

The MATCHES operator of INFORMIX uses the star, question mark and square braces wildcard characters.
The LIKE operator of Sybase ASA offers the percent, underscore and square braces wildcard characters.

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

Solution :

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

ODIASA025 - INFORMIX specific SQL statements in BDL

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

Examples :

• CREATE DATABASE dbname IN dbspace WITH BUFFERED LOG
• START DATABASE (SE only)
• ROLLFORWARD DATABASE
• CREATE TABLE ... IN dbspace WITH LOCK MODE ROW

Solution :

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

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

Sybase ASA does not support insert cursors.

Solution :

Insert cursors are emulated by the Sybase ASA database interface.

ODIASA030 - Very large data types

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

Sybase ASA recommends the following conversion rules :

Solution :

Very large character data types are not supported yet by the Sybase ASA database interface.

ODIASA031 - Cursors WITH HOLD

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

Sybase ASA does not close cursors when a transaction ends, as long as the global parameter close_on_endtrans is off.

Solution :

BDL cursors that are not declared "WITH HOLD" are automatically closed by the database interface when a COMMIT WORK or ROLLBACK WORK is performed by the BDL program.

ODIASA033 - Querying system catalog tables

As in INFORMIX, Sybase ASA provides system catalog tables (sysobjects,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.

ODIASA034 - 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 [,...]

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

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

ODIASA039 - Data storage concepts

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

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

The following table compares INFORMIX storage concepts to Sybase ASA storage concepts :

ODIASA040 - National characters data types

INFORMIX offers the NCHAR and NVARCHAR data types to store strings in a localized character set.