2
Oracle and Informix Dynamic Server Compared

This chapter contains information comparing the Informix Dynamic Server database and the Oracle database. It includes the following sections:

• Database Security
  
  
• Schema Migration
  
  
• Data Types
  
  
• Data Storage Concepts

Database Security

This section includes information on issues of security with Informix Dynamic Server databases and Oracle databases.

Database Authentication

A fundamental difference between Informix Dynamic Server and Oracle is database user authentication. Informix Dynamic Server users are maintained and authenticated by the host operating system, whereas Oracle users are maintained by the database and can use several methods of authentication, usually through the database.

A user can connect to an Informix Dynamic Server database server through the operating system login information, however access to the databases the server supports is restricted by the sysuser table. The sysuser is maintained by each database and the database administrator.

Database as a Logical Partition

Multiple databases on a single Informix Dynamic Server database server are migrated to a single Oracle database. Schemas in different databases are owned by the same user.

Users

Informix Dynamic Server has two special users, informix and root. A description of these users is as follows:

These users have database administrator access to all the databases supported by the Informix Dynamic Server database server. The two user names do not have to be listed in the sysusers table for any database. The Informix Dynamic Server plug-in creates the two user names in the Oracle database. Another special Informix Dynamic Server database user, which does not have to be an operating system user, is public.

You can grant the public database user system and object privileges and its database level privileges are entered in the sysusers table. The privileges granted to public are automatically available to every other database user. The Informix Dynamic Server plug-in migrates all the object privileges.

Oracle has the concept of a database group or role, where you can grant privileges. These privileges are made available to all other users in the database. It is also called PUBLIC.

The difference is that public is not listed as a database user and you cannot grant connect system privilege to public to enable any user logged on to the host operating system gain access to the database.

All Informix Dynamic Server object level privileges granted to public are migrated to Oracle. None of the three Informix Dynamic Server database privileges granted to public are migrated.

The Informix Dynamic Server plug-in could not detect what operating system users had access to the database. The Informix Dynamic Server plug-in only creates Oracle users for the users listed in sysusers in each of the Informix Dynamic Server databases selected for migration. Therefore, if you rely on granting connect, resource, or even dba to public as a method of allowing operating system users access to the database, then you must explicitly grant each of those users the appropriate database level privilege.

Schema Migration

The schema contains the definitions of the tables, views, indexes, users, constraints, stored procedures, triggers, and other database-specific objects. Most relational databases work with similar objects.

The schema migration topics discussed here include the following:

• Schema Object Similarities
  
  
• Schema Object Names
  
  
• Informix Dynamic Server Database-level Privileges
  
  
• Migrating Multiple Databases
  
  
• Table Design Considerations
  
  
• Schema Migration Limitations for Informix Dynamic Server

Schema Object Similarities

There are many similarities between schema objects in Oracle and Informix Dynamic Server. However, some schema objects differ between these databases. For specific information about schema objects, see the SQL Statements topic within the Oracle9i SQL Reference, Release 1 (9.0.1).

Table 2-1 shows the differences between Oracle and Informix Dynamic Server.

Table 2-1 Schema Objects in Informix Dynamic Server and Oracle  

Schema Object Names

Reserved words differ between Oracle and Informix Dynamic Server. Many Oracle reserved words are valid object or column names in Informix Dynamic Server. Use of reserved words as schema object names makes it impossible to use the same names across databases. The Migration Workbench appends an underscore (_) to the name of an Informix Dynamic Server object that is an Oracle reserved word.

Neither Oracle nor Informix Dynamic Server is case-sensitive with respect to object names. Object names in Informix Dynamic Server are stored as lower case, while Oracle schema object names are stored as upper case.

Choose a schema object name that is the following:

• unique by case
  
  
• by at least one other characteristic

Ensure that the object name is not a reserved word from either database.

For a list of Oracle reserved words, see the Oracle9i SQL Reference, Release 1 (9.0.1).

In non-ANSI-Compliant Informix Dynamic Server databases, schema object names are required to be unique across users. This behavior in Oracle is similar to Informix Dynamic Server ANSI-Compliant mode databases. Different users can create objects with the same name without any conflicts.

Informix Dynamic Server Database-level Privileges

For information on database-level privileges, see the Oracle Migration Workbench Release Notes.

Migrating Multiple Databases

The Migration Workbench supports the migration of multiple Informix Dynamic Server databases if they are on the same Informix Dynamic Server database server.

Table Design Considerations

This section discusses table design issues that you need to consider when converting Informix Dynamic Server databases to Oracle. This section includes the following:

• Data Types
  
  
• IMAGE and TEXT Data Types (Binary Large Objects)
  
  
• Check Constraints

Data Types

This section outlines conversion considerations for the following data type:

• DATETIME Data Types

DATETIME Data Types

The Datetime precision in Informix Dynamic Server is to 5 decimal places, 1/100000th of a second. Oracle9i has a new data type TIMESTAMP which has a precision of 1/100000000th of a second. Oracle also has a DATE data type that stores date and time values accurate to one second. The Migration Workbench has a default mapping to the DATE data type.

For applications that require finer date/time precision than seconds, the TIMESTAMP data type should be selected for the datatype mapping of date data types in Informix Dynamic Server. The database stores point-in-time values for DATE and TIME data types.

As an alternative, if an Informix Dynamic Server application uses the DATETIME column to provide unique IDs instead of point-in-time values, you can replace the DATETIME column with a SEQUENCE in the Oracle schema definition.

In the following examples, the original design does not allow the DATETIME precision to exceed seconds in the Oracle table. The examples assume that the DATETIME column is used to provide unique IDs. If millisecond precision is not required, the table design outlined in the following example is sufficient:

Table 2-2 Original Table Design

CREATE TABLE example_table
(datetime_column    datetime        
not null,
text_column         text            
null,
varchar_column      
varchar(10)     null)

CREATE TABLE example_table
(datetime_column    date            not null,
text_column         clob            null,
varchar_column      varchar2(10)    null)

The design shown in Table 2-3 allows you to insert the value of the sequence into the integer_column. This allows you to order the rows in the table beyond the allowed precision of one second for DATE data type fields in Oracle. If you include this column in the Informix Dynamic Server table, you can keep the same table design for the Oracle database.

Table 2-3 Revised Table Design

CREATE TABLE example_table
(datetime_column    datetime        
not null,
integer_column      int             
null,
text_column         text            
null,
varchar_column      varchar(10)     
null)

CREATE TABLE example_table
(datetime_column    date            not null,
integer_column      number          null,
text_column         clob            null,
varchar_column      varchar2(10)    null)

For Informix Dynamic Server, the value in the integer_column is always NULL. For Oracle, the value for the field integer_column is updated with the next value of the sequence.

Create the sequence by issuing the following command:

CREATE SEQUENCE datetime_seq

Values generated for this sequence start at 1 and increment by 1.

Many applications do not use DATETIME values as UNIQUE IDs, but still require the date/time precision to be higher than seconds. For example, the timestamp of a scientific application may have to be expressed in milliseconds, microseconds, and nanoseconds. The precision of the Informix Dynamic Server DATETIME data type is 1/100000th of a second; the precision of the Oracle DATE data type is one second. The Oracle TIMESTAMP data type has a precision to 1/100000000th of a second. However, the precision recorded is dependent on the operating system.

IMAGE and TEXT Data Types (Binary Large Objects)

The physical and logical storage methods for BYTE and TEXT data in Informix Dynamic Server is similar to Oracle BLOB data storage. A pointer to the BYTE or TEXT data is stored with the rows in the table while the IMAGE or TEXT data is stored separately. This arrangement allows multiple columns of IMAGE or TEXT data per table. Oracle may store IMAGE data in a BLOB type field and TEXT data may be stored in a CLOB type field. Oracle allows multiple BLOB and CLOB columns per table. BLOBs and CLOBs may or may not be stored in the row depending on their size. If LONG or LONG RAW appears, only one column is allowed.

If the Informix Dynamic Server TEXT column is such that the data never exceeds 4000 bytes, convert the column to an Oracle VARCHAR2 data type column instead of a CLOB column. An Oracle table can define multiple VARCHAR2 columns. This size of TEXT data is suitable for most applications.

Check Constraints

You can define check constraints in a CREATE TABLE statement or an ALTER TABLE statement in Informix Dynamic Server. You can define multiple check constraints on a table. A table-level check constraint can reference any column in the constrained table. A column can have only one check constraint. A column-level check constraint can reference only the constrained column. These check constraints support complex regular expressions.

Oracle defines check constraints as part of the CREATE TABLE or ALTER TABLE statements. A check constraint is defined at the TABLE level and not at the COLUMN level. Therefore, it can reference any column in the table. Oracle, however, does not support complex regular expressions.

Schema Migration Limitations for Informix Dynamic Server

The schema migration limitations are separated into the following categories:

• Dbspaces
  
  
• Mapping for Informix Dynamic Server Database Level Privileges to Oracle System Privileges
  
  
• Defaults
  
  
• Indexes
  
  
• Check Constraints
  
  
• Check Constraint Owners

Dbspaces

The Migration Workbench captures all dbspaces on the Informix Dynamic Server, even though you may not require all dbspaces. You can delete the dbspaces, as appropriate, from the Source Model. If you delete the root dbspace, the next time you start the Migration Workbench, the sizing information shows up as zero (0).

Mapping for Informix Dynamic Server Database Level Privileges to Oracle System Privileges

Before migration ensure that the sysmaster database exists for the database server you are migration from. Oracle does not support the migration of Default DATETIME literal.

The Informix Dynamic Server CONNECT privilege maps to the following Oracle system privileges:

• Create Session
  
  
• Alter Session
  
  
• Create View
  
  
• Create Any View
  
  
• Create Synonym
  
  
• Create Any Synonym
  
  
• Create Public Synonym
  
  
• Drop Public Synonym
  
  
• Create Cluster
  
  
• Create Database Link
  
  
• Create Sequence
  
  
• Unlimited Tablespace
  
  
• Create Table
  
  
• Create Procedure
  
  
• Create Trigger

The Informix Dynamic Server RESOURCE privilege maps to the following Oracle system privileges:

• Create Session Operator
  
  
• Alter Session
  
  
• Create Any View
  
  
• Create View
  
  
• Create Synonym
  
  
• Create Any Synonym
  
  
• Create Public Synonym
  
  
• Drop Public Synonym
  
  
• Create Cluster
  
  
• Create Database Link
  
  
• Create Sequence
  
  
• Unlimited Tablespace
  
  
• Create Table
  
  
• Create Procedure
  
  
• Create_trigger
  
  
• Create_type
  
  
• Create_indextype
  
  
• Create_operator

The Informix Dynamic Server DBA privilege maps to the Oracle system All Privileges privilege.

You cannot migrate Informix Dynamic Server DBA users with the WITH ADMIN OPTION for any of the system privileges. The Informix Dynamic Server DBA cannot grant the privileges to other users.

Defaults

The following limitations apply to the Defaults schema object:

• An Informix Dynamic Server user name can be up to 8 characters long. Oracle users can be up to 30 characters long. The Informix Dynamic Server USER system function maps to the Oracle USER system function. However, if you use the Oracle USER function as the default, the addition of the default fails because the column definition, such as CHAR(8), in Informix Dynamic Server is too small for Oracle USER names. Change the length of the column in the Oracle Model to CHAR(30) before migrating.
  
  
• You cannot delete defaults in the Source Model although it appears this is possible within the Migration Workbench. For more information, see Bug 1642519 in the Oracle Bug Database.
  
  
• Defaults for INTERVAL columns that are not a number and are migrated to CHAR(30) fail during migration.
  
  
• Defaults for DATETIME columns that you do not specify in the YYYY-MM-DD HH:MI:SS format fail to migrate properly.

Indexes

Migrate indexes, then migrate unique constraints and primary key constraints. If you do not migrate the schema objects in this order, a system generated index is created for unique constraints and primary keys. This causes the CREATE INDEX statement to fail.

Check Constraints

Check constraints within Informix Dynamic Server are not parsed to Oracle syntax. The user should ensure that they can successfully execute all check constraints listed in the Oracle Model. For more information, see Bug 1644309 in the Oracle Bug Database.

Check Constraint Owners

If a user creates a check constraint on another users' table, the check constraint is created in the Oracle Model and the check constraint is owned by the owner of that table.

Data Types

This section provides descriptions of the differences in data types used by Informix Dynamic Server and Oracle databases. This section contains the following information:

• A table showing the base Informix Dynamic Server data types available and how they are mapped to Oracle data types
  
  
• Recommendations based on the information listed in the table

  Table 2-4 Data Types Summary Table  

  Informix Dynamic Server	Description	Oracle	Comments
  INTEGER INT	Four-byte integer, 31 bits, and a sign. Stores whole numbers in the range -2,147,483,647 to +2,147,483,647	NUMBER(10)	You may wish to place a check constraint on columns of this type to enforce values between 2^31 and2^31.
  SMALLINT	Two-byte integer, 15 bits, and a sign. Stores whole numbers in the range -32,767 to +32,767.	NUMBER(5)	You may wish to place a check constraint on columns of this type to enforce values between -2^15 and 2^15.
  SERIAL	Stores a sequential INTEGER assigned automatically by the database server when a row is inserted.	NUMBER(10)	A Sequence and Trigger is created automatically to update the column that was originally SERIAL.
  DECIMAL DECIMAL(p) floating point DEC DEC(p)	A floating point number with p digits of precision. If you omit p, p defaults to 16.	NUMBER	A floating point number with 38 digits of precision.
  DECIMAL(p,s) fixed-point DEC (p,s)	A fixed point number with precision p and scale s.	NUMBER(p,s)	A fixed point number with precision p and scale s.
  SMALLFLOAT REAL	Stores single-precision floating-point numbers corresponding to the float datatype in C.	FLOAT(63)
  FLOAT(p) DOUBLE PRECISION	Stores double-precision floating-point numbers corresponding to the double datatype in C. p specifies a precision, 1.14, however it is ignored.	FLOAT(126)	You may want to add a check constraint to constrain range of values. Also, you get different answers when performing operations on this type due to the fact that the Oracle NUMBER type is much more precise and portable than FLOAT.
  CHAR(n) CHARACTER(n)	Fixed-length string of exactly n 8-bit characters, blank padded. 0< n < 32768	CHAR(n) if n <= 2000 VARCHAR2(n) if 2000 < n <= 4000 CLOB or LONG if n > 4000	Oracle CHAR can only hold up to 2000 bytes of data. Oracle VARCHAR2 can hold up to 4000 bytes of data. Oracle LONG can hold up to 2G of data, but there are many restrictions on LONG columns. Oracle CLOB can hold up to 4G.
  VARCHAR(n)	Varying-length character string. 0 < n < 256.	VARCHAR2(n)
  TEXT	Stores any kind of text data, up to 2G. A table can contain more than one TEXT column.	CLOB LONG	The CLOB datatype can hold up to 4G of character data. A table can have more than one CLOB column. LONG has a limit of 2G but there are several restrictions on LONG columns.
  BYTE	Stores any kind of binary data, up to 2G. A table can contain more than one BYTE column.	BLOB LONG RAW	The BLOB datatype can hold up to 4G of binary data. A table can have more than one BYPE column. LONG RAW can store binary data. has a limit of 2G but there are several restrictions on LONG columns.
  DATETIME	Stores and instance in time expressed as a calendar date and time of day. It can be defined with qualifiers to specify the precision. For example: DATETIME largest_qualifier TO smallest_qualifier Qualifier Values YEAR MONTH DAY HOUR MINUTE SECOND FRACTION a decimal fraction of a second with up to five digits of precision.	DATE	The Informix Dynamic Server DATETIME data type has higher precision, YEAR to Fraction of Second, than the Oracle DATE data type, Year to Second. The fractional second information, if specified in the Informix Dynamic Server column definition, is lost in the migration. The Oracle TIMESTAMP data type can also be used . It has a precision of 1/10000000th of a second.
  DATE	DATE is stored internally as an integer equal to the number of days since December 31,1899.	DATE	Store a date and time, the time defaults to 12:00AM midnight.
  INTERVAL		NUMBER(p)	Where p is the precision of the largest qualifier value.
  MONEY(p,s)		NUMBER(p,s)

Recommendations

You can map data types from Informix Dynamic Server to Oracle with the equivalent data types listed in Table 2-4. You can define how the base type is mapped to an Oracle type in the Data Type Mappings page in the Options dialog.

BYTE

The Informix Dynamic Server BYTE datatype stores any type of binary data and has a maximum limit of 2^31 bytes (2G). The comparable Oracle datatypes are LONG RAW and BLOB.

Oracle LONG RAW stores variable-length raw binary data field used for binary data up to 2G in length. Although you can insert RAW data as a literal in an INSERT statement (a hexidecimal character represents the bit pattern for every four bits of RAW data, 'CB' = 11001011), there are several restrictions on LONG and LONG RAW columns, for example, only one LONG columns is allowed per table and LONG columns can not be indexed. The LONG RAW datatype is provided for backward compatibility with existing applications. For new applications, Oracle recommends the use of BLOB and BFILE datatypes for large amounts for binary data.

Oracle9i and Oracle8i BLOB, and other Oracle9i and Oracle8i LOB types, CLOB, NCLOB, and BFILE, have a much greater storage capacity than LONG RAW, storing up to 4G of data and Oracle9i and Oracle8i tables can have multiple LOB columns.

Oracle LONGs support on sequential access, while Oracle9i and Oracle8i LOBs support random piece wise access. Although Oracle9i and Oracle8i SQL cannot directly manipulate LOBs, you can access LOBs from SQL through the Oracle9i and Oracle8i supplied DBMS_LOB PL/SQL package. The DBMS_LOB package provides many functions and procedures to append the contents of one LOB to another, compares contents or parts of contents, copies contents, reads from and writes to LOBs, and also returns part of a LOB from a given offset and length.

For example, with Informix Dynamic Server you can select any part of a BYTE column by using subscripts:

select cat_picture[1,75] from catalog where catalog_num = 10001;

A similar request in Oracle9i and Oracle8i follows:

blob_loc BLOB;
binchunk RAW;

SELECT cat_picture INTO blob_loc FROM catalog WHERE catalog_num = 10001;
binchunk := dbms_lob.substr(blob_loc, 75, 1);  

CHAR(n)

The Informix Dynamic Server CHAR datatype stores any sequence of letters, numbers, and symbols. It can store single byte and multibyte characters. A character column has a maximum length of n bytes, where 1<=n<=32767. If n is not specified, 1 is the default. If a character string is less than n bytes, then the string is extended with spaces to make up the length. If the string value is longer than n bytes, the string is truncated without raising an error.

The comparable Oracle datatypes are:

• CHAR(n), fixed-length field, up to 2000 bytes in length
  
  
• VARCHAR2(n), variable-length character data, up to 4000 bytes
  
  
• LONG, variable-length character data up to 2G in length
  
  
• CLOB, character large object up to 4G in length

Informix Dynamic Server CHAR(n) datatypes can be up to 32767 bytes in length. Columns defined as CHAR with a length <= 2000 can be migrated to the Oracle CHAR datatype and functionality contains nearly the same functionality. Both are fixed-length character strings and if you insert a shorter string, the value is blank-padded to the fixed length. If, however, a string is longer, Oracle returns an error.

Oracle VARCHAR2 can hold data up to 4000 bytes in length. Oracle Corporation recommends you us a migration to VARCHAR2 when you are migrating Informix Dynamic Server CHAR columns that store more than 2000 bytes of data but less than or equal to 4000. VARCHAR2 is a variable length datatype and uses non-padded comparison semantics.

If Informix Dynamic Server tables have CHAR(n) columns defined with n > 4000 then the only option is to migrate to Oracle LONG or CLOB.

The LONG datatype can store variable-length character data up to 2G in length. LONG columns can be used in SELECT lists, SET clauses of UPDATE statements, and VALUES clause of INSERT statements. The LONG datatype is provided for backward compatibility and CLOB should be used for storing large amounts of character data. There are several restrictions on LONG datatypes, such as the following:

• One LONG columns is allowed per table
  
  
• LONG columns can not be indexed

The CLOB datatype is just one of the LOB datatypes supported by Oracle. LOB datatypes differ form LONG datatypes in several ways:

• A table may contain multiple LOB columns but only one LONG column
  
  
• A table containing one or more LOB columns can be partitioned, but a table containing a LONG columns can not be partitioned
  
  
• Maximum size of a LOB is 4G, maximum size of LONG is 2G
  
  
• LOBs support random access to data, but LONGs only support sequential access

LOB datatyes can be stored in-line within a table, or out-of-line within a tablespace, using a LOB locator, or in an external file -- a BFILE datatype. It is not currently supported by the Migration Workbench.

Using PL/SQL to manipulate LOBs, VARCHAR2s in PL/SQL can store up to 32767 bytes of data, so handling large Informix Dynamic Server CHAR datatypes should be reasonably efficient when stored in Oracle as CLOBs.

...
clob_loc CLOB;
some_text VARCHAR2(32767);
text_len INTEGER;
...
INSERT INTO page_info (page_num, page_text) VALUES (101, empty_clob);
SELECT page_text INTO clob_loc FROM page_info where page_num = 101;
text_len := LENGTH(some_text);
DBMS_LOB.WRITE(clob_loc, text_len,1, some_text);
 

You can use subscripts on BYTE columns. Subscripts can also be used on CHAR, VARCHAR2, NCHAR, NVARCHAR, and TEXT columns. The subscripts indicate the starting and ending character positions that define each column substring. With the DBMS_LOB package functions, you can choose to receive all or part of the CLOB, using READ and SUBSTR.

Collation Order

Data stored in CHAR columns is sorted based on the order of the code-set for the character set of the database, irrespective of the current location. Sorting in Oracle is based on the Oracle NLS settings. If the Oracle NLS specify a different sort order than the character code set, a sort-by code set can be enabled to ensure that the expected result remains the same. For more information, see the NCHAR(n) topic.

Multibyte Character Sets

Just as is the case for Informix Dynamic Server CHAR and VARCHAR datatypes, the length of Oracle CHAR and VARCHAR2 datatypes is specified in bytes. If the database character set is multibyte, make sure to calculate the appropriate space requirements to allow for the maximum possible number of bytes for a given number of characters.

Comparison Sematics

Informix Dynamic Server comparison semantics for the CHAR datatype and the Oracle CHAR datatype are the same. If you are migrating CHAR(n) columns that have a length where n such that 2000< n <= 4000 to then see the VARCHAR section for more details on comparison semantics for VARCHAR2.

Empty Strings

Informix Dynamic Server CHAR (and VARCHAR) columns can store an empty string, i.e. no data with a length zero. Even though a CHAR column may appear blank-padded, its length is 0. The empty string is not the same as NULL, which indicates that the value is undefined and of unknown length. However, Oracle does not have the concept of an empty string. Therefore, Oracle inserts empty strings as NULL.

You should check the application code and logic for unexpected behavior, such as empty strings migrating to NULL.

CHARACTER(n)

CHARACTER is a synonym for CHAR.

NCHAR(n)

Informix Dynamic Server CHAR and NCHAR datatypes both store the same type of data, a sequence of single-byte or multibyte letters, numbers, and symbols. The main difference between the datatypes is the order the Informix Dynamic Server database server sorts the data. CHAR columns are sorted on code set, the numeric values of the characters defined by the character encoding scheme. NCHAR columns are sorted based on the locale-specific localized order.

Oracle can sort CHAR data based on both the code set and the local-specific order.

The Migration Workbench for Informix Dynamic Server migrates both the Informix Dynamic Server CHAR and NCHAR datatypes to the Oracle CHAR datatype.

Collation Order

The Oracle NLS settings, either by default or as configured by the DBA, define the exact behavior of the sorting order. The NLS settings can be made at the database/init.ora, environment, and session levels.

A locale-specific sort is as a known as a linguistic sort in Oracle. You can use a linguistic sort by setting one of the Oracle collation parameters, NLS_SORT. The following is an example of a linguistic sort:

NLS_SORT = French

A code set sort is known as a binary sort in Oracle. If an application, for some reason, needs to sort data in a CHAR column based only on the code set, then the application can set the NLS_SORT to be a binary sort. The following is an example of a binary sort:

NLS_SORT = BINARY

Aside

Oracle does have a built-in NCHAR datatype, as well as NVARCHAR2 and NCLOB datatypes. These three datatypes can be used to store fixed-width and variable-width multibyte character set data as specified by the NATIONAL CHARACTER SET setting in the CREATE DATABASE command.

The National Character Set is an alternative character set to the Database Character Set. It is particularly useful in databases with a variable-width multibyte database character set because NCHAR, NVARCHAR2, and NCLOB can store fixed-width multibyte characters. Storing fixed-width multibyte characters enhances performance by allowing optimized string processing on these columns. An Oracle database can not be created with a fixed-width multibyte character set but the National Language datatypes allow storage of fixed-width multibyte character set data. The properties of a fixed-width character set may be more desirable for extensive processing operations or for facilitating programming.

VARCHAR(m,r)

The Informix Dynamic Server VARCHAR datatype stores varying length single-byte and multibyte character strings of letters, numbers. and symbols. The maximum size of this column is m, which can range from 1 to 255. The minimum reserved space is r. This is optional and defaults to 0 if not specified. The minimum reserved space can range from 0 to 255.

The comparable Oracle datatype is VARCHAR2(n) that also stores variable-length character strings. An Oracle VARCHAR2(n), however, can have a maximum string length of between 1 and 4000 specified for n.

Specifying a minimum reserved space is useful if the data in a row is initially small but is expected to grow at a later date. If this is the case then, when migrating Informix Dynamic Server tables that contain VARCHAR columns consider increasing the PCTFREE value in the storage clause for these tables in the Oracle database. If this column is used in an index, then the PCTFREE values for the corresponding index storage should also be considered. For indexes based on VARCHAR columns, Informix Dynamic Server allocates the maximum storage.

Comparison Semantics

Informix Dynamic Server VARCHAR values are compared to other VARCHAR values and to character values in the same way that character values are compared. The shorter values are blank-padded until the values have equal lengths, then they are compared for the full length.

Oracle VARCHAR2 comparisons are made using non-padded comparison semantics. Trailing blanks are important and are included in the comparison. Two values are only equal if they have the same characters and are of equal length.

Oracle CHAR comparison uses blank-padded comparison semantics, similar to the way Informix Dynamic Server compares CHAR and VARCHAR data. If two values have different lengths, Oracle adds blanks at the end of the shorter value, until the two values are the same length. Oracle then compares the values, character by character, up to the first character that differs. So two values that are different only in the number of trailing blanks are considered equal.

This is important behavior for the migration of the applications. It is possible for some comparisons on Informix Dynamic Server VARCHAR columns may fail when migrated to Oracle VARCHAR2 columns where trailing blanks are involved. To offset this, you may need to use RTRIM() on all columns in a comparison to strip off the trailing blanks.

Collating VARCHAR

The main difference between the NVARCHAR and VARCHAR datatype is the difference in collation sequencing. NVARCHAR character collation order depends on the database server locale, while the collation of VARCHAR characters depends on the code set. For more information on how these collation methods are implemented in Oracle and the impact on Informix Dynamic Server, see the NCHAR(n) topic.

Aside

Oracle has a built-in VARCHAR datatype that is currently synonymous with the VARCHAR2 datatype. However, VARCHAR is reserved for future use. In a later version of Oracle, the definition of VARCHAR may change and since VARCHAR2 is fully supported, the VARCHAR2 datatype is used to store variable-length character strings to avoid any possible changes from the current behavior.

CHARACTER VARYING(m,r)

The Informix Dynamic Server CHARACTER VARYING datatype is the ANSI-compliant format for character data of varying length. The Informix Dynamic Server VARCHAR datatype supports the same functionality and is treated as one in the database server. This datatype is treated the same as the VARCHAR datatype and migrates to the Oracle VARCHAR2 datatype. For more information, see the VARCHAR(m,r) topic.

NVARCHAR(m,r)

The Informix Dynamic Server NVARCHAR(m,r) datatype stores data of varying length, similar to VARCHAR, except that it compares data in the order that the locale specifies.

The Informix Dynamic Server NVARCHAR(m,r) datatype is migrated to the Oracle VARCHAR2(n) datatype.

For more information on migration issues, see the VARCHAR(m,r) and NCHAR(n) topics.

DATE

The DATE datatype stores the calendar date and the default display format is mm/dd/yyyy where mm is the month (01-12), dd is the day of the month (01-31) and yyyy is the year (0001-9999).

The DATE values are stored as integers thus DATE can be used in arithmetic expressions. For example, subtracting a DATE value from another DATE value returns the number of days that have elapsed between the two dates.

Subtracting two Oracle DATE datatypes from each other returns the number of days between the two dates. If only calendar dates are stored in Oracle, then the default time of 12:00:00AM (midnight) is also stored, so any subtraction results in a whole number indicating the number of days between the two dates. For the month, Informix Dynamic Server accepts a number value of either 1 or 01 for January, and so on. Similarly, for the day, Informix Dynamic Server accepts either 1 or 01 for the first day of the month. This is also true in Oracle.

DATETIME

The Informix Dynamic Server DATETIME datatype stores an instant in time expressed as a calendar date and time of day. The precision that a DATETIME value is stored can be chosen, with the precision ranging from a year to a fraction of a second. DATETIME in effect is a family of 28 datatypes.

The Oracle DATE datatype matches just one of the 28 datetime types, DATETIME YEAR TO SECOND.

The Informix Dynamic Server plug-in stores DATETIME values as Oracle DATE values, losing the FRACTION part of DATETIME. If you need to keep the fraction part of DATETIME, before migration, add a new column to the table and store the fraction part as a DECIMAL with the appropriate precision migrated to the appropriate NUMBER datatype.

Any DATETIME table columns that do not store a particular precision use the Oracle defaults. The defaults for Oracle DATE are the first day of the current month and 12:00:00AM (midnight).

For applications that need to manipulate various DATETIME precisians, the SQL code needs to be changed. For example, if a column is defined as MONTH TO DAY, and contains two values, date1: March 10 and date2: February 18 shown as (mm/dd) 03/10 and 02/18. If these values are stored in Oracle DATE (if the current year is 1999, the values would be date1: 1999/03/10 12:00:00 and date2:1999/02/18 12:00:00 respectively. The expression date1 - date2 UNITS DAY returns 20 days. However, if the year was 2000, then the expression date1 - date 2 UNITS DAY returns 21 days.

Using a combination of TO_DATE and TO_CHAR and appropriate date format masks, the year the DATE was stored can be replaced with the current year for use in the expression. The following is an example of this combination:

SQL> SELECT TO_CHAR(TO_DATE(TO_CHAR(TO_DATE('01-01-1997',
'MM-DD-YYYY'), 'MM-DD'), 'MM-DD'), 'MM-DD-YYYY') from dual; 
TO_CHAR(TO 
---------- 
01-01-2000 

Oracle DATE Arithmetic

Subtraction of DATE returns days. Because each date contains a time component, most results of date operations include a fraction. The fraction indicates a portion of one day. For example, 1.5 days is 36 hours.

The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31 day month.

You cannot add dates, but another Oracle function available for date arithmetic is ADD_MONTHS(date,n). To add days to a date, add a number constant to the date.

INTERVAL

Currently, there is no corresponding Oracle datatype for the Informix Dynamic Server INTERVAL datatype.

The Informix Dynamic Server INTERVAL datatype can be defined as one of 18 different precisions, YEAR TO YEAR, YEAR TO MONTH, MONTH TO MONTH, DAY TO DAY and so on right down to FRACTION TO FRACTION(f). These are divided into two classes, YEAR TO MONTH, and DAY TO FRACTION.

If the largest qualifier value and the smallest qualifier value are not the same, then the INTERVAL column is migrated to CHAR(30).

Manipulating Oracle DATE with Informix Dynamic Server INTERVAL Values

Numeric constants can be added or subtracted from the Oracle DATE datatype -- to that Informix Dynamic Server DATE and DATETIME datatypes are mapped -- and are treated in terms of days. Therefore any operations involving the second class of Informix Dynamic Server INTERVAL, DAY TO FRACTION must be expressed as a fraction in terms of days. For example,

CURRENT + INTERVAL (10 12) DAY TO HOUR   

should be expressed as

SYSDATE + 10.5

To handle addition and subtraction of the first class of INTERVAL, YEAR TO MONTH, the INTERVAL needs to be expressed in terms of months and passed as a parameter, along with the date.

The Oracle function ADD_MONTHS(date,n) can be used for arithmetic:

TODAY + INTERVAL (2) YEAR TO YEAR 

should be expressed as

ADD_MONTHS(SYSDATE, 24)

You do have the option to migrate all INTERVAL columns as CHARACTER(30) preserving all details, including subsecond information. However, the application must manipulate this data appropriately, using TO_DATE() and others.

DECIMAL

Informix Dynamic Server DECIMAL datatype can take two forms:

• DECIMAL(p) floating-point
  
  
• DECIMAL(p,s) fixed point

DECIMAL(p) floating point

DECIMAL(p) floating point stores decimal floating point numbers up to a maximum of 32 significant digits.

The total number of significant digits is p. This is optional, DECIMAL is treated as DECIMAL(16). DECIMAL(p) has an absolute values range of between 10-130 and 10124.

In an ANSI-compliant Informix Dynamic Server database, DECIMAL(p) defaults to DECIMAL(p,0). If only p is specified, s is actually stored as 255 in the catalog tables.

In Oracle, Informix Dynamic Server DECIMAL(p) floating point values are always stored as NUMBER. It has 38 significant digits since it is not possible to restrict the total number of significant digits for storing a floating-point number. Oracle can store negative and positive values in the range 1.0x10-130 and 9.9...9x10125, which is 38 nines followed by 88 zeros. NUMBERS are stored in scientific notation. Leading and trailing zeros are not stored.

Since Oracle can store floating-point numbers with a greater precision than Informix Dynamic Server, there should be no loss of precision after the migration to Oracle.

DECIMAL(p,s) fixed-point

The precision is p with a range 1 to 32. The number of digits to the right of the decimal place is s. Numbers < 0.5x10-s have the value 0.

In Oracle, DECIMAL(p,s) maps to NUMBER(p,s).

MONEY(p,s)

The MONEY datatype is always a fixed-point number with a maximum 32 significant digits.

MONEY(p) = DECIMAL(P,2)

MONEY = DECIMAL(16,2)

The Informix Dynamic Server MONEY datatype is represented as DECIMAL. The Informix Dynamic Server MONEY(p,s) datatype maps to Oracle NUMBER(p,s).

INTEGER

The Informix Dynamic Server INTEGER datatype is mapped to NUMBER(10).

Range Boundaries

The Informix Dynamic Server INTEGER datatype can store values in the range -2,147,483,647 to 2,147,483,647. If a value to be inserted is outside this range, the Informix Dynamic Server database server does not store the value and returns an error. A column defined as NUMBER(10) in an Oracle database allows values in the range -9,999,999,999 to 9,999,999,999 to be inserted without raising an error. If mapped, INTEGER columns should enforce the original range, then a check constraint can be added to the columns to ensure that values entered into these columns are within the range -2,147,483,647 to 2,147,483,647.

Storage

Informix Dynamic Server stores INTEGER as a signed binary integer and requires 4 bytes per value.

Oracle stores numeric data in variable length format, in scientific notation. The smallest storage space Oracle uses to represent an INTEGER is 2 bytes, 12 bytes is the maximum storage space required. The storage space for the value depends on the number of significant digits.

Inserting Fractions

If you insert 7.2 and 7.8 into Informix Dynamic Server INTEGER datatype, fractional parts are truncated, therefore the values 7 and 7 are stored.

If you insert 7.2 and 7.8 into Oracle NUMBER(10), fractional parts are rounded, therefore the values are stored as 7 and 8.

It may be necessary to check application code and logic to ensure there is no unexpected behavior. This is because it is assumed that fractional parts of any number are automatically truncated when inserted into the Informix Dynamic Server database.

INT

The Informix Dynamic Server INT datatype is a synonym for INTEGER

SMALLINT

The Informix Dynamic Server SMALLINT datatype is mapped to NUMBER(5).

Range Boundaries

The Informix Dynamic Server INTEGER datatype can store values in the range -32,767 to 32767. If a value is outside this range, the Informix Dynamic Server database server does not store the value and returns an error. A column defined as NUMBER(10) in an Oracle database allows values in the range -99,999 to 99,999 to be inserted without raising an error. If mapped, INTEGER columns should enforce the original range then a check constraint can be added to the columns to ensure that values entered into these columns are within the range -32,767 to 32767.

Storage

Informix Dynamic Server SMALLINT datatype values take up 2 bytes per value.

Oracle stores a values in an NUMBER(5) datatype with a minimum of 2 bytes and a maximum of 4 bytes

Inserting Fractions

For information on differences in behavior for Informix Dynamic Server INTEGER and Oracle NUMBER, see the Inserting Fractions topic.

SERIAL

The Informix Dynamic Server SERIAL datatype creates a column in a table that auto-increments an INTEGER value every time a row is inserted into the table. By default, if the column is simply defined as SERIAL, the column begins inserting with the value 1. Other starting values can be set by defining the column. For example, SERIAL(1000)creates a column that begins inserting with the value 1000. The starting number cannot be 0 and the maximum value SERIAL can reach, or be initially set to, is 2,147,483,647. After reaching the maximum value, the SERIAL column resets to 1. Only one SERIAL column may be defined for an Informix Dynamic Server table. The SERIAL datatype is not automatically a unique column, a unique index must be created for this column to prevent duplicate serial numbers.

The Migration Workbench for Informix Dynamic Server maps the Informix Dynamic Server SERIAL datatype to an Oracle NUMBER(10) datatype and flags the column as an auto-increment column. The Migration Workbench also creates a NOT NULL CONSTRAINT on that column, as is the case with Informix Dynamic Server SERIAL columns.

The Migration Workbench creates an Oracle sequence and an Oracle trigger on the table that contained the SERIAL column. The trigger fires every time a row is inserted into the table. It gets the next value in the sequence and inserts it into the field.

For example, the following JOBS table was migrated to Oracle and the JOB_ID column was originally defined as an Informix Dynamic Server SERIAL datatype:

     CREATE TABLE clerk.JOBS(JOB_ID NUMBER (10) NOT NULL,

               JOB_DESC VARCHAR2 (50) NOT NULL,
               MIN_LVL NUMBER (5),
               MAX_LVL NUMBER (5)) 

The Oracle trigger and sequence is created after a table with a SERIAL column is migrated. The sequence is created using the option START WITH 1. If the data for this table is not moved automatically by the Migration Workbench, the sequence starts inserting with 1.

If the table data is selected to be moved automatically by the Migration Workbench while database table objects are created, the Migration Workbench creates the trigger and sequence after the data has been moved. Before the sequence is created the Migration Workbench selects the maximum value from the SERIAL column (for example, 1231) and add 1 to this value and use it as the START WITH value in the CREATE SEQUENCE statement; as follows:

CREATE SEQUENCE clerk.SEQ_11_1 START WITH 1232; 

In the resulting Oracle database inserts to the table continue to auto-increment by one a value for the old serial column every time a row is inserted into the table.

Additional Oracle Sequence Options for Informix Dynamic Server SERIAL Migrations

The Migration Workbench uses the following command to create the sequence:

CREATE SEQUENCE sequence_name START WITH integer;

The Oracle CREATE SEQUENCE command has several options, the only option that is used is the START WITH option.

Many of these options have defaults that are what would be required to replicate the Informix Dynamic Server SERIAL datatype. However, there are a couple of settings that can be altered on the SEQUENCE to make it behave even more closely to the Informix Dynamic Server SERIAL datatype.

In Oracle, all the options that were used to create a sequence can be altered except for START WITH.

Resetting the Start Value

To restart an Oracle sequence at a different number, you must drop and re-create it.

The following Table 2-5 shows that Informix Dynamic Server changes the next value to be used in a SERIAL column, provided 1000 is not less than the current maximum for the column.

Table 2-5 Serial Column Comparison

ALTER TABLE clerk.jobs MODIFY ( job_id 
SERIAL(1000) );

DROP SEQUENCE seq_11_1;
CREATE SEQUENCE seq_11_1 STARTWITH 1000 
MAXVALUE 2147483647 CYCLE;

Some Exceptional Cases

Occasionally the migrated SERIAL column does not behave as it would in Informix Dynamic Server.

Example 1

If the last number values inserted into a table are deleted from the table, the migrated tables sequence begin before the next.

If the table was created as follows:

CREATE TABLE table_with_serial_col ( col1 SERIAL, col2 CHAR(5) )

and after several inserts on the table, as follows:

INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 1]
INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 2]
INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 3]

the definition is changed, as follows:

ALTER TABLE table_with_serial_col MODIFY ( col1 SERIAL(1000) )

If the database is migrated at this point, execute the following command on Informix Dynamic Server:

INSERT INTO table_with_serial_col VALUES ("XXX");    

results in the new row with a value of 1000 for col1. If you execute the same command in the migrated Oracle environment, the new row would have a value of 4 for col1.

Example 2

Another possibility follows:

INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 1]
INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 2]
INSERT INTO table_with_serial_col VALUES ("XXX");    [col1 = 3]

DELETE FROM table_with_serial_col WHERE col1 = 3;

If the database is migrated at this point, execute the following command on Informix Dynamic Server:

INSERT INTO table_with_serial_col VALUES ("XXX");    

results in the new row with a value of 4 for col1. If you execute the same command in the migrated Oracle environment, the new row would have a value of 3 for col1.

It is possible that this would have no effect on the execution of the application or the integrity of the data. The only dependency is that this value is unique and auto-incremental, but it may be useful to check the application logic if situations similar to the examples could occur.

How to examine current Informix Dynamic Server SERIAL values

For Informix Dynamic Server, set the SERIAL value to the values of sysmaster:systabinfo(ti_serialv) where sysmaster:systabinfo(ti_partnum) is the partnum of the table with the serial column.

select c.dbsname, a.owner, a.tabname, d.ti_serialv
  from systables a, syscolumns b, sysmaster:informix.systabnames c,   
sysmaster:informix.systabinfo d
 where (b.coltype = 6  OR b.coltype = 262)
   and a.tabid = b.tabid
   and a.tabid > 99
   and a.owner = c.owner
   and a.tabname = c.tabname
   and c.dbsname = "<DATABASENAME>"
   and c.partnum = d.ti_partnum;

Replace <DATABASENAME> as appropriate.

In the Oracle environment, use the following SQL statements to get the next sequence number to be generated for each sequence:

SQL> SELECT sequence_name FROM USER_SEQUENCES;
SQL> SELECT (<sequence_name>.CURRVAL+1) FROM DUAL;

Replace <sequence_name> as appropriate.

Data Storage Concepts

This chapter provide a description of the conceptual differences (and in many cases, similarities) in data storage for Informix Dynamic Server and Oracle9i and Oracle8i databases.

Recommendations

The following are recommendations:

1. The conceptual differences in the storage structures do not affect the conversion process directly.
   
   
2. Both Oracle and Informix Dynamic Server have a way to control the physical placement of database objects:
   
   
   • IN dbspace for Informix Dynamic Server
     
     
   • TABLESPACE for Oracle.
3. Storage information can be preserved when converting to Oracle. The decisions made when defining the storage of the database objects for Informix Dynamic Server should also apply for Oracle. Especially important are the initial object and physical object placement.

An Oracle database server consists of a shared memory area, several processes that access the database and maintain data integrity and consistency, the Oracle Instance, and a database that stores the data.

An Informix Dynamic Server database server also consists of a shared memory area, several process to access the data and maintain data integrity and consistency, however a single Informix Dynamic Server database server can support several separate databases.

A Oracle database consists of one or more tablespaces. Tablespaces provide logical storage space that link a database to the physical disks that hold the data. A tablespace is created from one or more data files. Data files are files in the file system or an area of disk space specified by a raw device. A tablespace can be enlarged by adding more data files.

An Oracle database consists of a least a SYSTEM tablespace, where the Oracle tables are stored. It can also consist of user defined tablespaces. A tablespace is the logical storage location for database objects. For example, you can specify where a particular table or index gets created in the tablespace.

The size of a tablespace is determined by the amount of disk space allocated to it. Each tablespace is made up of one or more data files.

Data Storage Concepts Table

Table 2-6 Data Storage Concepts in Informix Dynamic Server and Oracle