Skip Headers
Oracle® Migration Workbench Reference Guide for Informix Dynamic Server Migrations
Release 10.1.0 for Microsoft Windows 98/2000/NT/XP and Linux x86
Part No. B16022-01
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

2 Oracle and Informix Dynamic Server Compared

This chapter compares the Informix Dynamic Server database and the Oracle database. It includes the following sections:

Database Security

This section includes information about security issues 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

The following table describes the Informix Dynamic Server special users:

User name Description
informix Informix Dynamic Server software owner
root Operating system super user

These users have database administrator access to all the databases supported by the Informix Dynamic Server database server. These special user names do not have to be listed in the sysusers table for any database. The Migration Workbench automatically creates the two user names in the Oracle database. Another special Informix Dynamic Server database user that 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 Migration Workbench 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 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 cannot 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, 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.

This section contains the following:

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 more information about schema objects, see the Oracle9i SQL Reference.

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

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

Oracle Informix Dynamic Server
Database Database
Schema Schema
Tablespace Dbspace
User User
Role Role
Table Table
Temporary tables Temporary tables
Index Cluster Index
Check constraint Check constraint
Column default Column default
Unique key Unique key
Primary key Primary key
Foreign key Foreign key
Index Index
PL/SQL Procedure SPL Procedure
PL/SQL Function SPL Function
Packages N/A
AFTER triggers Triggers
BEFORE triggers Triggers
Triggers for each row Triggers for each row
Synonyms Synonyms
Sequences SERIAL data type for a column
Snapshot N/A
View View

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

This section outlines conversion considerations for the following data type:

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 that 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 data type 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

Informix Dynamic Server
Oracle
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

Informix Dynamic Server
Oracle
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 con­straints 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.

Foreign Key Constraint ON DELETE Option

The Migration Workbench maps Informix Dynamic Server foreign key constraints to equivalent constraints in Oracle. However, the Migration Workbench has a default option to create foreign keys with the ON DELETE CASCADE rule. This default option specifies ON DELETE CASCADE in all foreign key constraint CREATE statements.

To only migrate foreign key constraints that were originally created with ON DELETE CASCASE to Oracle with ON DELETE CASCASE, you must turn off the option in the Migration Workbench Oracle model.

Schema Migration Limitations for Informix Dynamic Server

The schema migration limitations are separated into the following categories:

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

Blobspaces and Byte Columns in Table

The Migration Workbench does not support the migration of Informix Dynamic Server byte columns specified to be stored in a blobspace outside the dbspace of the table. These byte columns are migrated to the same tablespace as the table, as the Migration Workbench cannot specify how to store LOB data separately.

For tables that require BLOB data to be stored in a separate tablespace, you can generate the tables automatically using the Migration Workbench, and then use Oracle Enterprise Manager to modify the LOB storage clause, or issue a SQL*PLUS ALTER TABLE MOVE LOB statement, before migrating the data.

You can also use the Migration Workbench to generate the Oracle SQL scripts for Tables and modify the CREATE TABLE statements manually to specify the LOB storage requirement. Then run the modified SQL script against the target Oracle database.

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.


Note:

Informix Dynamic Server database users granted the CONNECT database level privilege do not have the privilege to create tables, procedures or triggers. However, CONNECT users may be the owner of these object types, created for them by more privileged users.

The Migration Workbench creates schema objects connected to the Oracle database as the owner of the object. Any attempt to create an object without the appropriate privilege generates an error. Therefore, the Informix Dynamic Server plug-in maps users, that have the Informix Dynamic Server CONNECT privilege, to Oracle with the system privileges to create tables, procedures and triggers. To revoke the privileges from the users after migration execute the following:

revoke create table, create procedure, create trigger from <user>;


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.

Table and Index Fragmentation

The Migration Workbench does not support the mapping of fragmentation information. you must user the Migration Workbench to generate the SQL scripts for tables and indexes and modify them manually. Then run the modified SQL script against the target Oracle database.

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.

SQL statements are not migrated to Oracle syntax. If the Informix SQL statement in the check constraint uses a syntax that is equivalent to the Oracle SQL syntax, there are no issues. If the check constraints cannot be migrated automatically you must use the Migration Workbench to generate the Oracle SQL scripts for check constraints and modify them manually. Then run the modified SQL script against the target Oracle 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.

Disabled Objects and Constraints

The Migration Workbench does not support the migration of any disabled Informix Dynamic Server objects.

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:

Table 2-4 Data Types Summary Table

Informix Dynamic Server
Description Oracle Comments
BYTE
Stores any kind of binary data, up to 2G.

A table can contain more than one BYTE column.

BLOB

LONG RAW

The BLOB data type can hold up to 4G of binary data. A table can have more than one BYTE column.

LONG RAW can store binary data. has a limit of 2G but there are several restrictions on LONG columns.

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.

COLLECTION
Set, multiset, and list. VARRAY
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.
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.

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.
FLOAT(p)

DOUBLE PRECISION

Stores double-precision floating-point numbers corresponding to the double data type 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.
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.
INTERVAL

NUMBER(p) Where p is the precision of the largest qualifier value.
MONEY(p,s)

NUMBER(p,s)
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.
SMALLFLOAT

REAL

Stores single-precision floating-point numbers corresponding to the float data type in C. FLOAT(63)
TEXT Stores any kind of text data, up to 2G. A table can contain more than one TEXT column. CLOB

LONG

The CLOB data type 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.

VARCHAR(n) Varying-length character string.

0 < n < 256.

VARCHAR2(n)

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 data type stores any type of binary data and has a maximum limit of 2^31 bytes (2G). The comparable Oracle data types 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 data type is provided for backward compatibility with existing applications. For new applications, Oracle recommends the use of BLOB and BFILE data types 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 data type 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 data types 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) data types can be up to 32767 bytes in length. Columns defined as CHAR with a length <= 2000 can be migrated to the Oracle CHAR data type 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.


Note:

Oracle compares CHAR values using blank-padded comparison semantics. For more information about CHAR values, see Comparison Sematics.

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 data type 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 data type 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 data type is provided for backward compatibility and CLOB should be used for storing large amounts of character data. There are several restrictions on LONG data types, such as the following:

  • One LONG column is allowed per table

  • LONG columns can not be indexed

The CLOB data type is just one of the LOB data types supported by Oracle. LOB data types differ form LONG data types 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 data types 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 data type. 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 data types 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 NCHAR(n).

Multibyte Character Sets

Just as is the case for Informix Dynamic Server CHAR and VARCHAR data types, the length of Oracle CHAR and VARCHAR2 data types 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 data type and the Oracle CHAR data type 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 that 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.

CHARACTER VARYING(m,r)

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

COLLECTION

A collection data type is a complex type that is made up of one or more elements, all of the same data type. A collection element can be of many different data type, including complex data types. However, it cannot be a BYTE, TEXT, SERIAL, or SERIAL8 data type and the element cannot have a NULL value. You must specify the NOT NULL constraint for collection elements. No other constraints are valid for collections. Informix Dynamic Server supports LIST, SET, and MULTISET built-in collection types. The keywords used to declare these collections are the names of the type constructors or just constructors. For more information about the syntax of collection types, see the IBM Informix Guide to SQL: Syntax.

Oracle supports ARRAYS and NESTED TABLE collection data types. In Informix 9 Migration Workbench plug-in, the Informix Dynamic Server collection data type is mapped to VARRAYs. An Oracle VARRAY is an ordered set of data elements. All elements of a given array are of the same data type. Each element has an index number corresponding to the position of the element in the array. The number of elements in an array is the size of the array. Oracle arrays are of variable size. You must specify a maximum size when you declare the varray.

Mapping Comparison

For each Informix Dynamic Server collection typed table column, a new Oracle VARRAY type is created. The type name is the type of the corresponding Oracle table column. For example, an Informix Dynamic Server table Employee has a column Hobbies containing a LIST of varchar(50). Migration Workbench creates Oracle VARRAY type called LST0003 that is a VARRAY for Varchar2(50). In the translated Oracle table EMPLOYEE, the type of column HOBBIES is LST0003. You can modify the name of the VARRAY type during migration.

DATE

The DATE data type 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 data types 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 data type 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 data types.

The Oracle DATE data type 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 data type.

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 

Note:

The Migration Workbench does not support all DATETIME specifications. Only DATETIME columns with the general format YYYY/MM/DD HH24:MI:SS.FF are supported.

For other DATETIME specifications you may have to use the Migration Workbench to generate the Oracle SQL scripts for defaults, modify them manually to include the TO_DATE() function with an appropriate date format mask. Then run the modified SQL script manually against the target Oracle database.


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.


Note:

Evaluate the logic of the addition or subtraction. Remember you can have months that are 28, 29, 30, or 31 days and you can have years that are 365 or 366 days.

DECIMAL

Informix Dynamic Server DECIMAL data type 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).

INT

The Informix Dynamic Server INT data type is a synonym for INTEGER

INTEGER

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

Range Boundaries

The Informix Dynamic Server INTEGER data type 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 data type, 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.

INTERVAL

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

The Informix Dynamic Server INTERVAL data type can be defined as one of 18 different precisians, 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.

Value INTERVAL
YEAR TO YEAR NUMBER(4)

Informix Dynamic Server default precision for YEAR

DAY(3) TO DAY NUMBER(3)
SECOND(6) TO SECOND NUMBER(6)

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 data type -- to that Informix Dynamic Server DATE and DATETIME data types 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.

MONEY(p,s)

The MONEY data type 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 data type is represented as DECIMAL. The Informix Dynamic Server MONEY(p,s) data type maps to Oracle NUMBER(p,s).

NCHAR(n)

Informix Dynamic Server CHAR and NCHAR data types both store the same type of data, a sequence of single-byte or multibyte letters, numbers, and symbols. The main difference between the data types 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 data types to the Oracle CHAR data type.

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 data type, as well as NVARCHAR2 and NCLOB data types. These three data types 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 data types 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.

NVARCHAR(m,r)

The Informix Dynamic Server NVARCHAR(m,r) data type 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) data type is migrated to the Oracle VARCHAR2(n) data type.

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

SMALLINT

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

Range Boundaries

The Informix Dynamic Server INTEGER data type 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 data type values take up 2 bytes per value.

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

Inserting Fractions

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

SERIAL

The Informix Dynamic Server SERIAL data type 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 data type 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 data type to an Oracle NUMBER(10) data type 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 data type:

     CREATE TABLE clerk.JOBS(JOB_ID NUMBER (10) NOT NULL,
               JOB_DESC VARCHAR2 (50) NOT NULL,
               MIN_LVL NUMBER (5),
               MAX_LVL NUMBER (5)) 
     TABLESPACE PUBS; 
     REM 
     REM Message : Created Sequence: clerk.SEQ_11_1 
     REM User : system 
     CREATE SEQUENCE clerk.SEQ_11_1 START WITH 1 
     / 
     REM 
     REM Message : Created Sequence Trigger: clerk.TR_SEQ_11_1 
     REM User : system 
     CREATE TRIGGER clerk.TR_SEQ_11_1 
     BEFORE INSERT ON clerk.JOBS FOR EACH ROW 
     BEGIN 
       SELECT clerk.SEQ_11_1.nextval INTO :new.JOB_ID FROM dual; END; 
     /

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 data type. However, there are settings that you can alter on the SEQUENCE to make it behave more closely to the Informix Dynamic Server SERIAL data type. The following table outlines these settings:

Option Description
START WITH integer Specify the start sequence value.

For more information, see Resetting the Start Value.

INCREMENT BY integer Specify the interval between sequence numbers. If this value is negative, then the sequence descends. For Informix Dynamic Server, use the Oracle default of 1.
MAXVALUE integer NOMAXVALUE is the default setting. For Informix Dynamic Server, set this to 2147483647 to override the Oracle default value.
NOMAXVALUE Specify NOMAXVALUE to indicate a maximum value of (10^27)-1, twenty eight 9's in a row, for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE integer Specify the sequence minimum value. For Informix Dynamic Server, indicate 1 as the value so that if the sequence ever restarts, it restarts with this value.
NOMINVALUE Specify NOMINVALUE to indicate a minimum value of 1 for an ascending sequence or -(10^26) for a descending sequence. This is the default. For Informix Dynamic Server, use the default because the default INCREMENT BY value of 1, we get a default minimum value of 1.
CYCLE Specify CYCLE to indicate that the sequence continues to generate values after reaching either maximum or minimum value. After an ascending sequence reaches maximum value, it generates minimum value.
SERIAL For Informix Dynamic Server, the column resets to 1 after reaching 2147483647.
NOCYCLE Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching maximum or minimum value. This is the default. For Informix Dynamic Server, override this default Oracle behavior to CYCLE.
CACHE integer Specify how many values of the sequence Oracle preallocates and keeps in memory for faster access.
NOCACHE Specify NOCACHE to indicate that values of the sequence are not preallocated.

If both CACHE and NOCACHE are omitted, Oracle caches 20 sequence numbers by default. For Informix Dynamic Server, since MINVALUE is 1, MAXVALUES is at least 2,147,483,647 and INCREMENT is 1, then there should be no problems with the default. If the table is a target of high activity then, the CACHE values may have to be reviewed along with FREELISTS, INITTRANS, MAXTRANS, and others.)


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.

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

Informix Dynamic Server
Oracle
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.

VARCHAR(m,r)

The Informix Dynamic Server VARCHAR data type stores varying length single-byte and multibyte character strings of letters, numbers. and symbols. The maximum size of this column is m, that 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 data type 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 data type 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 about how these collation methods are implemented in Oracle and the impact on Informix Dynamic Server, see NCHAR(n).

Aside

Oracle has a built-in VARCHAR data type that is currently synonymous with the VARCHAR2 data type. 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 data type is used to store variable-length character strings to avoid any possible changes from the current behavior.

Data Storage Concepts

This section provide a description of the conceptual differences and 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

Informix Dynamic Server
Oracle
Chunks

Physical disk space is allocated in terms of chunks. A chunk can be from a file system or raw disk space.

The root dbspace is mapped to a chunk specified by a raw device name or the full path name of a file in a file system, through the initialization file, on_config.

Data Files

One or more data files are created for each tablespace to physically store the data of all the logical structures in a tablespace.

Page and Blobpage

Page:

A chunk has its space divided into pages, each with a specified number of bytes. Any I/O must be performed in page units.

Blobpage:

A blobpage stores BYTE and a TEXT data within a blobspace. The size of blobpage is a unit of disk allocation selected by the user who creates the blobspace, and can vary from blobspace to blobspace.

Data Block

One data block corresponds to a specific number of bytes of physical space on disk. The database block size can be specified when creating the database.

Extent

Extent is the allocation of disk space to a database object in units of physically contiguous pages and cannot span chunk boundaries. All database objects have space allocated in increment of one extent. For a single table, extents can be located in different chunks of the same dbspace. Within an extent, all data pertains to a single tblspace.

Extent

An extent is a specific number of contiguous data blocks, obtained in a single allocation.

Tblspace

The total diskspace allocated to a table includes pages allocated to:

  • data

  • indexes

  • storage of blob data (BYTE or TEST) in the dbspace (excluding pages storing blob data in separate blobspace)

  • tracking page usage within the table extents

Segments

A segment is a set of extents allocated for a certain logical structure. The extents of a segment may or may not be contiguous on disk, and may or may not span data files.

Physical Log

A unit of contiguous disk pages containing "before images" of data that has been modified during processing.

Logical Log

Logical log file is the name of each of these additions of space. This log records logical operations during on-line processing. All transaction information is stored in the logical files as a database is created with the transaction log.

Redo Log Files

Each database has a set of two or more redo log files. All changes made to the database are recorded in the redo log. Redo log files are critical in protecting a database against failures.

Root dbspace

The root dbspace stores information about all databases created.

System Tablespace

Oracle Control Files

Each database has a control file. This file records the physical structure of the database, such as the database name, name and location of the database data files and redo logs.

Dbspace and Blobspace

Dbspaces:

Database objects are stored in a dbspace, which is a minimum of one piece of physical disk or chunk.

BYTE and TEXT (Binary Large Objects, or BLOBs) data can be stored in a dbspace, but performance may suffer if the BLOBs are larger than two dbspace pages.

The ROOT dbspace is the name of the first dbspace created. Specific pages and internal tables in the ROOT dbspace describe and track all other dbspaces, blobspaces, and tblspaces.

Blobspaces:

A blobspace provides a storage area for TEXT and BYTE data using a larger and more efficient space allocation mechanism more suited to large objects as opposed to storing them in a dbspace with more traditional data types.

Tablespace

A database is divided into logical storage units called tablespaces. A tablespace is used to group related logical structures together. A database typically has one system tablespace an one or more user tabletops.